Para detectar problemas a nivel de hardware podemos usar los contadores de rendimiento, que nos permitirán comprobar "la salud" de nuestro servidor y ver donde podemos tener el cuello de botella. Aun así, por muy potente que sea nuestro hardware, el servidor no responderá adecuadamente si el diseño de las bases de datos no es correcto o no está optimizado.
Centrándonos en este último punto, os quería presentar algunas consultas que nos pueden ser de utilidad para detectar qué consultas o procedimientos almacenados están "presionando" la CPU, el disco y la memoria:
Procedimientos almacenados más ejecutados frecuentemente
SELECT TOP 100 qt.text AS 'Nombre_SP', qs.execution_count AS 'NumEjecuciones', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo', (qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)', qs.total_worker_time/1000000.0 AS 'TiempoTotal(s)', qs.max_logical_reads AS 'LecturasLogicas', qs.max_logical_writes AS 'EscriturasLogicas', qs.total_physical_reads AS 'LecturasFisicas', DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() ORDER BY qs.execution_count DESCProcedimientos almacenados más costosos para la CPU
SELECT TOP 20 qt.text AS 'Nombre_SP', qs.total_worker_time/1000000.0 AS 'TiempoTotal(s)', (qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)', qs.execution_count AS 'NumEjecuciones', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo', qs.max_logical_reads AS 'LecturasLogicas', qs.max_logical_writes AS 'EscriturasLogicas', qs.total_physical_reads AS 'LecturasFisicas', DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() ORDER BY qs.total_worker_time DESCProcedimientos almacenados más costosos para la memoria
SELECT TOP 20 qt.text AS 'Nombre_SP', total_logical_reads AS 'TotalLecturasLogicas', qs.execution_count AS 'NumEjecuciones', (total_logical_reads/qs.execution_count)/1000000.0 AS 'PromedioLecturasLogicas', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo', (qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)', qs.max_logical_reads AS 'LecturasLogicas', qs.max_logical_writes AS 'EscriturasLogicas', qs.total_physical_reads AS 'LecturasFisicas', DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() ORDER BY total_logical_reads DESCProcedimientos almacenados más costosos para el disco (lecturas)
SELECT TOP 20 qt.text AS 'Nombre_SP', qs.total_physical_reads AS 'TotalLecturasFisicas', (qs.total_physical_reads/qs.execution_count)/1000000.0 AS 'PromedioLecturasFisicas', qs.execution_count AS 'NumEjecuciones', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo', qs.max_logical_reads AS 'LecturasLogicas', qs.max_logical_writes AS 'EscriturasLogicas', DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() ORDER BY qs.total_physical_reads DESCCabe destacar que las consultas están basadas en el uso de las funciones y vistas de administración dinámica (popularmente conocidas como DMV).
Antes de finalizar el artículo, me gustaría mencionar que parte de las consultas han sido obtenidas del blog sqlserverperformance, aunque las he adaptado y modificado para mayor comprensión de los lectores.
¡Enjoy T-SQL!



