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 ...

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 ...

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 ...

The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the same change reduced performance by around 70% (on 64-bit systems – more on that later).  ...

You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when ...

If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.  To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause.  Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned. The ...