Apéndice A: Supervisión del estado de SQL Server

Puede ejecutar la consulta siguiente para obtener todos los nombres de DMV y DMF:

SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name

A menudo, un cuello de botella de CPU suele ser consecuencia de un plan de consulta no óptimo, una configuración o factores de diseño deficientes, o de insuficientes recursos de hardware. Las siguientes consultas se suelen utilizar para ayudarle a identificar las causas que producen un cuello de botella de CPU.

La siguiente consulta proporciona una vista de alto nivel de los lotes o procedimientos almacenados actualmente en la memoria caché que están utilizando la mayor parte de la CPU.

SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

La siguiente consulta muestra el uso agregado de la CPU mediante planes almacenados en caché con texto de SQL.

SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset, 
            --MAX(statement_end_offset) AS statement_end_offset
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 

La siguiente consulta muestra las 50 instrucciones SQL cuyo promedio de utilización de la CPU es más alto.

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

El siguiente ejemplo muestra las consultas DMV para averiguar las compilaciones y recompilaciones excesivas.

select * from sys.dm_exec_query_optimizer_info
where 
      counter = 'optimizations'
      or counter = 'elapsed time'

La siguiente consulta de ejemplo muestra los 25 principales procedimientos almacenados que se han vuelto a compilar. plan_generation_num indica el número de veces que se ha vuelto a compilar la consulta.

select top 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid 
from sys.dm_exec_query_stats a
      cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

Un plan de consulta ineficiente puede provocar una mayor utilización de la CPU.

La siguiente consulta muestra la consulta que más utiliza la CPU de forma acumulada.

SELECT 
    highest_cpu_queries.plan_handle, 
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from 
    (select top 50 
        qs.plan_handle, 
        qs.total_worker_time
    from 
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

La siguiente consulta muestra algunos operadores que pueden hacer un uso intensivo de la CPU como, por ejemplo, ‘%Hash Match%’, ‘%Sort%’ para la búsqueda de elementos sospechosos.

select *
from 
      sys.dm_exec_cached_plans
      cross apply sys.dm_exec_query_plan(plan_handle)
where 
      cast(query_plan as nvarchar(max)) like '%Sort%'
      or cast(query_plan as nvarchar(max)) like '%Hash Match%'

Si ha detectado planes de consultas ineficientes y que pueden provocar una alta utilización de la CPU, ejecute UPDATE STATISTICS en las tablas implicadas en la consulta y compruebe si el problema persiste. A continuación, recopile los datos e informe del problema al soporte técnico de PerformancePoint Planning.

Si el sistema tiene excesivas compilaciones y recompilaciones, puede provocar un problema de rendimiento limitado de la CPU en el sistema.

Puede ejecutar las siguientes consultas DMV para averiguar si hay excesivas compilaciones y recompilaciones.

select * from sys.dm_exec_query_optimizer_info
where 
counter = 'optimizations'
or counter = 'elapsed time'

La siguiente consulta de ejemplo muestra los 25 principales procedimientos almacenados que se han vuelto a compilar. plan_generation_num indica el número de veces que se ha vuelto a compilar la consulta.

select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid 
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

Si ha detectado una excesiva compilación o recompilación, recopile tantos datos como sea posible e informe al soporte técnico de Planeación.

Antes de iniciar la detección e investigación de la presión de memoria, asegúrese de habilitar las opciones avanzadas de SQL Server. Ejecute la siguiente consulta en la base de datos principal para activar primero esta opción.

sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go

Ejecute la siguiente consulta para comprobar, en primer lugar, las opciones de configuración relacionadas con la memoria.

sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go

Ejecute la siguiente consulta DMV para ver la información de la CPU, el programador de memoria y el bloque del búfer.

select 
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info

Identifique los cuellos de botella de E/S mediante el examen de la espera de pestillos. Ejecute la siguiente consulta DMV para buscar las estadísticas de E/S de la espera de pestillos.

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type

Si waiting_task_counts y wait_time_ms cambian de forma significativa con respecto a lo que aparece normalmente, se tratará de un problema de E/S. Es importante establecer una línea de base de los contadores de rendimiento y obtener los resultados de las principales consultas DMV cuando SQL Server se ejecuta sin problemas.

Estos wait_types pueden indicar si está experimentando un cuello de botella del subsistema de E/S.

Use la siguiente consulta DMV para buscar las solicitudes de E/S pendientes. Ejecute esta consulta periódicamente para comprobar el estado del subsistema de E/S y para aislar los discos físicos implicados en los cuellos de botella de E/S.

select 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

Por lo general, la consulta no devuelve nada en situación normal. Si la consulta devuelve algunas filas, será necesario que investigue más a fondo.

También puede encontrar consultas asociadas con E/S mediante la ejecución de la siguiente consulta DMV.

select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
           (total_physical_reads/execution_count) as avg_physical_reads,
           Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
      cross apply sys.dm_exec_query_plan(plan_handle) p
      cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc

La siguiente consulta DMV se puede utilizar para buscar lotes/solicitudes que están generando la mayor parte de E/S. Se puede usar una consulta DMV similar a la siguiente para buscar las cinco principales solicitudes que generan la mayor parte de E/S. El ajuste de estas consultas mejorará el rendimiento del sistema.

select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by  (total_logical_reads + total_logical_writes) Desc

Ejecute la consulta siguiente para determinar las sesiones de bloqueo.

select blocking_session_id, wait_duration_ms, session_id from 
sys.dm_os_waiting_tasks
where blocking_session_id is not null

Use esta llamada para averiguar qué devuelve SQL mediante blocking_session_id. Por ejemplo, si blocking_session_id es 87, ejecute esta consulta para obtener el código SQL.

dbcc INPUTBUFFER(87)

La siguiente consulta muestra el análisis de esperas de SQL y los 10 principales recursos en espera.

select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc

Para averiguar qué spid está bloqueando otro spid, cree el siguiente procedimiento almacenado en la base de datos y, a continuación, ejecútelo. Este procedimiento almacenado informa sobre la situación de bloqueo. Escriba sp_who para averiguar el @SPID; @SPID es parámetro opcional.

create proc dbo.sp_block (@spid bigint=NULL)
as
select 
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id    
from 
    sys.dm_tran_locks as t1, 
    sys.dm_os_waiting_tasks as t2
where 
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = isnull(@spid,t1.request_session_id)

Los siguientes ejemplos muestran el uso de este procedimiento almacenado.

exec sp_block
exec sp_block @spid = 7

Tareas

Recuperar Planning Server con SQL Server

Conceptos

Daños en una base de datos de SQL Server Analysis Services