Hypothetical Indexes

Question: I was reviewing a list of sql indexes from a database and spotted some indexes prefixed with “_dta_index”. What are they ? How did they get here? And how can I clean them up. Answer: These are hypothetical indexes created by the Database Tuning Advisor (DTA). To find the indexes use the following the query Use MyDB GO select * from sys.indexes where is_hypothetical = 1 If DTA is allowed to complete gracefully , the DTA cleans up the indexes. If the DTA crashes such as shutting down unexpectedly then the hypothetical indexes remain in place – under these... Read more →

Query to find non – sargable sql statements – SQL Antipattern #009

When a predicate is SARGABLE we mean the sql statement is able to exploit the indexes supporting the sql statement. If the sql statemet is non – SARGABLE – we mean the statement is not capable of exploiting the indexes If a statement is non-SARGABLE it can return the correct data set – as intended by the developer – but the response rate can be slower than expected. As the statement cannot exploit the index – it’s forced into a full scan. For the smaller data sets this may not appear to be a problem, but as the data sets... Read more →

How to monitor progress of Online Index Rebuild

After posting How to monitor progress of Offline Index Rebuild , I received an email asking how to monitor an Online Index Rebuild. If I need to monitor the progress of an Online Index Rebuild it is usually to estimate how much time may be left. One of the benefits of an Online Index Rebuild is the capacity to maintain access to the Index , but one of the costs is the maintenance of two index trees simultaneously . This can cause some resource contention The Progress Report: Online Index Operation event class is exposed through Profiler and will supply... Read more →

How to monitor progress of Offline Index Rebuild

Question: I’ve created a list of Non_clustered indexes , which were formerly disabled. I’ve used the script in this link.SQL Server Disable Indexes and Rebuild Indexes dynamically The method I’ve used is for an Offline Index rebuild. The table has 250 million rows with 17 non-clustered indexes, therefore they are taking a long time to rebuild. What method(s) can I use to monitor the progress of the Index rebuild? Answer: Offline Index rebuilds are slightly trickier than Online to monitor progress. In your circumstances there are a couple of methods to use. As the non clustered indexes were disabled prior... Read more →

Find Table Scans in Query Plan Cache

Question: I’m managing a SQL Server with queries running much slower than normal. I’ve analysed the sys.dm_db_index_usage_stats for heap scans. To identify the heap statistics I identified rows where the column index_id returned a value of zero. I’ve identified some heaps with a very high level of usage . How can I find the queries which are creating these large amounts of user scans? Answer: Using the sys.dm_db_index_usage_stats is a good method to identify heaps. Checking the user scans is a good way of finding clues for inefficient queries. It does not guarantee these queries are the root cause, but... Read more →