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 →

Forceseek table hint to the rescue

SQL Forceseek table hint was added in SQL Server 2008 , but it was until recently I had a legitimate use for it. I was in the midst of a large data archiving job and the runtimes were slower than expected. Checking the execution plans there were some mysterious index scans. I was confused. It was a highly selective field , so I expected the index seek to occur. I doublechecked the sql statistics were updated and cleared the cache . I would have expected SQL Server to guess during the runtime and generate an index seek, but this was... Read more →

How to find Nested Loops in Execution Plans for a specific table

What is a nested loop in SQL Server execution plan & Nested Loops SQL Execution Plan 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 →

Memory grants and execution plans

Question: When reviewing some executions plans for slow running queries, I’ve noticed Memory Grant appearing. What is the Memory grant message? Answer: As an example: When the SQL Optimizer predicts it will operate on 15 rows based on the latest statistics for those tables , a chunk of memory is granted to fulfil the operation. But, if the statistics are inaccurate or not up to date, and the operation returns 10000 rows , the operation doesn’t automatically grant the extra memory. The operation will spill to disk – in the tempdb. Spilling to disk will allow the sorting and joining... Read more →