SQL Server 2005 Diagnostic Information Queries - Part 2
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
– Also gives you an idea which indexes are most active