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 Popular search terms:sql server blocked queries– Get most blocked queriesmost blocked query sql 2000most blocked search termsscript most blocked queries sql server 2008
Archive of posts filed under the DMV Queries category.
Find Query Reuse (Execution Plan Reuse)
– 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 <= 1 –and objtype != ‘adhoc’ –and objtype != ‘prepared’ ) t WHERE SNAME IS NOT NULL order by usecounts, size_in_bytes desc Popular search terms:tool to check plan reuse SQLsqlserver execution plan [...]
Find Table Fragmentation
DECLARE @Database VARCHAR(255) DECLARE @TableName VARCHAR(255) DECLARE @IndexName VARCHAR(255) SET @Database = ‘SQLServerPlanet’ SET @TableName = ‘Users’ SET @IndexName = NULL SELECT avg_fragmentation_in_percent ,page_count FROM sys.dm_db_index_physical_stats ( DB_ID(@Database) ,OBJECT_ID(@TableName) ,OBJECT_ID(@IndexName) ,NULL ,NULL ) Or you can still do it the old fashioned way. Just substitute the name of the table or index below. It should [...]
Find User Connection Count
– Show users with highest connections SELECT login_name, session_count FROM( SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name ) t ORDER BY session_count desc In SQL Server 2008 you can also find out how many connections have been created to SQL Server since the last time it got restarted: SELECT @@Connections Popular search [...]
Finding the total virtual, shared and AWE memory used
This query shows: Amount of memory allocated to the buffer pool Amount of memory consumed by BPool Amount of memory used by AWE SELECT SUM(single_pages_kb + virtual_memory_committed_kb + shared_memory_committed_kb + awe_allocated_kb) as [Used by BPool with AWE, Kb] FROM sys.dm_os_memory_clerks WHERE type = ‘MEMORYCLERK_SQLBUFFERPOOL’ Popular search terms:SQL awe check amount of memory usedawe memorymemoryclerk_sqlbufferpoolsingle_pages_kb virtual_memory_committed_kbquery [...]
