Clase 30 de 31 • Curso de Optimización de Bases de Datos en SQL Server
Contenido del curso
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.
DAVID EDUARDO BAEZ SANCHEZ
CARLOS FRANZ GUTIERREZ GUTIERREZ
Luis Santiago Cortés Hernández
Roy Rojas
Iván González Mendoza
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
Muy buenos querys, gracias profe. Seria interesante ver como organiza sus querys como buena practica en GIT
Quien me puede explicar en esta consulta para que se utiliza el 80?, no me quedo muy claro la explicación.
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats
ese valor es el FILLFACTOR Es el porcentaje de espacio de cada página de índice que se utiliza para almacenar los datos cuando el índice se crea o se vuelve a generar.
Cuando un índice se crea se le puede dejar un porcentaje vacío para que cuando entren nuevos datos el indice no crezca. Ya que al crecer el archivo consume recursos del servidor. Por eso en este caso se le indica 80, osea va a tener 20% vacio para nuevos datos.
Sígueme en Twitter: https://twitter.com/royrojasdev
Muchas gracias por los scripts de monitoreo, van a ser de mucha ayuda.
