Deadlock Troubleshooting, Part 3

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 table   à 3. Update Supplier table 3. Update Part table…

45

Deadlock Troubleshooting, Part 2

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 called us for help with.  To set up the scenario,…

5

Deadlock Troubleshooting, Part 1

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, cancels that spid’s current batch, and rolls backs his…

81

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

Yet Another Blog

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 exactly People magazine-type fare that would entertain the masses.  Recently, though, I…

6