How to create a Deadlocked Scheduler Scenario at will

  First, please do not try this on your production server, because it will actually work. Second, this example is to illustrate how Deadlock scheduler issues can and do occur with the goal of increasing your understanding of the problem. Over the years of working with SQL Server, I learned that to understand a technology…


Meditation: Replicate Data from Multiple Instances into a Single Reporting Database

  Somebody approached me recently with a question like this: Question: We’ll be hosting our existing application in one data center, but we’ll be setting up 2 more instances in two different data centers. Is possible to use replication to move all the data into an single database so that we can report on all…


Procedure Execution with Table-Valued Parameters Involved Cannot be Replicated

  Somebody approached me with this error, asking whether this is by design or caused by certain conditions. Msg 25023, Level 16, State 1, Server <removed name>, Procedure sp_MSaddexecarticle, Line 192 Stored procedures containing table-value parameters cannot be published as ‘[serializable] proc exec’ articles.   Step 1: The first thing I wanted to do was…


Finding Large Transactions that Bloat Your Transaction Log

  I know this is a fairly common problem and there might be multiple solutions out there, but I figured adding another one may not hurt. An application I was supporting recently, was leading to 100s of GB of transaction log growth in spurts and we needed to discover what was causing the growth. I…


Dealing with Diacritics in Your Data

  Question: My customer wants to store data other than English in his table, using the Latin alphabet. For example, he has Spanish, German, Portuguese and English data. He wants to be able to return all the variations of the same character with one query, regardless of whether it has a diacritic mark (accent). For…


Choosing what SQLIO tests to Run and Automating the Tests

Many companies rely on IO stress-testing tools like SQLIO Disk Subsystem Benchmark Tool (aka SQLIO) to perform an I/O subsystem validation before they deploy SQL Server in production. Over the years, I have seen a variety of SQLIO tests being used and many of them are not really necessary because they do not mimic SQL…


Finding Performance Metrics (Duration, CPU, Reads) based on a XML Query Plan

  I was approached recently with the following request: “Hey Joseph, we think that this query is performing horribly. Here is the XML query plan. Can you tell us what is going on?” My first reaction was to start looking at the query plan and see if I can find an “obvious” problem. But I…


Dealing with Unique Columns when Using Table Partitioning

Recently, I had the opportunity to present table and index partitioning and a follow up question came up regarding partitioning a table with unique constraint/index. The Special Guidelines for Partitioned Indexes article describes the right approach   “Partitioning Unique Indexes   When partitioning a unique index (clustered or nonclustered), the partitioning column must be chosen…


Memory Grants Meditation: The mysterious SQL Server memory consumer with Many Names

The Memory Consumer with Many Names Have you ever wondered what Memory grants are? What about QE Reservations? And Query Execution Memory? Workspace memory? How about Memory Reservations? As with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory…


Recompile Meditation: Is a plan always cached?

I wanted to make this post because I think the term “recompile” is used to describe two slightly different concepts in SQL Server. Here are the two uses of the term “recompile”:   1. WITH RECOMPILE or OPTION (RECOMPILE) – in this context a new plan is compiled but not cached when a query runs….