Find Busy MS SQL Databases

From Hostek.com Wiki
Jump to: navigation, search


Here are a few SQL queries that will help track down a busy database.


Find High Read and Write Databases

This SQL query will return the Read, Write and sql_handle values. The sql_handle will be a real long combination of numbers and characters.

SELECT total_logical_reads, total_logical_writes, sql_handle
FROM sys.dm_exec_query_stats deqs ORDER BY total_logical_writes DESC 

Then once you have the sql_handle, you can use this query to get deeper details into what databases are associated with that sql_handle The sql_handle below is just an example sql_handle. Replace it with the sql_handle from above.

SELECT DB_NAME(dbid) AS DatabaseName, stats.execution_count, stats.total_logical_reads, stats.total_logical_writes, stats.total_worker_time, [text] FROM 
sys.dm_exec_query_stats stats CROSS APPLY sys.dm_exec_sql_text(stats.plan_handle) WHERE stats.sql_handle = 
0000123000ABCDEFG012345662F79CED6831812AFE8B0853F2E570000000000000000000000000000000000000000;