DMV Queries That Will Help To Analyze Possible Bottlenecks in SQL Server 2005

Posted: December 16, 2008 in Sqlserver

DMV queries on a sql server 2005, It would be very useful to quickly analyze the workload and possible bottlenecks on a SQL server.


Query 1 shows which stored procedures are being called the most often

Query 2 shows the top 20 stored procedures sorted by total worker time (CPU pressure). This will tell you the most expensive stored procedures from a CPU perspective.

Query 3 shows the top 20 stored procedures sorted by total logical reads(Memory pressure). This will tell you the most expensive stored procedures from a memory perspective

Query 4 shows the top 20 stored procedures sorted by total physical reads(Read I/O pressure). This will tell you the most expensive stored procedures from a read I/O perspective.

Query 5 shows the top 20 stored procedures sorted by total logical writes(Write I/O pressure). This will tell you the most expensive stored procedures from a write I/O perspective.



Query 1

— Get Top 100 executed SP’s ordered by execution count

SELECT TOP 100 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

Query 2

— Get Top 20 executed SP’s ordered by total worker time (CPU pressure)

SELECT TOP 20 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

Query 3

— Get Top 20 executed SP’s ordered by logical reads (memory pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, total_logical_reads,

qs.execution_count AS ‘Execution Count’, total_logical_reads/qs.execution_count AS ‘AvgLogicalReads’,

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_reads, qs.max_logical_writes, qs.total_physical_reads,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’, qt.dbid

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

Query 4

— Get Top 20 executed SP’s ordered by physical reads (read I/O pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’,

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,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Age in Cache’, qt.dbid

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

Query 5

— Get Top 20 executed SP’s ordered by logical writes/minute (write I/O pressure)

SELECT TOP 20 qt.text AS ‘SP Name’, qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS ‘AvgLogicalWrites’,

qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS ‘Logical Writes/Min’,

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’,

qs.total_physical_reads/qs.execution_count AS ‘Avg Physical Reads’, qt.dbid

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

Here are a few more useful DMV queries that will give you a better idea which tables in your database are getting the most read and write activity :

— Tables with the most writes

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT TableName = object_name(s.object_id),

Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

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 = @dbid

GROUP BY object_name(s.object_id)

ORDER BY writes DESC

— Tables with the most reads

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT TableName = object_name(s.object_id),

Reads = SUM(user_seeks + user_scans + user_lookups), Writes = SUM(user_updates)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

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 = @dbid

GROUP BY object_name(s.object_id)

ORDER BY reads DESC

Advertisements

Comments are closed.