Row Goals Gone Rogue

  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 TABLE even (c1 int, c2…

1

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 T-SQL. Some of the most authoritative posts, like this one, come to the following conclusions: (a) You cannot depend on expression evaluation order for things like “WHERE <expr1> OR <expr2>“, since the optimizer might choose a plan that evaluates…

1

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 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. Just in case,…

7

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 that happened to use really long strings to identify entity instances. These identity strings had theoretically unbounded length, but in practice they were never more than 1000-2000 characters. The database layer needed to accept an identifier of this sort…

5

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 / query plan hash) features that were added in SQL Server 2008.  A fingerprint identifies all queries or all plans that have the same “shape”.  With query fingerprints you can get the cumulative cost of all executions of a query…

1

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 really do think that people should seriously question whether it’s wise to use T-SQL User Defined Data Types.  Mostly, though, I just like that Edwin Starr song.)    Let me start this post off by saying that…

7

SQL Server Sizing Resources

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, answer a couple of questions about things like concurrent user counts and desired CPU utilization…

1

Purging Data

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…

4

Finding procedure cache bloat

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 queries faster by avoiding unnecessary compilation when the “same” query is run repeatedly with different parameters, and it can reduce…

1

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 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 be associated with efficient and consistent execution, but performance is…

11