Monitoreo de actividades

30/31

Lectura

En esta lectura separamos el monitoreo de actividades de la lectura anterior de sp_who3, de otros queries, principalmente por la importancia del query anterior. El query de esta clase puedes encontrarlo en https://github.com/royrojas/Platzi-SQL-Optimizacion/tree/29-query-monitoreo-actividades

Estos son solo unos que utilizo mucho en mi d铆a a d铆a como DBA y en el camino te aseguro que vas a ir modificando estos y creando tus propios queries, vas a ir aprendiendo a facilitarte el trabajo con querys que vas a ir guardando poco a poco. Te recomiendo utilizar los Gists de GitHub para tener ordenados tus scripts.

Consulta a los objetos de la base de datos

Cuando consultamos la tabla sys.objects vamos a poder obtener informaci贸n sobre todos los objetos que hemos creado en nuestra base de datos. Cada objeto tiene un id para poder identificarlo y un tipo de objeto (tabla de usuario, tabla de sistema, procedimiento almacenado, primary key, funci贸n, vista, etc.) Con estos primeros queries vamos a poder ver la fecha de creaci贸n y la fecha en que se modific贸 el objeto.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7

Por ejemplo, en este query vemos los objetos tipo P (procedimiento almacenado) que se modificaron en los 煤ltimos 7 d铆as.

En otro ejemplo podemos ver los triggers creados o modificados.

SELECT
o.name as [Trigger Name],
CASE WHEN o.type = 'TR' THEN 'SQL DML Trigger'
     WHEN o.type = 'TA' THEN 'DML Assembly Trigger' END
     AS [Trigger Type],
sc.name AS [Schema_Name],
OBJECT_NAME(parent_object_id) as [Table Name],
o.create_date [Trigger Create Date], 
o.modify_date [Trigger Modified Date] 
FROM sys.objects o
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE (type = 'TR' OR type = 'TA')
AND ( DATEDIFF(D,create_date, GETDATE()) < 7 OR
    DATEDIFF(D,modify_date, GETDATE()) < 7) -- Last 7 days

Como puedes ver en el ejemplo, modificar el query para tus propios requerimientos es muy sencillo.

Actualizaci贸n r谩pida de 铆ndices

Si quieres actualizar todos los 铆ndices de tu base de datos de una forma muy r谩pida esta puede ser una opci贸n. Aunque siempre te voy a recomendar hacerlo en planes de mantenimiento, esta opci贸n nos puede ayudar principalmente en bases de datos peque帽as y tambi茅n en servidores con SQL Express.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats

En este caso el factor de relleno est谩 en 80, el cual es el porcentaje de espacio en cada p谩gina de 铆ndice para almacenar datos cuando se crea o reconstruye el 铆ndice. Si indicamos valor 0 se va a usar el 煤ltimo valor utilizado o el valor configurado como valor por defecto.

Estado del avance de procesos

Otro query que utilizo regularmente es el de avance en procesos.

-- ver avances de procesos
SELECT session_id as SPID, command, a.text AS Query, start_time, 
percent_complete, dateadd(second,estimated_completion_time/1000, 
getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY 
     sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE',
                    'BACKUP LOG','DbccFilesCompact',
                    'DbccSpaceReclaim','DBCC')

En este ejemplo podemos ver el avance en procesos como backups, procesos de mantenimiento como DBCC y restaurar base de datos (鈥楤ACKUP DATABASE鈥,鈥楻ESTORE DATABASE鈥, 鈥楤ACKUP LOG鈥,鈥楧bccFilesCompact鈥, 鈥楧bccSpaceReclaim鈥,鈥楧BCC鈥). Pero estos valores los puedes modificar por otros m谩s, si ejecutamos el query sin where podremos ver m谩s opciones y dependiendo de nuestras necesidades podremos consultar sobre procesos de reconstrucci贸n de 铆ndices en bases de datos pesadas. Se usa para ver actividades que pueden durar muchos minutos ya que nos muestra el tiempo transcurrido y un estimado del tiempo pendiente para finalizar un proceso.

Monitoreo del tama帽o de la base de datos

Otros queries 煤tiles son para monitorear el tama帽o de las bases de datos.

pasted image 12.png

Si ejecutamos el sp_spaceused nos va mostrar el tama帽o de la base de datos donde tenemos la sesi贸n actual, se puede utilizar de esta forma o tambi茅n podr铆amos incluirlo en alg煤n JOB y que nos alerte eventualmente si llega a alg煤n tama帽o.

Una mejor forma de poder obtener el tama帽o de nuestras bases de datos y que nos puede mostrar informaci贸n m谩s 煤til es el siguiente query:

select db_name() as dbname,
name as filename,
size/128.0 as currentsize,
size/128.0 - cast(fileproperty(name,'SpaceUsed') as int)/128.0 as freespace from sys.database_files
pasted image 13.png

Nos va a mostrar los datos exactos del tama帽o y espacio libre en cada archivo, en el de datos y en el log.

S铆gueme en Twitter como @royrojasdev o en mis p谩ginas royrojas.com y dotnetcr.com. Ah铆 publico regularmente tips y otros queries que te van a ayudar en la optimizaci贸n de tus bases de datos.

Aportes 1

Preguntas 1

Ordenar por:

驴Quieres ver m谩s aportes, preguntas y respuestas de la comunidad? Crea una cuenta o inicia sesi贸n.

Muy interesante y 煤tiles

Para actualizar todas las estad铆sticas al 100% de muestreo, muy 煤til para mejorar rendimiento, pero demorado

EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;'

El monitoreo de crecimiento es muy 煤til y recomiendo guardarlo en una tabla y ejecutarlo peri贸dicamente mediante un job.

Revisar los logs de Sql server

EXEC sp_readerrorlog

Reiniciar el log de sql porque est谩 muy grande

EXEC sp_cycle_errorlog

y revisar la cantidad de registros de las tablas, entre muchos querys que se necesitan鈥

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [RowCount] desc