This post discusses “ row goals “, but with a twist. The point is to illustrate how row goals can cause unnecessarily slow queries. First, run this script: USE tempdb GO IF OBJECT_ID ('even') IS NOT NULL DROP TABLE even; IF OBJECT_ID ('odd') IS NOT NULL DROP TABLE odd; GO CREATE...

A few days ago I was in the middle of writing up a quick post about a query performance problem I recently ran into. The writeup referenced predicate selectivity , and I found myself wondering whether everyone who came across the post would have a good intuitive understanding of what that referred to...

This post is about deleting a lot of rows, as you might do in a data archiving or purging task. By “a lot of rows”, I mean anything from a few thousand rows up to billions. This may seem elementary, but there are some surprising problems that can appear if you use a simple DELETE to delete...

Programmers should naturally gravitate toward the simplest, most elegant solution. This is because the simplest coding solution is so often the best solution: simple solutions are cheaper to implement; easier for others to understand, maintain, and extend; and less prone to bugs. Simplicity can also...

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create? The "missing index" DMVs in SQL are a really great new feature in SQL Server 2005 that (in my opinion) seem to have been underutilized so far. If you want to see if this feature can spare you the tedium...

Here’s an example of the classic scenario that is usually used to introduce the concept of a deadlock in a database: Process A Process B 1. Begin Transaction 1. Begin Transaction 2. Update Part table 2. Update Supplier...

CraigFr has a great series of posts in his blog describing the difference between the various logical and physical join types, the details of how SQL Server implements these joins, and the things that the query optimizer takes into account when selecting a join type. These five posts are a wonderful...

Sometime we get complaints that a query is slower than it could be because a filter isn’t pushed very deeply down into a plan. For example, consider this hypothetical poor performance scenario (my apologies in advance for the lack of normalization): USE tempdb IF OBJECT_ID ('Sales') IS NOT NULL...

Here's another case where you might see intermittently poor performance that is "by design". Suppose you see that a delete, insert, or update query in a stored proc usually runs quickly, but occasionally the query takes much longer to complete. You captured a detailed profiler trace of both the good...