Over the years I have spent working with SQL Server, the personal time investment that has repaid itself more than any other is becoming intimately familiar with execution plans and how they can be used to diagnose and correct performance problems. There Read More...

Introduction Back in January 2014, I wrote an article for SQLperformance.com describing the cardinality estimation process for queries with multiple predicates, from the point of view of the old and new cardinality estimators. The article describes the Read More...

Nested loops join query plans can be a lot more interesting (and complicated!) than is commonly realized. One query plan area I get asked about a lot is prefetching. It is not documented in full detail anywhere, so this seems like a good topic to address Read More...

It’s not often that a SQL Server bug really surprises me or makes me wonder how it was never spotted before, but this is one of those. To get right into it, say you have the following two tables: CREATE PARTITION FUNCTION PF ( integer ) AS RANGE RIGHT Read More...

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

Most tuning efforts for data-changing operations concentrate on the SELECT side of the query plan. Sometimes people will also look at important storage engine considerations like locking and transaction log throughput that can have dramatic effects. As Read More...

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

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

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, Read More...

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, Read More...

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, Read More...

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

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

This post is for SQL Server developers who have experienced the special kind of frustration, which only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan. This situation often occurs when making Read More...

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