Optimización en SQL Server: Consultas fundamentales

Frecuentemente nos podemos encontrar con problemas de rendimiento de nuestro servidor de bases de datos. Detectar donde se encuentra el cuello de botella y cuáles son las causas que lo producen se convierte en una tarea fundamental para cualquier DBA. ¿El problema se encuentra en la parte más física (CPU, memória, disco) del servidor? O por el contrario, ¿se encuentra en el diseño más lógico (diseño de las bases de datos, consultas, índices, etc.)
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 DESC

Procedimientos 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 DESC

Procedimientos 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 DESC

Procedimientos 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 DESC

Cabe 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!