Tracing Deadlock Graphs: Extended Events or Server Side Trace

Deadlock graphs are incredibly helpful for figuring out why queries are getting automatically killed off by SQL Server. But it can be tricky to trace deadlocks in SQL Server. You might be tempted to use Profiler, but the application has lots of baggage. You’re better off using either Extended Events (which may be confusing), or a Server Side Trace (which you can script from Profiler– if you know exactly which buttons to push in the right order). You might look in the System Health trace on recent versions of SQL Server, but if the deadlock didn’t happen recently, then it may have rolled off the trace. To make this all easier, I’ve created a gist on GitHub sharing TSQL to save you a bunch of time. Choose the script that works for you. You can: Use a simple Extended Events trace to get deadlock graphs via the sqlserver.xml_deadlock_report event Use a Server…
Read More

Testing an Insert for Race Conditions with Ostress.exe

Whenever we have multiple sessions modifying data, things get tricky. When we have a pattern of “check if the data exists and then do a thing,” multiple sessions get even more complicated. It’s not very practical to try to open a bunch of sessions in SQL Server Management Studio to run commands in a loop. It’s hard to manage and if you’re like me, you find a way to crash SSMS doing it. It’s much easier to test for race conditions using the free OStress.exe tool from Microsoft. Download OStress.exe as part of the RML Utilities for SQL Server (x64) here. Our Example: Insert a Row if It Doesn’t Exist Already We have a simple table named dbo.RaceCondition with two columns: RaceConditionId and RaceConditionValue. RaceConditionId is the clustered Primary Key. A unique index on RaceConditionValue prevents duplicate rows from being inserted. Here’s how to create it: IF DB_ID('RaceConditionTesting') is not null BEGIN…
Read More

The Case of the Blocking Merge Statement (LCK_M_RS_U locks)

Recently I got a fun question about an “upsert” pattern as a “Dear SQL DBA” question. The question is about TSQL, so it lent itself to being answered in a blog post where I can show repro code and screenshots. Here’s the Scenario in the Anonymized Question We have a lookup table, which is the parent table in a foreign key relationship with a child table. The child table has lots more rows. When data comes in, we need to: Check the Parent Table (the lookup table) to see if the ParentValue is already present. If ParentValue is not present, insert a row for the new ParentValue into the Parent Table. This happens very rarely, but we need to check for it. Then insert a row into the Child Table, using the ParentId. The Problem: Blocking against the Parent Table When run under a few sessions, locking and blocking issues were creeping…
Read More

How to Script Out Indexes from SQL Server

Sometimes you need to script out all the indexes in a database. Maybe you’re concerned something has changed since they were last checked in. Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!) Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share. Enjoy! If you’ve got ideas about ways to improve this, I’d love to hear them in the comments. TSQL For Scripting Out All Indexes in a Database This will script out: Clustered and nonclustered indexes (including filtered indexes) The filegroup the index is created on (if not partitioned) The partition scheme the index is created on (if partitioned) Compression settings if the index is compressed–…
Read More

Why ROWLOCK Hints Can Make Queries Slower and Blocking Worse in SQL Server

  Sometimes when SQL Server gets slow, developers and DBAs find that the problem is blocking. After lots of work to identify the query or queries which are the blockers, frequently one idea is to add ROWLOCK hints to the queries to solve the problem or to disable PAGE locks on the table. This often backfires – here’s why. The Theory: ROWLOCK Makes Locks More Granular The idea behind the change is that by forcing SQL Server to take out row locks instead of page locks, each individual lock will be smaller. If locks are smaller, queries will be less likely to block one another. How It’s Implemented: Hints and ALTER INDEX SET ALLOW_PAGE_LOCKS=OFF By default, the  SQL Server engine will select row or page based locks based on what it thinks is best. You can coerce the database engine into using row based locking in two ways: TSQL hints, or by disallowing…
Read More

How to Check if an Index Exists on a Table in SQL Server

It seems like this should be easy. But it’s not. Code Should be Rerunnable – So You Need to Check if Indexes Exist Whenever you set up a script to create or drop an index, you want a safety check in there. Drop the index if it exists. Create the index if it doesn’t. Or do something else programmatically. Checking if an index exists is a pretty frequent task. But there’s no simple function to test if an index exists in SQL Server. Here’s what I’ll show you in this post: Example code to check if an index exists using OBJECT_ID. The code is simpler, but it requires a shared schema lock on the table you’re checking. Example code to check if an index exists just using joins. This uses less locking, but is wordier code. Examples of why simpler code doesn’t do the trick. Why the DROP_EXISTING=ON clause in…
Read More