March 23, 2016

Takeaway: The best way to avoid tempdb GAM and PFS contention caused by table-valued parameters (TVPs) is to use Memory-Optimized Table Variables.

This is my last post about tempdb. You can’t believe how wonderful it is to type that.*

SQL Server 2014 can eradicate tempdb contention and I didn’t even know it. Just yesterday I was saying that if I could have my one SQL Server wish, it would be to use table-valued parameters at high frequency without suffering from tempdb latch contention on GAM or PFS.

Any procedures that use the new table type BIGINTSET_OLTP will not touch tempdb.

It seems pretty easy doesn’t it? In this example, the reason I don’t just replace the existing type is because I want to be very deliberate about choosing memory optimized parameters. By adding a new table type, I make that choice in each procedure or query on a case by case basis.

To see whether this technique avoids hitting tempdb as advertised, I wrote this demo.

Look at the last two queries. The first one invokes a procedure that uses a memory-optimized table variable. The other one invokes a different procedure that uses a standard table variable. Then look at what’s going on in tempdb’s transaction log (using techniques found here). With the standard table variable I see this sort of activity in tempdb’s log:

But I see that memory-optimized queries avoids all tempdb activity. It’s not just the logging activity that’s avoided, all tempdb activity is avoided. The tempdb is simply not touched in this case.

Caveats

Notice a few things:

This mainly addresses tempdb contention experienced by workloads with extremely frequent TVP use (thousands per second).

In SQL Server 2014, memory-optimized table variables precludes the use of parallel queries.

As the feature name suggests, the temp tables live in memory.

Luckily the high frequency described by the first caveat means that the queries I use must be as lightweight as possible. And that means that I don’t mind the second or third caveat so much. Aaron Bertrand dives deeper on the performance of Memory-Optimized table variables at Hekaton with a twist: In-memory TVPs – Part 3.

In the past, my colleagues and I have been incredibly frustrated at this bottleneck. A bottleneck that could not be fixed, not even with dollars. And so I’m looking forward to this feature more than any other single feature introduced in 2014 or 2016. We vendors are often constrained to use only those features present in every SQL Server version we support. In my case, it will be a while before we de-support SQL Server 2012 but that day will be like Christmas.

—

* I’ve written before about TVPs and the different kinds of contention caused by frequent use of TVPs:

I just met a friend at a SQL Saturday who let me know that he recognizes my name because it was attached to a project I wrote five years ago. The “Blocked Process Report Viewer”. I was impressed. I’m glad to know that it’s still used. So I decided to update it for 2016.

I’ve extended the Blocked Process Report Viewer to look at blocked process reports that were collected with extended events session.

Where To Find It

How To Use It

The viewer can consume blocked process report events captured by any extended events session as long as that session has a target of ring_buffer or event_file. For example, if you set up your extended events session the way Jeremiah Peschka did in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. Then you would use the viewer like this:

exec sp_blocked_process_report_viewer
@Source ='blocked_process', -- the name that Jeremiah gave to his xe session
@Type ='XESESSION';

which gives you something like

The value of the blocked process report viewer is that it organizes all the blocked processes into “episodes”. Each episode has a lead blocker which is the process in the front of the traffic jam. Look at that process closely.

Feedback

Let me know how it goes. Tell me if there are any errors or performance issues. I’m especially interested in the performance of the viewer when given extended events sessions that use ring_buffer targets.

Takeaway: When assessing concurrency, performance testing is the primary way to ensure that a system can handle enough load concurrently. Monitoring blocked process reports does not give enough warning to anticipate blocking issues.

Logical Contention

Capacity planning is difficult for DBAs who expect growth. Will there be enough CPU, Memory or I/O to serve the anticipated load? One category falls outside those three, logical contention.

Logical contention is a problem where excessive blocking causes throughput to suffer. It would be great to get advanced warning. One essential strategy is to make use of the blocked process report. The problem is that blocked process reports are an alarm metric, not a guage metric. In other words, the blocked process report can indicate when there is a problem, but it is poor at giving advanced notice.

A Simple Concurrency Simulator

It simulates how well a server can handle tasks that use pessimistic locking (tasks that cannot be run in parallel because they block eachother). It’s amazing to see how nice things seem at 6000 tasks per hour and how quickly things can go wrong by 7000 tasks per hour. Unfortunately this scenario is too familiar. Episodes of excessive blocking tend to be either nonexistent or very painful with no in-between.

Graphically that looks like this:

You’ll notice that even at a five second threshold, the blocked process report gives us little warning. Blocked process reports don’t warn of approaching trouble, they indicate that trouble is here.

