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: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP. There was an interesting question asked by Mark Storey-Smith on dba.stackexchange.com back in October 2011.  He was looking at the execution plan for a query that counts a million rows from a virtual auxiliary table of ...

There are interesting things to be learned from even the simplest queries.  For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but fewer than ten. One possible query to meet that specification is: SELECT ...

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

Many people believe that whenever SQL Server creates an execution plan that uses parallelism, an alternative serial plan is also cached.  The idea seems to be that the execution engine then decides between the parallel and serial alternatives at runtime. I’ve seen this on forums, in blogs, and even in books.  In fairness, a lot of the ...

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