SQL Server 2005 Diagnostic Information Queries

It has been a while since I posted the SQL Server 2005 version of this query, and with the release of SQL Server 2005 SP3 CU12 on Monday, I thought it would be a good time to put up the latest version.

– SQL Server 2005 Diagnostic Information Queries
– Glenn Berry
– October 2010
http://glennberrysqlperformance.spaces.live.com/
– Twitter: GlennAlanBerry

– SQL Version information for current instance
SELECT @@VERSION AS [SQL Version Info];

–   SQL 2005 SP2 is now an "unsupported service pack"
–   SQL 2005 SP2 Builds             SQL 2005 SP3 Builds
– Build       Description        Build       Description
– 3042        SP2 RTM             4035        SP3 RTM
– 3161        SP2 CU1              4207        SP3 CU1
– 3175        SP2 CU2              4211        SP3 CU2
– 3186        SP2 CU3              4220        SP3 CU3        
– 3200        SP2 CU4              4226        SP3 CU4        
– 3215        SP2 CU5              4230        SP3 CU5         
– 3228        SP2 CU6              4266        SP3 CU6       
– 3239        SP2 CU7              4273        SP3 CU7       
– 3257        SP2 CU8              4285        SP3 CU8
– 3282        SP2 CU9              4294        SP3 CU9
– 3294        SP2 CU10            4305        SP3 CU10
– 3301        SP2 CU11            4309        SP3 CU11
– 3315        SP2 CU12            4311        SP3 CU12
– 3325        SP2 CU13
– 3328        SP2 CU14
– 3330        SP2 CU15
– 3355        SP2 CU16
– 3356        SP2 CU17
– SP2 Branch is "retired"
 
– Hardware Information for SQL Server 2005
– (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info;

– Get configuration values for instance
SELECT name, value, value_in_use, [description]
FROM sys.configurations
ORDER BY name ;

– Focus on
– clr enabled (only enable if you need it)
– lightweight pooling (should be zero)
– max degree of parallelism
– max server memory (MB)
– priority boost (should be zero)

– File Names and Paths for TempDB and all user databases in instance
SELECT DB_NAME([database_id])AS [Database Name], [file_id],
       name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;

– Things to look at:
– Are data files and log files on different drives?
– Is everything on C: drive?
– Is TempDB on dedicated drives?
– Are there multiple data files?

– Calculates average stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC;

– Helps determine which database files on the entire instance have the most I/O bottlenecks

– Recovery model, log reuse wait description, and compatibility level for all databases on instance
SELECT [name], recovery_model_desc, log_reuse_wait_desc, [compatibility_level]
FROM sys.databases;

– Things to look at
– How many databases are on the instance?
– What recovery models are they using?
– What is the log reuse wait description?
– What compatibility level are they on?

– Clear Wait Stats
– DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR);

– Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (‘CLR_SEMAPHORE’,’LAZYWRITER_SLEEP’,’RESOURCE_QUEUE’,’SLEEP_TASK’
,’SLEEP_SYSTEMTASK’,’SQLTRACE_BUFFER_FLUSH’,’WAITFOR’, ‘LOGMGR_QUEUE’,’CHECKPOINT_QUEUE’
,’REQUEST_FOR_DEADLOCK_SEARCH’,’XE_TIMER_EVENT’,’BROKER_TO_FLUSH’,’BROKER_TASK_STOP’,’CLR_MANUAL_EVENT’
,’CLR_AUTO_EVENT’,’DISPATCHER_QUEUE_SEMAPHORE’, ‘FT_IFTS_SCHEDULER_IDLE_WAIT’
,’XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’, ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) – W1.pct < 99; — percentage threshold

– Common Significant Wait types with BOL explanations

– *** Network Related Waits ***
– ASYNC_NETWORK_IO        Occurs on network writes when the task is blocked behind the network

– *** Locking Waits ***
– LCK_M_IX                Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock
– LCK_M_IU                Occurs when a task is waiting to acquire an Intent Update (IU) lock
– LCK_M_S                Occurs when a task is waiting to acquire a Shared lock