How Useful Are Blocked Process Reports Then?

Continue monitoring blocked processes. Knowing that you’ve got concurrency issues is preferable to not knowing. Just keep in mind that it can’t replace performance testing.

February 3, 2016

As many people will point out, you can’t depend on the order of results without using the ORDER BY clause. So it’s easy to say “Simple! Don’t write code that expects unsorted data to be sorted”. But it’s very difficult to be careful everywhere all the time.

Remember that this is an application problem and is not a SQL problem. We only get into trouble when applications (or people) expect results to be sorted when they’re not. So unless you have a tiny application, or a huge amount of discipline, it’s likely that there is some part of your application that assumes sorted results when it shouldn’t.

Here’s a method I used that attempts to identify such areas, exposing those assumptions. It involves reversing indexes.

If you don’t ask for results to be ordered they may still appear to be ordered. SQL Server will return results in a way that’s convenient and this is often in some index order. So if the indexes are reversed, then the idea is that what’s convenient for SQL Server will be reversed.

Open up the create_indexes.sql script in an editor and swap ASC for DESC and vice versa

Create the reversed indexes

Recreate the foreign keys

That’s it! Now unsorted results will be returned in a format convenient to SQL Server which should be opposite to the original order.

Test

Remember how these ORDER BY assumptions are human or application problems? It’s time to bring them into this process. Test your applications/reports manually, or if you’re fortunate enough to have them, run your automated tests.

I’m one of the fortunate ones. I have access to a suite of automated tests that includes thousands of integration tests. In my case, roughly one percent of them failed after this experiment. Colleagues reactions were varied. They ranged from “That many?” to “That few?”

This experiment cannot identify all ordering assumptions, but it has a good chance at identifying many.

Fix

First let me give some advice on how not to fix this. Don’t begin by indiscriminately sprinkling ORDER BY clauses throughout all your queries. I found the best approach is to handle each problem on a case-by-case basis.

Here are some approaches:

Fix the test For automated tests, sometimes the test itself assumed an order. This is an easy case to deal with.

January 24, 2016

I want to use extended events to store recent deadlock graphs. And I want to create an asynchronous file target instead of using the existing system_health session. I don’t like the system_health target for a couple reasons. It’s too slow to query and it rolls over too quickly and it disappears after server restarts.

So I searched the web for a solution and when I couldn’t find one, I wrote my own solution, I tested it and I decided to blog about it.

Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.

So go read Jeremiah’s article, it’s really well written. What follows is my solution. The only difference is that mine only focuses on deadlocks. Jeremiah’s focuses on both deadlocks and blocked processes.

Create The Session

Here’s the session that I use. It

has five rollover files so that a couple server restarts don’t lose any recent deadlock graphs

uses an asynchronous_file_target which I prefer over the ring buffer,

and it cleans itself up over time. I don’t need a maintenance job to remove ancient data

XACT_ABORT

Microsoft’s docs for XACT_ABORT are pretty clear. The setting determines whether “SQL Server automatically rolls back the current transaction when a statement raises an error”.

And in nearly every scenario I can think of that uses a transaction, this automatic rollback is the desired behavior. The problem is that it’s not the default behavior. And this leads to Dan Guzman’s advice where he strongly recommends that SET XACT_ABORT ON be included “in all stored procedures with explicit transactions unless you have a specific reason to do otherwise.”

What Could Go Wrong?

When a statement inside a transaction fails (for whatever reason) and XACT_ABORT is set to off, then…

That transaction is abandoned.

Any locks taken during that transaction are still held.

Even if you close the connection from the application, .NET’s connection pooling will keep that connection alive and the transaction on SQL Server stays open.

Fortunately, if someone reuses the same database connection from the connection pool, the old transaction will be rolled back.

Unfortunately developers can’t count on that happening immediately.

Abandoned transactions can cause excessive blocking leading to a concurrency traffic jam.

Also, abandoned transactions can interfere with downstream solutions. Specifically ones that depend on the transaction log. Transaction logs can grow indefinitely. Replication solutions can suffer. If RCSI is enabled, the version store can get out of hand.

Some (or all) of those things happened to us last week.

Steps To Take

Here are some things you can do:

Do you have abandoned transactions right now?
It’s not too hard to identify these abandoned transactions:

-- do you have abandoned transactions?select p.spid, s.textas last_sql
from sys.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where p.status='sleeping'
and p.open_tran>0

