Paso 1: Master.dbo.sysperfinfo La tabla de sistema master.dbo.sysperfinfo contiene todos los contadores de rendimiento interno de SQL server que se ven con PerfMon. Los pros y los contras de utilizar sysperfinfo son: Pros: - Est谩 disponible online sin la necesidad de configurar un entorno de monitorizaci贸n. Te puedes crear un simple script para recopilar los datos de rendimiento. - Al contrario que Perfmon, no son necesarios permisos a nivel de Sistema Operatifo. S贸lo necesitas permiso de lectura a la base de datos master. Los permisos a nivel de sistema operativo a veces son dif铆ciles de conseguir cuando los servicios de una empresa est谩n "outsourceados". Contras: - Sysperfinfo abarca s贸lo los contadores de SQL Server. No est谩n los de CPU, memoria o estad铆sticas de I/O de disco disponibles. El script 1.1 te ense帽a c贸mo recopilar los datos del contador 'Log Bytes Flushed/sec' durante 3 minutos tomando muestras a intervalos de 5 segundos.
--Script 1.1: CREATE TABLE #Writes(ts DATETIME, LogBytesFlushes BIGINT) DECLARE @lbf INT, @lbfold INT, @starttime DATETIME SET @starttime = getdate() SELECT @lbfold=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Log Bytes Flushed/sec' AND instance_name = 'test' WAITFOR DELAY '00:00:05' WHILE getdate() < dateadd(mi,3,@starttime) BEGIN SELECT @lbf=cntr_value FROM master..sysperfinfo WHERE counter_name = 'Log Bytes Flushed/sec' AND instance_name = 'test' INSERT INTO #writes (ts,LogBytesFlushes) VALUES (getdate(), @lbf - @lbfold) SET @lbfold = @lbf WAITFOR DELAY '00:00:05' END SELECT * FROM #writes
El script 1.2 crea todas las tablas necesarias para un entorno de ensayo:
--Script 1.2: CREATE TABLE dbo.wt_current_sysperfinfo ( InsertTime DATETIME NULL CONSTRAINT DF_wt_current_sysperfinfo_InsertTime DEFAULT (getdate()), object_name NCHAR (128) NOT NULL , counter_name NCHAR (128) NOT NULL , instance_name NCHAR (128) NULL , cntr_value INT NOT NULL , cntr_type INT NOT NULL ) ON [PRIMARY] GO CREATE TABLE dbo.wt_previous_sysperfinfo ( InsertTime DATETIME NULL CONSTRAINT DF_wt_previous_sysperfinfo_InsertTime DEFAULT (getdate()), object_name NCHAR (128) NOT NULL , counter_name NCHAR (128) NOT NULL , instance_name NCHAR (128) NULL , cntr_value INT NOT NULL , cntr_type INT NOT NULL ) ON [PRIMARY] GO CREATE TABLE dbo.tbl_log_sysperfinfo( InsertTime datetime NULL CONSTRAINT DF_tbl_log_sysperfinfo_InsertTime DEFAULT (getdate()), [Batch Requests/sec] FLOAT NULL, [Buffer cache hit ratio] FLOAT NULL, [Page life expectancy] FLOAT NULL, [User Connections] INT NULL ) ON [PRIMARY] GO
El script 1.3 crea el procedimiento almacenado para registrar un nuevo valor
--Script 1.3: CREATE PROC usp_refresh_log_sysperfinfo as -- declarations DECLARE @previous_inserttime DATETIME, @current_inserttime DATETIME, @elapseTimeSec INT DECLARE @previous_batch_request_sec FLOAT, @current_batch_request_sec FLOAT, @batch_request_sec FLOAT DECLARE @current_Buffer_cache_hit_ratio FLOAT, @current_Buffer_cache_hit_ratio_base FLOAT, @Buffer_cache_hit_ratio FLOAT DECLARE @Page_life_expectancy INT, @User_Connections INT -- calculate elapse time SELECT TOP 1 @current_inserttime = InsertTime FROM dbo.wt_current_sysperfinfo (NOLOCK) SELECT TOP 1 @previous_inserttime = InsertTime FROM dbo.wt_previous_sysperfinfo (NOLOCK) SELECT @elapseTimeSec = datediff(s, @previous_inserttime, @current_inserttime) -- calculate @batch_request_sec SELECT @current_batch_request_sec = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Batch Requests/sec' SELECT @previous_batch_request_sec = cntr_value FROM dbo.wt_previous_sysperfinfo (NOLOCK) where counter_name = 'Batch Requests/sec' SELECT @batch_request_sec = (@current_batch_request_sec - @previous_batch_request_sec) / @elapseTimeSec -- calculate Buffer cache hit ratio SELECT @current_Buffer_cache_hit_ratio = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio' SELECT @current_Buffer_cache_hit_ratio_base = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Buffer cache hit ratio base' SELECT @Buffer_cache_hit_ratio = @current_Buffer_cache_hit_ratio / @current_Buffer_cache_hit_ratio_base * 100.00 -- calculate Page life expectancy SELECT @Page_life_expectancy = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'Page life expectancy' -- calculate User Connections SELECT @User_Connections = cntr_value FROM dbo.wt_current_sysperfinfo (NOLOCK) WHERE counter_name = 'User Connections' INSERT INTO dbo.tbl_log_sysperfinfo ( [Batch Requests/sec], [Buffer cache hit ratio], [Page life expectancy], [User Connections] ) VALUES ( @batch_request_sec, @Buffer_cache_hit_ratio, @Page_life_expectancy, @User_Connections )
El script 1.4 a帽ade un registro en la tabla tbl_log_sysperfinfo. Este script se deber铆a ejecutar en un trabajo programado de SQL Server, una vez por minuto.
--Script 1.4: DROP TABLE dbo.wt_previous_sysperfinfo SELECT * INTO wt_previous_sysperfinfo FROM dbo.wt_current_sysperfinfo TRUNCATE TABLE dbo.wt_current_sysperfinfo INSERT wt_current_sysperfinfo (object_name, counter_name, instance_name, cntr_value, cntr_type) SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM master.dbo.sysperfinfo EXEC usp_refresh_log_sysperfinfo