I love SQL Server execution plans. It is often easy to spot the cause of a performance problem just by looking at one. The task is considerably easier if the plan includes run-time information (a so-called ‘actual’ execution plan), but even a compiled plan can be very useful. Nevertheless, there are still times where the execution plan does not ...

Summary: If you use MERGE, indexed views and foreign keys, your queries can return incorrect results. Microsoft have released a fix for incorrect results returned when querying an indexed view. The problem applies to: SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 The Knowledge Base article does not go into much detail, ...

Can DELETE operations cause pages to split?  Yes.  It sounds counter-intuitive on the face of it; deleting rows frees up space on a page, and page splitting occurs when a page needs additional space.  Nevertheless, there are circumstances when deleting rows causes them to expand before they can be deleted.  The mechanism at ...

SQL Server 2005 onward caches temporary tables and table variables referenced in stored procedures for reuse, reducing contention on tempdb allocation structures and catalogue tables.  A number of things can prevent this caching (none of which are allowed when working with table variables): Named constraints (bad idea anyway, since ...

Ask anyone what the primary advantage of temporary tables over table variables is, and the chances are they will say that temporary tables support statistics and table variables do not.  This is true, of course; even the indexes that enforce PRIMARY KEY and UNIQUE constraints on table variables do not have populated statistics associated with ...

This is the final part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia. Links to other parts of this series: Part 1 Part 2 Part 3 Beating the Optimizer ...

This is the third part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia. Links to other parts of this series: Part 1 Part 2 Part 4 Storage of Alternative ...

This is the second part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia. Links to other parts of this series: Part 1 Part 3 Part 4 Cost-Based Optimization ...

This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia. Links to other parts of this series: Part 2 Part 3 Part 4 Introduction The motivation ...

In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan. Trace Flags We will need three undocumented trace flags.  The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab. The second trace flag is ...