Also if you use sp_whoisactive, you can identify these processes as those with a sleeping status and at least one open transaction. But there’s a trick I use to identify these quickly. The sql_text value in the output of sp_whoisactive will typically begin with CREATE PROCEDURE. When I see that, I know it’s time to check whether this connection is sleeping or not.

SET XACT_ABORT ON
Follow Dan Guzman’s advice to include SET XACT_ABORT ON in all stored procedures with explicit transactions.
You can actually find the procedures in your database that need a closer look

-- find procedures that could suffer from abandoned transactionsSELECT*FROM sys.procedureswhere OBJECT_DEFINITION(object_id) like '%BEGIN TRAN%'
and OBJECT_DEFINITION(object_id) not like '%XACT_ABORT%'orderby name

Set XACT_ABORT ON server-wide
If you choose, you can decide to set the default value for all connections to your server. You can do that using Management Studio:

Code Review

I love code reviews. They’re more than just a tool for improving quality. They’re learning opportunities and teaching opportunities for all involved.

Last week, I invited readers to have a look at a procedure in a post called Code Review This Procedure. I was looking for anyone to suggest turning on XACT_ABORT as a best practice. It’s a best practice where I work, but things like this slip through. We should have caught this not just during testing, but during development. It’s obvious with hindsight. But I wanted to determine how obvious it was without that hindsight. I guess it was pretty subtle, the XACT_ABORT was not mentioned once. That’s either because the setting is not often used by most developers, or because it is easily overlooked.

But here are some other thoughts that readers had:

Concurrency
Many people pointed at concurrency and transaction isolation levels as a problem. It turns out that concurrency is very hard to do right and nearly impossible to verify by inspection. In fact one of my favorite blog posts is about getting concurrency right. It’s called Mythbusting: Concurrent Update/Insert Solutions. The lesson here is just try it.

Cody Konior (blog) submitted my favorite comment. Cody writes “I often can’t disentangle what the actual impact of various isolation levels would be so I go a different route; which is to create a quick and dirty load test”. I can’t determine concurrency solely by inspection either, which is why I never try. Cody determined that after hammering this procedure, it never failed.

People also suggested that blocking would become excessive. It turns out that throughput does not suffer either. My testing framework still managed to process 25,000 batches per second on my desktop without error.

Validating inputs
Some people pointed out that if NULL values or other incorrect values were passed in, then a foreign key violation could be thrown. And they suggested that the procedure should validate the inputs. But what then? If there’s a problem, then there are two choices. Choice one, raise no error and exit quietly which is not ideal. Or choice 2, raise a new error which is not a significant improvement over the existing implementation.

Avoiding the transaction altogether
It is possible to rewrite this procedure without using an explicit transaction. Without the explicit transaction, there’s no chance of abandoning it. And no chance of encountering the trouble that goes with abandoned transactions. But it’s still necessary to worry about concurrency. Solutions that use single statements like MERGE or INSERT...WHERE NOT EXISTS still need SERIALIZABLE and UPDLOCK.

Since your EVENT_TICKETS table has required foreign keys (evidenced by NOT NULL on all columns with foreign key references) the proc should be validating that the input parameter values exist in the foreign key tables before trying to insert into EVENT_TICKETS. If it doesn’t find any one of them it should throw an error and gracefully rollback the transaction and return from the proc.

The way it’s designed currently I think you could get an error on inserting to EVENT_TICKETS that would fail the proc and leave the transaction open.

At work, we store error messages from our web servers in a database. Up until recently, we were loading them using procedures that use wide TVPs. We discovered that if we saw a large enough spike in errors, then we’d also see tempdb contention which would flood our error logging service with more errors. This positive feedback loop made our database server unresponsive and the system as a whole couldn’t recover without intervention. That’s the problem I tackled, and I want to explain my troubleshooting process.

The Troubleshooting Landscape

Let’s start at the beginning. Here’s an abstract visualization of how I see throughput and performance thresholds.

The blue area represents the load sent to the database. The red bars are examples of performance thresholds that can limit throughput. Based on hardware and configuration, those red lines can be moved up or down. The lowest red line is the performance ceiling. In this example, notice that adding more CPUs would not allow more throughput unless tempdb contention is also tackled:

The Troubleshooting Landscape

I like to show this graph to people in order to illustrate the implicit relationship between a database developer and a database administrator. There’s obviously a lot of overlap, but in general:

The red lines are part of the DBA’s job. It is up to the DBA to provide and configure a database server that can support the load sent by the application.

The blue area is part of the developer’s job. It is up to the developer to make most efficient use of the hardware given.

