How to find Longest running queries in SQL Server

How to find Longest running queries in SQL Server Read more →

Lots of NULL values can cause trouble

There isn’t a limit to the number of NULLs allowed in data sets but having more NULLs than you think you need may be a sign that it’s time to review. Some applications require a high amount of NULLs. It is considered to be acceptable and is a built-in expectation. For example, I was working on a large ETL process and the staging server allowed NULLs as it fused data from different sources. This worked fine. But there were clean up processes which deal with the NULLs . There were post Import scripts which dealt with NULLs in preparation for... Read more →

ALTER INDEX REBUILD clears sys.dm_db_index_usage_stats

Question: I’m experiencing some strange behaviour with response from the SQL Server DMV sys.dm_db_index_usage_stats. I understand the sys.dm_db_index_usage_stats are refreshed every time there is a SQL Server restart. This is documented behaviour , which I build in to my understanding of how to interpret these statistics The strange behaviour is when I execute a ALTER INDEX REBUILD and then check the sys.dm_db_index_usage_stats, the usage stats for the index disappear. I’m using SQL Server 2012 Enterprise SP2 Answer: It sounds like you’re experiencing a bug which appeared in SQL Server 2012 and was fixed in SQL Server 2012 Service Pack 3... Read more →

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 →