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

You may find reference material on the Internet that says don't depend on expression short circuiting in T-SQL except in CASE statements. It turns out that there are edge cases where even CASE doesn't provide deterministic short circuit behavior.

...

An overview of some concepts that are important to understand when examining query plans or troubleshooting slow queries.

...

This post lists some options that you might consider if you need to do an indexed lookup of values that are longer than SQL's 900 bytes limit on index keys.

...

Query Hash Statistics is a free download that collects historical query and query plan fingerprint statistics and allows you easily to view the data in SSMS. It allows you to see the true cumulative cost of the queries in each of your databases, without a Profiler trace.  You can use the project as-is, or – if you’re the tinkering type – disassemble it and build your own custom query hash stats solution.  Source code is included. 

...

User-defined data types (UDTs) in T-SQL seem alluring, but in reality they may fail to live up to their promise.

...

Recently, I was asked whether Microsoft had any SQL Server hardware sizing tools. The asker referred me to http://www.sizinglounge.com as an example of what he was looking for. (Sizing Lounge apparently allows you to select an app like SAP or Exchange...

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

Explicitly parameterizing queries is a well-known best-practice for database app developers. There are cases where it can make sense to execute a query unparameterized, but, in general, you should default to explicit parameterization. It can make your...

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

SQL Server 2008 added a new data type named “ datetimeoffset ”. This is similar to the old datetime data type, with the following significant differences: · Internally, the time is stored in unambiguous UTC format · The local time zone offset is...

I've received a couple of questions in email and in comments about deadlocks involving mysterious-sounding non-lock resources like " exchangeEvent " and " threadpool ". There are a couple of examples in the comments for post http://blogs.msdn.com/bartd...

Policy Based Management (PBM) is a new feature in SQL Server 2008 that allows you to define a set of policies that capture the intended state for a group of servers. For example, you could define a policy that says that your user databases should all...

In versions of SQL Server before SQL Server 2008, it can be difficult to determine the cumulative cost of the queries running on a server if the workload includes unparameterized queries. The only truly reliable method is to capture a Profiler trace during...

This isn't perf-related like most of my earlier posts, but I thought it was useful enough that I should share it. We recently had a situation where we had to convert a hexadecimal string representation of a binary value to a true binary (e.g. varchar...

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

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

In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in Deadlock Troubleshooting, Part 1 so you can see them in action. This is a simplified version of a deadlock scenario that an internal customer here at Microsoft...

A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed. When the deadlock monitor thread in SQL Server detects a circular blocking chain, it selects one of the participants as a victim...

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

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

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

To set up this scenario, run the script below: USE tempdb GO IF OBJECT_ID ('test1') IS NOT NULL DROP TABLE test1 GO CREATE TABLE test1 (c1 tinyint, c2 smallint) DECLARE @x int DECLARE @msg varchar(1000) SET @x = 1 SET NOCOUNT ON BEGIN TRAN WHILE...

Everyone that has worked with databases for long enough has run into situations where the query optimizer doesn't select the best possible plan. You may find that you can force SQL to use an index, choose a different join algorithm or join order, or use...

I have resisted creating a blog for two reasons: 1) there are never enough hours in the day, and 2) the areas that I know well enough to say something interesting about -- SQL Server performance and some other engine issues, mainly -- are esoteric, not...