Happy databases are ones where the blue and the red don’t meet.

SQL Server DBAs are the only ones that have to worry about tempdb (Oracle and Postgres DBAs get a break).
But look at that tempdb contention limit. I like to point out to anyone who will listen that tempdb contention is Microsoft’s fault. Every minute spent on tempdb problems is time spent working around a defect in SQL Server. It’s frustrating. It’s already hard enough worrying about CPU and IO without worrying about logical contention caused by the database engine. I feel like this guy:

My Troubleshooting Workflow

So if you’ve been following my blog for the past few weeks, this is what I’ve been leading up to. With a ton of hindsight, here’s my workflow for troubleshooting tempdb contention:

Some of the early information in the early steps can be detected using sp_whoisactive, and some of the last steps are links to other parts of this blog series.

But…

The world is rarely as nice and predictable as we model it to be. Database load is no exception. Database load is not a smooth thing. It’s spikey and uneven and it consists of an unpredictable variety of queries.

Once when I thought that tempdb transactions per second was the best metric to watch, I captured this graph over a couple of days:

The outage seems unrelated to tempdb activity

The spikes in tempdb transactions correspond to nightly report runs or maintenance. But we experienced a database outage caused by tempdb contention during a period of time where tempdb usage should have been tolerable. This was an outage where practically no queries completed.

And that was puzzling to me. I knew it was had to be one of two things. Either

the metric I was using – tempdb transactions per second – did not accurately reflect the activity that I wanted to measure. Or

the database activity (both volume and variety) changed so suddenly, that I couldn’t measure it. It not only caused an outage, but it also prevented me from measuring the effect

It turns out that both ideas were correct. I embarked on a process to learn more about tempdb and that’s when I found out that wide TVPs were multipliers of tempdb usage. Taking that into account, I discovered that our error logging procedure was very very expensive. And it wasn’t too long to find that any spike in errors could quickly cause a feedback loop. So quickly that our servers couldn’t cope and couldn’t recover.

What We Did

We did a couple things. We isolated our logging database onto a separate server. Our logging service was adjusted to not use TVPs as frequently. This helped. Our tempdb slowdowns were no longer server killers, they were merely throttles. Another adjustment to the number of data files reduced our tempdb headaches to almost zero and this meant that we could finally focus on the next bottleneck in our troubleshooting landscape.

In the past few months, I’ve tackled this bottleneck and about a dozen others. In fact, if I had time enough, you’d probably see more posts like this one about troubleshooting and tackling performance challenges. After a lot of work by many people, we were able to support as much as 58K transactions per second and we’re really proud of that. It’s been a difficult but fun couple of months.

Then each time the procedure is executed, SQL Server will pre-allocate six extents (48 pages) of space in tempdb whether or not it’s needed.

Measuring tempdb Activity

So what’s the problem? Well, if these procedures are called frequently (like hundreds or thousands of times per second), then the extra allocations for wide TVPs can be excessive. These allocations can cause tempdb latch contention.

I found that SQL Server will allocate tempdb space based on the width of a TVP table. If a TVP uses a table type that has a NVARCHAR(10) column, then SQL Server doesn’t pre-allocate any space at all. But if the TVP table type has a NVARCHAR(4000) column, then SQL Server will allocate six extents of space. To measure the tempdb activity exactly, I wrote a program which let me generate this:

This chart takes some explaining. But first notice that a single query can cause up to 56 tempdb PFS operations!

Here’s how my program helped me create this chart. It starts by creating a table type. The table type has one column of type NVARCHAR(X). Next, the program executes a simple query that uses a table-valued parameter of that table type. The program measures the number of tempdb allocations and deallocations for various TVP widths: (X ranges from 1 to 4000). Also notice that

The x axis corresponds to the maximum width of the column in the table type

The y axis corresponds to the number of PFS operations found in tempdb. An extent is allocated with one PFS operation, but deallocated one page at a time, this results in nine PFS operations per extent.

The steps correspond to four, five and six extents.

There is some overhead associated with creating and dropping tempdb objects. But that overhead is avoided with temp table caching. And those operations have been removed from this chart.

Table types that can be wider than a 8000 bytes such as those with multiple columns or with NVARCHAR(MAX) columns are treated the same as table types with an NVARCHAR(4000) column; they’re given six extents of space.

What I Think

I’m still making an assumption that transaction log operations on PFS and GAM pages share a one-to-one relationship with latches on those pages. But experimentally, I have seen that skinny TVPs do in fact enjoy much higher throughput than wider TVPs. In general, this is really hard to measure. I’m frustrated that there are no good metrics to help measure this kind of thing.

