David Lozano Lucas

Paso 5: Configurar la visualización en Excel

Todos tus datos están ahora almacenados en tablas de SQL Server y los interfaces los has creado utilizando vistas. Ahora puedes visualizarlos como gráficos en cualquier herramienta, como por ejemplo, Excel. Si te sientes más agusto con Reporting Services, hazlo así. Reporting services es una forma más profesional, pero por simplicidad, vamos a usar excel aquí. Además, la configuración no va a ser muy distinta.
Todos estos pasos los tienes que hacer en la máquina cliente donde quieres ver tu panel de monitorización (en inglés dashboard, por el parecido al salpicadero de un coche).

Paso 5.1: Crear un DSN de sistema para hacer la conexión a la base de datos de monitorización.
Paso 5.2: Clic en en Data - Import External Data - New Database Query.
Paso 5.3: Selecciona el origen de datos que has creado en el paso 5.1.
Paso 5.4: Ves por todos los pasos y une las diferentes vistas utilizando el campo CounterDateTime.
Paso 5.5: Carga los resultados en una nueva hoja.
Paso 5.6: Clic en Data - Import External Data – Data Range Properties y selecciona Refresh every 1 minute.
Paso 5.6: Abre el asistente para gráficas (chart wizard).

Paso 4: Profiler

La dificultad para recopilar datos del Profiler es encontrar un proceso que filtre información útil de la columna textdata. Por ejemplo: quieres investigar la duración media de un procedimiento almacenado, pero no puedes realizar una agrupación a menos que te deshagas de todos los parámetros que también se almacenan en textdata.
El script 4.1 puede ser un primer paso en tu búsqueda a cómo filtrar y consolidar los datos del SQL Profiler para que te sean útiles. Esta función depende del uso sp_executesdql, por lo que podrías necesitar personalizarlo según tus condiciones.

--Script 4.1
CREATE FUNCTION fn_getSPfromTextdata (@textdata VARCHAR(4000))
RETURNS VARCHAR (4000)
AS
BEGIN
DECLARE @ret VARCHAR (4000)
SET @ret = ''
IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING (@textdata, 22, 6) = 'INSERT'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% (%', SUBSTRING(@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING(@textdata, 22, 6) = 'UPDATE'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% SET %', SUBSTRING(@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF SUBSTRING(@textdata, 1, 18) = 'exec sp_executesql' AND SUBSTRING(@textdata, 22, 6) = 'DELETE'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 22, PATINDEX ('% WHERE %', substring (@textdata, 6, LEN(@textdata) - 5))-17)
END
ELSE IF substring(@textdata, 1, 4) = 'exec'
BEGIN
SELECT @ret = SUBSTRING(@textdata, 6, PATINDEX ('% %', SUBSTRING(@textdata, 6, LEN(@textdata) - 5)))
END
RETURN @ret
END

Paso 3. Configurar la base de datos de monitorización

Una vez configurado el PerfMon según las indicaciones del artículo anterior, un conjunto de tablas se habrán creado automáticamente. Si quieres utilizar esas tablas para consultarlas, deberás crearles índices.

El script 3.1 ajustará tu base de datos de monitorización para que las consultas vayan más rápido.

--Script 3.1
CREATE CLUSTERED INDEX ix_1 ON dbo.CounterData(CounterID) ON [PRIMARY]
CREATE UNIQUE INDEX ix_2 ON dbo.CounterData(RecordIndex, CounterID) ON [PRIMARY]
CREATE INDEX ix_1 ON dbo.CounterDetails(CounterName) ON [PRIMARY]
CREATE INDEX ix_2 ON dbo.CounterDetails(ObjectName) ON [PRIMARY]

El script 3.2 crea vistas para cada contador que puedes monitorizar. Estos scripts se usarán en uno de los pasos siguientes en los que se trata la visualización. El script 3.2 recopilará los 100 valores más recientes de conexiones de usuario.

--Script 3.2
CREATE VIEW VW_User_Connections
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [User connections]
FROM CounterData data WITH (NOLOCK)
INNER JOIN CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.CounterName = 'User connections'
ORDER BY 1 desc
) AS t
ORDER BY t.CounterDateTime

El script 3.3 recopila los 100 valores más recientes del uso total de CPU

--Script 3.3
CREATE VIEW dbo.VW_CPU_total_user_time
AS
SELECT TOP 100 * FROM
(
SELECT TOP 100 data.CounterDateTime
, data.CounterValue AS [CPU Total User Time]
FROM dbo.CounterData data WITH (NOLOCK)
INNER JOIN dbo.CounterDetails details WITH (NOLOCK)
ON data.CounterID = details.CounterID
WHERE details.ObjectName = 'Processor'
AND details.CounterName = '% User Time'
AND details.InstanceName = '_Total'
ORDER BY 1 DESC
) AS t
ORDER BY t.CounterDateTime

Más tarde, estas dos vistas se unirán en un gráfico.

Paso 2: Configurar PerfMon

Los siguientes pasos cubren la configuración del Monitor de Rendimiento (perfmon). Esta configuración está específicamente diseñada para poder usar los datos en una sección posterior.

Paso 2.1. Crear un DSN de tu servidor de producción a tu servidor de monitorización.
Paso 2.2. Cambiar el inicio de sesión del servicio "Performance Logs and Alerts" por una cuenta de dominio que:
- Sea miembro del grupo de administradores local del servidor de producción
- Sea miembro del role db_owner de la base de datos a monitorizar
Paso 2.2. Iniciar Perfmon:
- Añadir un nuevo contador. En la pestaña General, clic en Add Objects para añadir contadores de los siguientes objetos:
Memory, Physical Disk, Process, Processor, SQLServer:Access Methods, SQLServer:Buffer Manager, SQLServer:Cache Manager, SQLServer:Databases, SQLServer:General Statistics, SQLServer:Latches, SQLServer:Locks, SQLServer:Memory Manager, SQLServer:SQL Statistics, SQLServer:SQL Settable.
Cambiar el intervalo a 1 minuto.
- En la pestaña Log Files. Cambiar Log file type a = SQL database. Elige el DSN que creaste en el paso 2.1. En la pstaña Schedule limita la ventana adecuada con las opciones de Start y Stop.
Cuando un fichero de log se cierra, ejecutar este comando: use this to automatically run your consolidation and cleanup script.
Paso 2.4. Inicia tu contador.

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