Bart Duncan's SQL Weblog

Filtered Indexes and Forced Parameterization: Can't we all just get along?

We have a database here that stores information about Azure SQLDB health issues; it was chugging...

Author: bartduncan Date: 02/08/2018

Row Goals Gone Rogue

This post discusses “row goals“, but with a twist. The point is to illustrate how row goals can...

Author: bartduncan Date: 03/14/2012

Don’t depend on expression short circuiting in T-SQL (not even with CASE)

There are a fair number of blog posts and forum discussions regarding expression short circuiting in...

Author: bartduncan Date: 03/03/2011

Query Tuning Fundamentals: Density, Predicates, Selectivity, and Cardinality

A few days ago I was in the middle of writing up a quick post about a query performance problem I...

Author: bartduncan Date: 01/25/2011

Living with SQL's 900 Byte Index Key Length Limit

We recently had a situation where we needed to interface with an external non-relational data source...

Author: bartduncan Date: 01/06/2011

Query Hash Statistics, a query cost analysis tool, now available for download

Some time ago I described the query fingerprint and query plan fingerprint (a.k.a. query hash /...

Author: bartduncan Date: 11/03/2010

T-SQL UDTs. (Huh!) What are they good for?

(The title of this post might seem a little inflammatory, but it’s not just a troll -- I...

Author: bartduncan Date: 08/25/2010

SQL Server Sizing Resources

Recently, I was asked whether Microsoft had any SQL Server hardware sizing tools. The asker referred...

Author: bartduncan Date: 06/16/2010

Purging Data

This post is about deleting a lot of rows, as you might do in a data archiving or purging task. By...

Author: bartduncan Date: 06/01/2010

Finding procedure cache bloat

Explicitly parameterizing queries is a well-known best-practice for database app developers. There...

Author: bartduncan Date: 05/26/2010

Sometimes the Simplest Solution Isn't the Best Solution (The Optional Parameter Problem)

Programmers should naturally gravitate toward the simplest, most elegant solution. This is because...

Author: bartduncan Date: 05/03/2009

The Death of DateTime?

SQL Server 2008 added a new data type named “datetimeoffset”. This is similar to the old datetime...

Author: bartduncan Date: 03/31/2009

Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"

I've received a couple of questions in email and in comments about deadlocks involving...

Author: bartduncan Date: 09/24/2008

Defining Complex Server "Health" Policies in SQL 2008

Policy Based Management (PBM) is a new feature in SQL Server 2008 that allows you to define a set of...

Author: bartduncan Date: 09/11/2008

Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You've Never Heard Of)

In versions of SQL Server before SQL Server 2008, it can be difficult to determine the cumulative...

Author: bartduncan Date: 09/03/2008

A simpler way to convert a hexadecimal string to varbinary

This isn't perf-related like most of my earlier posts, but I thought it was useful enough that I...

Author: bartduncan Date: 07/25/2007

Are you using SQL's Missing Index DMVs?

Did you know that your SQL Server is keeping track of the indexes that it thinks you should create?...

Author: bartduncan Date: 07/19/2007

Deadlock Troubleshooting, Part 3

Here’s an example of the classic scenario that is usually used to introduce the concept of a...

Author: bartduncan Date: 09/25/2006

Deadlock Troubleshooting, Part 2

In this post I’ll look at an actual deadlock, then troubleshoot it using the steps I described in...

Author: bartduncan Date: 09/12/2006

Deadlock Troubleshooting, Part 1

A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so...

Author: bartduncan Date: 09/08/2006

Joins Dissected on CraigFr's blog

CraigFr has a great series of posts in his blog describing the difference between the various...

Author: bartduncan Date: 08/16/2006

SELECT from a view slower than "equivalent" SELECT from the base table

Sometime we get complaints that a query is slower than it could be because a filter isn’t pushed...

Author: bartduncan Date: 08/14/2006

Wide vs. Narrow Plans

Here's another case where you might see intermittently poor performance that is "by design". Suppose...

Author: bartduncan Date: 07/27/2006

Limited Statistics Granularity

To set up this scenario, run the script below: USE tempdbGOIF OBJECT_ID ('test1') IS NOT NULL DROP...

Author: bartduncan Date: 07/25/2006

Why a bad plan isn't necessarily a bug

Everyone that has worked with databases for long enough has run into situations where the query...

Author: bartduncan Date: 07/25/2006

Yet Another Blog

I have resisted creating a blog for two reasons: 1) there are never enough hours in the day, and 2)...

Author: bartduncan Date: 06/09/2006