Appendix A: Monitoring SQL Server health

You can run the following query to get all DMV and DMF names:

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

A CPU bottleneck is often caused by a non-optimal query plan, a poor configuration, poor design factors, or insufficient hardware resources. The following are some commonly used queries to help you identify what causes a CPU bottleneck.

The following query gives you a high-level view of which currently cached batches or procedures are using the most 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

The following query shows the aggregate CPU usage by cached plans with SQL text.

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 

The following query shows the top 50 SQL statements with high average CPU consumption.

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

The following shows DMV queries to find out excessive compiles/recompiles.

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

The following sample query gives you the top 25 stored procedures that have been recompiled. The plan_generation_num indicates the number of times the query has recompiled.

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

An inefficient query plan may cause increased CPU consumption.

The following query shows which query is using the most cumulative CPU.

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

The following query shows some operators that may be CPU intensive, such as ‘%Hash Match%’, ‘%Sort%’ to look for suspects.

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%'

If you have detected inefficient query plans and that cause high CPU consumption, run UPDATE STATISTICS on the tables involved in the query and check to see if the problem persists. Then, gather the data and report the problem to PerformancePoint Planning support.

If your system has excessive compiles and recompiles, it could result in a CPU-bound performance problem on the system.

You can run the following DMV queries to find out excessive compiles/recompiles.

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

The following sample query gives you the top 25 stored procedures that have been recompiled. The plan_generation_num indicates the number of times the query has recompiled.

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

If you have detected excessive compilation or recompilation, gather as much data as you can and report it to Planning support.

Before you start memory pressure detection and investigation, make sure you have enabled the advanced options in SQL Server. Run the following query on the master database to turn on this option first.

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

Run the following query to check memory-related configuration options first.

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

Run the following DMV query to see the CPU, scheduler memory, and buffer pool information.

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

Identify I/O bottlenecks by examining the latch waits. Run the following DMV query to find I/O latch wait statistics.

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

Identify an I/O problem if your waiting_task_counts and wait_time_ms change significantly from what you see normally. It is important to get a baseline of performance counters and key DMV query outputs when SQL Server is running smoothly.

These wait_types can indicate whether your I/O subsystem is experiencing a bottleneck.

Use the following DMV query to find currently pending I/O requests. Execute this query periodically to check the health of I/O subsystem and to isolate physical disk(s) that are involved in the I/O bottlenecks.

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

The query usually returns nothing in the normal situation. You need to investigate further if this query returns some rows.

You can also find I/O bound queries by executing the following DMV query.

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

The following DMV query can be used to find which batches/requests are generating the most I/O. A DMV query like the following can be used to find the top five requests that generate the most I/Os. Tuning those queries will improve the system performance.

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

Run the following query to determine the blocking sessions.

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

Use this call to find out which SQL is returned by the blocking_session_id. For example, if the blocking_session_id is 87, run this query to get the SQL.

dbcc INPUTBUFFER(87)

The following query shows SQL waits analysis and top 10 resources waited on.

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

To find out which spid is blocking another spid, create the following stored procedure in your database and then execute the stored procedure. This stored procedure reports the blocking situation. Type sp_who to find out @spid; @spid is optional parameter.

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)

The following are examples of using this stored procedure.

exec sp_block
exec sp_block @spid = 7

This topic is included in the following downloadable book for easier reading and printing:

• Planning Server Operations Guide for PerformancePoint Server 2007

See the full list of available books at Downloadable content for PerformancePoint Planning Server.

Tasks

Recover Planning Server with SQL Server

Concepts

Corruption of a SQL Server Analysis Services database