Six extents for wide TVPs is really excessive and causes unnecessary pressure on tempdb latches when used frequently. I now begin to worry about the width of table types and the frequency that they’re used as table-valued parameters. But I don’t think we should have to. Stuff like this should be the concern of the database engine, not the database administrator.

I discovered that digging into tempdb’s transaction log using the undocumented fn_dblog is a surprisingly useful technique for finding data to help battle tempdb contention. But it’s not easy. The transaction log is cryptic and sorting through the information can be difficult.

Like many many others, I’ve found it difficult to avoid or diagnose tempdb allocation contention issues. Typical advice includes

Use multiple equally-sized data files for tempdb

Use trace flag 1118 in order to allocated full extents instead of mixed extents

Look at your applications to reduce queries that use tempdb

It’s that last point that is so tricky. I need to find out what parts of my application are using tempdb but it’s used for so many different things. Robert Davis lists eighteen different ways that SQL Server uses tempdb in his whitepaper Demystifying tempdb Performance and Manageability. So in order to tackle tempdb allocation contention, there are two things I want to do. I want to (A) measure tempdb allocations and (B) dig into tempdb allocations.

The Trouble (a Quick Recap)

Remember, tempdb is a resource shared by everyone using the database server. When a process wants to use space in tempdb, it allocates pages. Part of that allocation involves calling dibs for those pages by updating the Page Free Space (PFS) page and the Global Allocation Map (GAM) page. But updating those pages involves taking a short-lived latch on them. So with enough activity, we can see latch contention on these pages. Other people have done a better job explaining that than I did just now. You can probably find many of those explanations by googling tempdb latch contention.

Measuring Tempdb Allocations

So if latches on tempdb pages are a limited resource, then it would be wonderful to measure how often latches are taken there. That way developers could experiment to determine what activities use tempdb and to what extent. Right now, that can’t be measured. Maybe we’re spoiled. We have a wide variety of extended events and a rich set of dynamic management views that let us dig really deep into SQL Server internals. But when it comes to measuring tempdb latches, there’s no suitable metric.

This is a gauge metric and it would be useful if every tempdb transaction allocated a small number of pages. But at least one kind of activity – tempdb spills – will allocate many pages in a single tempdb transaction and that activity gets hidden.

These are useful when you want to categorize tempdb allocations. It’s reports the number of tempdb allocations that have been taken for any given point in time. But tempdb allocations taken by long-running transactions can be over-reported and short-running transactions tend to be missed. This makes it difficult to tell whether the data indicates typical tempdb allocations or whether it’s just showing long held allocations.

Oooh perfect. Too bad this performance counter is fictional. It doesn’t exist. I would love this metric if it existed because it would be a gauge metric that most directly corresponds to tempdb contention problems.

Digging into Tempdb Allocations

Putting metrics aside for the moment, I’ve also tried a number of ways to dig into events and logs in order to tie tempdb use with user activity. I’ve had limited success with these two methods:

There are new extended events called sqlserver.mixed_extent_allocation and sqlserver.mixed_extent_deallocation introduced in SQL Server 2014, but these are focused on mixed extents (which I already avoid by enabling trace flag TF-1118).

But it turns out that tempdb’s transaction log is rich with information (even though a lot of it is cryptic).

Digging into Tempdb’s Transaction Log

The easiest way to look at tempdb’s transaction log is by using fn_dblog:

use tempdb;
select*from fn_dblog(null, null);

But it can be a heavy query. Here’s a light-weight query that gets the most recent 5 seconds of activity or 10000 rows whichever is less:

Transaction names are cryptic, but they are organized by activity and we can use some educated guesses to figure out what’s going on. For example, queries that use table valued parameters are called TVQuery and are often associated with transactions called AllocFirstPage, FCheckAndCleanupCachedTempTable and if the TVP’s table type has a primary key defined, then offline index build.

PFS or GAM activity by tempdb transaction type
Putting it all together:

How Is This Actionable?

Remus Rusanu has a post titled How to read and interpret the SQL Server log. It gives an introduction to some of the internals involved with what you see in the transaction log. It’s not always obvious why understanding SQL Server internals helps in real world scenarios. I’ve found that it’s most useful for troubleshooting by focusing avenues of investigation (whether it’s TVPs, tempdb spills, version store stuff, etc…).

In my case, I used this data to narrow down the type of activity. Specifically I used

the Transaction SID column (giving me the user),

the SPID which gives me the session id and helps me tie it to data collected elsewhere