Find Resource Currently Involved in Blocking

[cc lang=”sql”] SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id [/cc] Continue reading ...

Find Queries Using Most CPU

[cc lang=”sql”] SELECT TOP 5 object_name(objectID) ,[Avg CPU Time] = total_worker_time/execution_count ,execution_count ,Plan_handle ,query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC; [/cc]

Find Most Executed Queries

This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV. [cc lang=”sql”] SELECT TOP 50 qs.execution_count ,OBJECT_NAME(objectid) ,query_text = 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) ,qt.dbid ,dbname = db_name(qt.dbid) ,qt.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY qs.execution_count DESC [/cc] Continue reading ...

Find Most Blocked Queries

While this query will expose the queries that are most blocked, it is somewhat of an guestimate. What shows up from this query is the procedures whose wait times cannot be explained by looking at the total CPU cycles. While this will mostly infer blocking, it doesn’t necessarily guarantee that’s the issue. It can also show procedures that suffer from other wait types (disk, network, clr, parallelism, etc). [cc lang=”sql”] SELECT OBJECT_NAME(objectid) ,BlockTime = total_elapsed_time – total_worker_time ,execution_count ,total_logical_reads FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) ORDER BY total_elapsed_time – total_worker_time DESC [/cc]

Find Query Reuse (Execution Plan Reuse)

[cc lang=”sql”] — find counts of query re-use SELECT * FROM ( select *, (select object_name(objectid) from sys.dm_exec_sql_text(p.plan_handle)) AS SNAME from sys.dm_exec_cached_plans p where usecounts Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!
css.php