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. [cc lang=”sql”] 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 [/cc] 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 it needs for the select list. In those cases, […]

Continue reading ...

Find Long Running Queries

[cc lang=”sql”] 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 [/cc]

Continue reading ...

Find Queries Taking Most CPU (Processor)

If your box is CPU bound then this is the script you need. The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. [cc lang=”sql”] — Find queries that take the most CPU overall SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = 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 ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,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.total_worker_time DESC — Find queries that have the highest average CPU usage SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = 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 ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,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.total_worker_time/qs.execution_count DESC [/cc]

Continue reading ...

Find Queries Using Most Memory (IO)

This query returns back the queries that use the most IO. This can mean that either the query is reading from disk more than usual or occupying and utilizing a large amount of buffer cache. These are typical symptoms of queries that do not have the proper indexes or queries that simply read a lot of data. [cc lang=”sql”] /********************************************************** * top procedures memory consumption per execution * (this will show mostly reports & jobs) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) — The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) –Logical Reads are memory reads ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ) T ORDER BY IO_Per_Execution DESC /********************************************************** * top procedures memory consumption total * (this will show more operational procedures) ***********************************************************/ SELECT TOP 100 * FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) — The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) –Logical Reads are memory reads ,Total_IO_Reads = SUM(qs.total_physical_reads + qs.total_logical_reads) ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + […]

Continue reading ...

SQL Server 2008 Minimally Logged Inserts

SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the requirements below are met. Trace flag 610 must be on Database recovery model must be bulk-logged or Simple Inserted data must be ordered by the clustered index To turn on the trace flag for your current session: [cc lang=”sql”] DBCC TRACEON (610) INSERT INTO dbo.MyTable SELECT * FROM ORDER BY 1 DBCC TRACEOFF (610) [/cc] This new change differs dramatically from the previous requirements for minimal logging. Previously there could be no clustered index and a table lock had to be acquired on the target table. For more information, visit: Minimal Logging Changes – MSDN Blog

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!
css.php