Michael J. Swart

February 28, 2013

Follow up on Ad Hoc TVP contention

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: — Michael J. Swart @ 12:00 pm

Last month I posted PAGELATCH Contention on 2:1:103. It described a troubleshooting experience I had. I was troubleshooting a performance problem that was felt when applications issued very frequent ad hoc queries that used Table Valued Parameters (TVPs).

I’ve recently learned some extra things from the SQL Server team at Microsoft that I wanted to pass on. So this post acts as a kind of update to that one.

I know. I've drawn Columbo before, but it's my blog.


One thing I did was to come up with an application that reproduces the contention on 2:1:103 in the same way as production. I share that here now.

What this reproduction does is launch fifty processes. Each process uses multiple threads to hit a db server with an ad hoc query that uses a TVP.  For the curious here it is: Download AdHocTVPContention.zip and then open readme.txt. This reproduction is a mashup of

  • A simple batch script which launches an executable 50 times at once. A technique I gleaned from Paul Randal in a demo of his.
  • A multi-threaded executable which sends queries asynchronously. A framework I first developed in a post I wrote on concurrency.
  • The ad hoc TVP query itself. It was a bit of a modification of my example at Table Valued Parameters, A Short Complete Example. The only difference is that the command is a query instead of a stored procedure.

What I’ve Learned Since Then

The Microsoft team helped me understand a few things. The main thing is that the particular version of SQL Server (including service pack level) matters a lot:

SQL Server 2005 and Earlier

TVPs weren’t supported then. (This post isn’t for you).

SQL Server 2008 and R2

In my earlier post, I dismissed a fix provided by Microsoft. Fix: Poor performance in SQL Server 2008 R2 when table-valued functions use many table variables. At the time, I dismissed it for a number of reasons:

  • The workaround listed on that kb article page did not help us.
  • The symptoms that were described at that issue did not match my own scenario (i.e. I wasn’t using table-valued functions).
  • The title mentioned R2 and I thought (mistakenly) that this fix was not applicable to SQL Server 2008. (Update April 5, 2013: The fix used to be available as a hotfix for 2008 SP3, but as of March 2013, this fix is now included in a cumulative update. Upgrade to SP3 CU10 if you’re using 2008 and want this fix. Microsoft has also updated the title of the fix to mention 2008)

Microsoft helped me learn that although the symptoms in that fix differ from mine, the improvement in that fix involves a change to code that also improves my scenario. So with the fix applied, on my test environment, I saw that SQL Server could handle at least 6 times the volume of ad hoc queries than it could before (your mileage may vary).

SQL Server 2012

But there’s one other thing I noticed. When I ran the reproduction on a SQL Server 2012 environment, the process just flew through the workload! Temp table creation was no longer a bottleneck; there was no more contention on temp table creation. The Microsoft Engineer I worked with noticed this too and eventually it prompted a CSS blog entry. You can read all about this 2012 performance improvement at Temp table caching improvement for table valued parameters in SQL Server 2012

Partner vs MVP

I’m very lucky to have many investigation options available to me when I’m digging into a particular quirk of SQL Server behaviour. There’s the public options like #sqlhelp or dba.stackexchange or Microsoft support. And for me there’s some private options like the MVP mailing list. But this month, I feel lucky to work as a Microsoft partner. It’s through that connection that I learned so much.

Too Long; Didn’t Read

If you’re experiencing 2:1:103 contention because of too frequent ad hoc TVP queries then:

February 8, 2013

Some Failed Blog Topics

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 5:08 pm

I list some blog topics that I don’t plan to complete.

I have a rule of thumb that I try to follow when writing. If I can write something in a simpler way I do. If I can cut out words, sentences or whole paragraphs without altering the meaning, I do. Sometimes the thing I’m left with is so short, simple and straightforward that I wouldn’t even bother tweeting it.

For example, a couple weeks ago, after a shortening revision, I wound up with “Index your tables properly while designing databases”. That’s not the title, that’s the whole thing. Something so short and simple doesn’t deserve a blog post and so neither did the long version of that post!

So I’m cleaning house. I want to post a bunch of ideas that were never good enough on their own to make the cut.

Knowledge Philanthropy

Those two words sound great together don’t they? I’ve always felt like more of a knowledge consumer. In fact this blog you’re reading is an effort on my part to “give back”. But no matter what, I’ve always felt like I’ve received way more than I’ve given.

Your Database is Not Perfect

But it doesn’t need to be. It needs to be stable, scalable, and easy to maintain. It needs to meet any requirements you care to define. Anything after that is going to be a harder sell.

Measuring the Effects of Fragmentation

An interesting experiment I started, but it led me past DBA-land into SAN Admin land and I was not comfortable saying anything with authority here.

Q & A for questions I made up:

I must have written the following while half asleep and I have absolutely no idea what it could mean:

ala Esquire: Fake… subtly bad advice “Jesus Bless You” Generic advice that gets suddenly personal: Why do delis always give pickles with sandwiches?” “Ordering Pheasant”

Quotestring — Safe way to use Data as Code?

No, I wouldn’t count on it.

Hey bud!

The following was inspired by comments at “Last Word On Scans” It was drawn for drawing’s sake.

Wanna buy a query plan operator?

Range Scans on Multicolumn Keys

Say you have a table with an index on (LastName, FirstName) and you want to select all the rows which sort between “Lucas, George” and “Spielberg, Stephen”. I’ve never been able to find straightforward syntax that results in a query plan with a simple range scan.

I’m a Paul White Fan

Not very many of my friends and family hold up their side of the conversation when I talk about databases. But at the PASS Summit, there are hundreds of people who will! And I think that’s why I love it. I always intended to write about my experience at 2012’s summit, but I couldn’t think of anything meaningful to write. I knew that Paul White’s session was a highlight for me, but I didn’t have much else to say.


On Being Competent

Billy Joel described himself once like this:

“… I consider myself to be an inept pianist, a bad singer, and a merely competent songwriter. What I do, in my opinion, is by no means extraordinary. I am, as I’ve said, merely competent. But in an age of incompetence, that makes me extraordinary.”

That particular quote probably struck me at a time when I wasn’t feeling particularly perfect enough at what I do.


Is here! Oh actually, april fools. It’s actually called Point in time restore, and it’s been available for a while. And the syntax is a little different…

Powered by WordPress