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

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

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

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

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 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 a representative time period, then post-process the trace with a utility…

4

Joins Dissected on CraigFr's blog

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 read.     Overview of Logical Join…

0

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 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 DROP TABLE Sales IF OBJECT_ID…

5

Wide vs. Narrow Plans

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 and bad periods, including the Showplan…

5

Limited Statistics Granularity

To set up this scenario, run the script below: USE tempdbGOIF OBJECT_ID (‘test1’) IS NOT NULL DROP TABLE test1GOCREATE TABLE test1 (c1 tinyint, c2 smallint)DECLARE @x intDECLARE @msg varchar(1000)SET @x = 1SET NOCOUNT ONBEGIN TRANWHILE (@x <= 1000000)BEGIN  INSERT INTO test1 (c1, c2) VALUES (@x % 255, CASE WHEN @x % 1000 = 500 THEN…

10

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 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 some other query hint to get a much faster plan.  But you’re reluctant…

0