Consultas SQL para Monitoreo y Optimización de Bases de Datos

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 ('BACKUP DATABASE','RESTORE DATABASE', 'BACKUP LOG','DbccFilesCompact', 'DbccSpaceReclaim','DBCC'). 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.

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

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.