To get the query memory usage of currently executing queries run the following: SELECT TEXT ,query_plan ,requested_memory_kb ,granted_memory_kb ,used_memory_kb FROM sys.dm_exec_query_memory_grants emg CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle) ORDER BY emg.requested_memory_kb DESC Popular search terms:sql server 2008 memory usage dmvsql server 2005 dmv memory usagesql query memory usage sql serverdmv memory usagesql server memory usage [...]
Archive of posts tagged DMV
Index Usage DMV
The following DMV query retrieves the usage statistics for existing indexes. User Seeks – A high number indicates a well utilized index. User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns User Lookups – Indicates a different index was used for criteria and the actual [...]
Choosing the Best Clustered Index
One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the queries for a [...]
Find Resource Currently Involved in Blocking
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 Popular search terms:find blocking using dmvfind resource sql server blockingfinding resource in sql blockinghow to find blcoking in sql server using dmvidentify blocking using dmv
Find Most Executed Queries
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 Popular search terms:most executed queriesfind most executed queries sql servermost executed query sql [...]
Find Most Blocked Queries
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