– *** I/O Related Waits ***
– ASYNC_IO_COMPLETION  Occurs when a task is waiting for I/Os to finish
– IO_COMPLETION        Occurs while waiting for I/O operations to complete.
–                      This wait type generally represents non-data page I/Os. Data page I/O completion waits appear
–                      as PAGEIOLATCH_* waits
– PAGEIOLATCH_SH        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
–                      The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
– PAGEIOLATCH_EX        Occurs when a task is waiting on a latch for a buffer that is in an I/O request.
–                      The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
– WRITELOG             Occurs while waiting for a log flush to complete.
–                      Common operations that cause log flushes are checkpoints and transaction commits.
– PAGELATCH_EX            Occurs when a task is waiting on a latch for a buffer that is not in an I/O request.
–                      The latch request is in Exclusive mode.
– BACKUPIO                Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data

– *** CPU Related Waits ***
– SOS_SCHEDULER_YIELD  Occurs when a task voluntarily yields the scheduler for other tasks to execute.
–                      During this wait the task is waiting for its quantum to be renewed.

– THREADPOOL            Occurs when a task is waiting for a worker to run on.
–                      This can indicate that the maximum worker setting is too low, or that batch executions are taking
–                      unusually long, thus reducing the number of workers available to satisfy other batches.
– CX_PACKET            Occurs when trying to synchronize the query processor exchange iterator
–                        You may consider lowering the degree of parallelism if contention on this wait type becomes a problem

– Signal Waits for instance
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
       CAST(100.0 * SUM(wait_time_ms – signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats;

– Signal Waits above 10-15% is usually a sign of CPU pressure

– Get Average Task Counts (run multiple times)
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

– Sustained values above 10 suggest further investigation in that area

– Get CPU Utilization History (SQL 2005 Only)
DECLARE @ts_now bigint;
SET @ts_now = (SELECT cpu_ticks / CONVERT(float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info);

SELECT TOP(144) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value(‘(./Record/@id)[1]‘, ‘int’) AS record_id,
            record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]‘, ‘int’)
            AS [SystemIdle],
            record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]‘,
            ‘int’)
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
            AND record LIKE ‘%<SystemHealth>%’) AS x
      ) AS y
ORDER BY record_id DESC;

 

– Page Life Expectancy (PLE) value for default instance
SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = ‘SQLServer:Buffer Manager’ — Modify this if you have named instances
AND counter_name = ‘Page life expectancy’;

– PLE is a good measurement of memory pressure
– Higher PLE is better. Below 300 is generally bad.
– Watch the trend, not the absolute value

– Buffer Pool Usage for instance
SELECT TOP(20) [type], SUM(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
GROUP BY [type] 
ORDER BY SUM(single_pages_kb) DESC;

– CACHESTORE_SQLCP  SQL Plans         – These are cached SQL statements or batches that aren’t in
–                                     stored procedures, functions and triggers
– CACHESTORE_OBJCP  Object Plans      – These are compiled plans for stored procedures,
–                                     functions and triggers
– CACHESTORE_PHDR   Algebrizer Trees  – An algebrizer tree is the parsed SQL text that
–                                     resolves the table and column names

– Find single-use, ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N’Compiled Plan’
AND cp.objtype = N’Adhoc’
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

– Gives you the text and size of single-use ad-hoc queries that waste space in plan cache
– Enabling ‘optimize for ad hoc workloads’ for the instance can help (SQL Server 2008 and 2008 R2 only)
– Enabling forced parameterization for the database can help

 

– Switch to user database *******************
–USE YourDatabaseName;
–GO

– Individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

– Look at how large and how full the files are and where they are located
– Make sure the transaction log is not full!!

– Cached SP’s By Execution Count (SQL 2005)
SELECT TOP(25) qt.[text] AS [SP Name], qs.execution_count AS [Execution Count], 
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS [Age in Cache]
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() — Filter by current database
ORDER BY qs.execution_count DESC;

 

– Cached SP’s By Worker Time (SQL 2005) Worker time relates to CPU cost
SELECT TOP(25) qt.[text] AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.execution_count AS [Execution Count],
ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()), 0) AS [Calls/Second],
ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS [AvgElapsedTime],
qs.max_logical_reads, qs.max_logical_writes,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
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() — Filter by current database
ORDER BY qs.total_worker_time DESC;

 

