Find Queries Performing Most Writes (Inserts)

   SELECT TOP 10         ‘Procedure’         = qt.text         ,DiskReads          = qs.total_physical_reads   — The worst reads, disk reads         ,MemoryReads        = qs.total_logical_reads    –Logical Reads are memory reads         ,Executions         = qs.execution_count         ,CPUTime            = qs.total_worker_time         ,DiskWaitAndCPUTime = qs.total_elapsed_time         ,MemoryWrites       = qs.max_logical_writes         ,DateCached         = qs.creation_time         ,DatabaseName       = DB_Name(qt.dbid)         ,LastExecutionTime  = qs.last_execution_time     FROM sys.dm_exec_query_stats AS qs     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt     ORDER BY qs.max_logical_writes DESC

Continue reading ...

Find Memory Usage of Executing Procedures

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

Continue reading ...

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 data was looked up from this index for the select list User Updates – Number of times the index was updated with additional records SELECT     ObjectName      = object_schema_name(idx.object_id) + ‘.’ + object_name(idx.object_id)     ,IndexName      = idx.name     ,IndexType      = CASE                         WHEN is_unique = 1 THEN ‘UNIQUE ‘                         ELSE ” END + idx.type_desc     ,User_Seeks     = us.user_seeks     ,User_Scans     = us.user_scans     ,User_Lookups   = us.user_lookups     ,User_Updates   = us.user_updates FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats us     ON idx.object_id = us.object_id     AND idx.index_id = us.index_id     AND us.database_id = db_id() WHERE object_schema_name(idx.object_id) != ‘sys’ ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC

Continue reading ...

Missing Indexes DMV

This query will show the indexes that are missing ordered according to those having the most impact. It will also provide the create index script needed in order to help you create the index. SELECT mid.statement   ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),   ‘CREATE INDEX [missing_index_’ + CONVERT (VARCHAR, mig.index_group_handle) + ‘_’ + CONVERT (VARCHAR, mid.index_handle)   + ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’   + ‘ ON ‘ + mid.statement   + ‘ (‘ + ISNULL (mid.equality_columns,”)     + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END     + ISNULL (mid.inequality_columns, ”)   + ‘)’   + ISNULL (’ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,   migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC What you need to know about this script however is what it does not account for. It does not account for an index that should be clustered. One of the warning signs that an index should be clustered is when this query suggests to you an index that contains a lot of columns (or has a lot of include columns). It is suggesting that, because it does not want to do a bookmark lookup to get the columns […]

Continue reading ...

Find Long Running Queries

SELECT TOP 10 ObjectName          = OBJECT_NAME(qt.objectid) ,DiskReads          = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads        = qs.total_logical_reads  –Logical Reads are memory reads ,Executions         = qs.execution_count ,AvgDuration        = qs.total_elapsed_time / qs.execution_count ,CPUTime            = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites       = qs.max_logical_writes ,DateCached         = qs.creation_time ,DatabaseName       = DB_Name(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_elapsed_time DESC

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!