– Cached SP’s By Logical Reads (SQL 2005) Logical reads relate to memory pressure
SELECT TOP(25) qt.[text] AS [SP Name], total_logical_reads, qs.max_logical_reads,
total_logical_reads/qs.execution_count AS [AvgLogicalReads], qs.execution_count AS [Execution Count],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.total_logical_writes,
qs.max_logical_writes, qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
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() — Filter by current database
ORDER BY total_logical_reads DESC;

– Top Cached SPs By Total Logical Writes (SQL 2005)
– Logical writes relate to both memory and disk I/O pressure
SELECT TOP(25) qt.[text] AS [SP Name], qs.total_logical_writes, qs.max_logical_writes,
qs.total_logical_writes/qs.execution_count AS [AvgLogicalWrites], qs.execution_count AS [Execution Count],
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime],
qs.total_worker_time AS [TotalWorkerTime],
qs.total_elapsed_time/qs.execution_count AS [AvgElapsedTime],
qs.total_physical_reads,
DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
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() — Filter by current database
ORDER BY total_logical_writes DESC;

– Lists the top statements by average input/output usage for the current database
SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name],
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],
SUBSTRING(qt.[text],qs.statement_start_offset/2,
    (CASE
        WHEN qs.statement_end_offset = -1
     THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
        ELSE qs.statement_end_offset
     END – qs.statement_start_offset)/2) AS [Query Text]   
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 [Avg IO] DESC;

– Helps you find the most expensive statements for I/O by SP

 

– Possible Bad Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
        user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
        user_updates – (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],’IsUserTable’) = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;

 

– Missing Indexes for entire instance by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

– Look at last user seek time, number of user seeks to help determine source and importance
– SQL Server is overly eager to add included columns, so beware

– Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName],
p.index_id, COUNT(*)/128 AS [buffer size(MB)],  COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;

– Tells you what tables and indexes are using the most memory in the buffer cache

– Detect blocking (run multiple times)
SELECT t1.resource_type AS [lock type],DB_NAME(resource_database_id) AS [database],
t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req],  — lock requested
t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],       — spid of waiter 
(SELECT [text] FROM sys.dm_exec_requests AS r                                    — get sql for waiter
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle])
WHERE r.session_id = t1.request_session_id) AS [waiter_batch],
(SELECT SUBSTRING(qt.[text],r.statement_start_offset/2,
    (CASE WHEN r.statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2
    ELSE r.statement_end_offset END – r.statement_start_offset)/2)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt
WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],    — statement blocked
t2.blocking_session_id AS [blocker sid],                         — spid of blocker
(SELECT [text] FROM sys.sysprocesses AS p                        — get sql for blocker
CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle])
WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt]
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address;

– When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name], 
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = ‘U’
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;   

– Helps discover possible problems with out of date statistics
– Also gives you an idea which indexes are most active

About these ads
This entry was posted in SQL Server 2005. Bookmark the permalink.

4 Responses to SQL Server 2005 Diagnostic Information Queries

  1. Keith Rowley says:

    Great post. Thank you so much for sharing these.

  2. cyberloco says:

    I find this very helpful, it’s all new to me i must confess, im gonna give it a try. Sometimes we have performance problems in our servers and we don’t make and accurate diagnosis of what is going on. I appreciate you help in this matter.

  3. AHPerez says:

    Thanks for updating this information, it is very helpful.

  4. krishna kanth kr says:

    the above queries are useful

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s