Michael J. Swart

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.

Reproduction

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:

• http://support.microsoft.com/kb/2566163 will help you out of a jam and
• SQL Server 2012 will squash that issue for good.

What’s up with triggers on views? What kind of patchwork monster is this?

I’m sharing something that I learned this week. It’s something I learned while talking to a friend at work. He wondered whether SQL Server had any feature similar to the rule system over at Postgres. (I thought no at first, but keep reading).  You know, I continue to learn tons while talking to friends at work even though I’m the in-house SQL Server specialist. I don’t mind at all.

Aside: Have I ever mentioned how amazing the people I work with are? I work for a great company and we’re hiring like crazy.

Back to the post. Here’s what I learned:

• I knew that insert/update/delete statements could be executed on views if the view was simple enough. If it was simple enough we call the view “updatable”.
• I thought that triggers couldn’t be defined on views but
• I was wrong and …
• I learned that not only are triggers allowed on views but INSTEAD-OF triggers will work on any kind of view, updatable or not.

Example

Here’s what I mean.

create table A (id int primary key)
create table B (id int primary key)
go
 
create view AB as
    select id, 'a' as tablename from A
    union all
    select id, 'b' as tablename from B
go

If I were to try to insert rows into the view, I get this error message:

'UNION ALL view ~ is not updatable because a partitioning column was not found.'

Other views give other errors, but the message is the same: “Your view is too complicated to be updatable”. That’s okay. I never really expected inserts to work on anything but the simplest views so I’m not too disappointed.

But like I said, here’s how you would make this view updatable – or rather insertable – with an INSTEAD OF trigger:

create trigger t_AB
on AB
instead of insert
as
    if exists (select 1 from inserted where isnull(tablename,'') not in ('a','b'))
         raiserror (N'column ''tablename'' must be either ''a'' or ''b''' 16, 1);
    else
    begin
        insert A (id) select id from inserted where tablename = 'a'
        insert B (id) select id from inserted where tablename = 'b' 
    end
GO

Boom! No more this-view-is-not-updatable errors.

But When Would I Use This?

I thought of the perfect use case. This strategy helps with  SCHEMA REFACTORING. Say your schema contains this table:

create table ANIMALS 
(
    Name varchar(100) primary key,
    IsVertebrate bit not null,
    VertebraeCount int
)

and you’ve decided to change it to look like this:

create table ANIMALS_BASE
(
    Name varchar(100) primary key
)
 
create table VERTEBRATES
(
    Name varchar(100) primary key
        references ANIMALS_BASE(Name),
    VertebraeCount int not null
)

After you’ve done that, you can create a view called ANIMALS with triggers to accommodate applications that still expect the old schema. That would look something like:

CREATE VIEW ANIMALS
AS
    SELECT AB.Name, 
        CASE WHEN V.Name IS NULL THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END as IsVertebrate,
        V.VertebraeCount
    FROM ANIMALS_BASE AB
    LEFT JOIN VERTEBRATES V
        ON AB.Name = V.Name

Next create the insert, delete and update triggers. I only show the insert trigger here. The delete and update shouldn’t be too hard to write either.

create trigger t_ANIMALS_i
on ANIMALS
instead of insert
as
    INSERT ANIMALS_BASE (Name) SELECT Name FROM inserted;
    INSERT VERTEBRATES (Name, VertebraeCount)
        SELECT Name, VertebraeCount 
        FROM inserted 
        WHERE IsVertebrate = 1;

Here’s a few ways that this helps:

• You might want to use this strategy when you don’t have control over every application using your database.
• This helps with migrations because applications are now free to expect the new or old schema. The applications can transition gradually to the new way of doing things.

Even if you believe every application uses only the new schema. You can still make sure gradually by logging whenever someone uses the old schema. Just add some logging statement to the definition of the triggers. You can log or track trigger use using any way you’re comfortable with. One suggestion is to use the user-settable performance counter via sp_increment_counter1 then Poof! You’ve got your own deprecated-features performance counter just like Microsoft.

Consider the Following

Did you know you can do science anywhere. Science is about learning how Nature behaves. Well what if we replace Mother Nature with SQL Server? SQL Server’s not as pretty, but we can still learn things. It’s 2012! When we wonder about things – even databases – we don’t have to guess.

The Question

To keep things simple, I’m going to follow the scientific method here and this is step one. The question. All good science starts with a question. The question itself doesn’t matter. I just want to talk about the process.

So I take the question from this DBA Stackexchange question: Why are NULLS sorted first?

Good question! The SQL Standard does not actually prescribe any behavior here (says Wikipedia). So the actual behavior is left up to the RDBMS vendor. The likely answer to the question is that NULLS are sorted that way because Microsoft had to pick something and they probably just made a choice based on what other RDBMS’s were doing or other implementation considerations. Or who knows, maybe they just flipped a coin.

But that leads me to this question: Are they always sorted first? If I reverse the order of a sort by using ORDER BY column DESC do Null values still show up first? If I look at the manual, Microsoft tells me “Null values are treated as the lowest possible values” so that ORDER BY column DESC will place Null values at the end.

But the way the stack overflow question is worded makes me wonder a little and I’d like to find out for sure. So to repeat:

“If I sort the results of a query using ORDER BY column DESC do Null values come last?”

Hypothesis / Prediction

I’m placing my bets with Microsoft’s documentation and so I answer YES.

I predict that any query written with “ORDER BY column DESC” will place the Null values at the end. If I’m wrong, then it would seem like null values always first is the behavior here (Note: NULLS FIRST is actually mentioned in the SQL Standards as an extension to the ORDER BY clause, but it’s not implemented by SQL Server. This also lets me believe I’m not crazy for wondering.)

Test

That’s simple enough:

select *
from (values (1),(2),(3),(null)) as v(value)
order by value desc

The prediction says that the fourth value in the results will be NULL. And tada! The prediction is correct.

Analysis

There’s nothing surprising here, but maybe it raises some new questions. Like:

• What do other vendors do? (Turns out they do the same. This lends weight to Microsoft picking this behavior because it was common practice)
• What’s up with this NULLS FIRST keyword?

There’s a publish part here. The default assumption (that Microsoft documentation is often correct) is still valid so there’s nothing surprising to publish. Probably not worth a blog post (despite what you’re reading).

Boiled Down

Normally the thought processes aren’t so structured, but as long as the question comes first and the guess is checked against experiment, it counts.

It seems like common sense. And maybe I could have picked a better example. Science helps satisfy our curiosity. But it also can save us from kidding ourselves. Without this kind of thinking we risk turning into superstitious pigeons. I once saw someone perform a task (release and renew his ip address) three times out of habit “just so it takes”. That way lies madness.

You know I’ve always thought of SQL Agent as one of the “Agent Programs” from the movie the Matrix:

So what follows is a mini-lesson about the SQL Agent. I recently got into trouble when I created a SQL Agent Job step. The only thing this step needed to do was to move some text files from one network drive to another.

I just wanted to use the dos command:

move "\\server1\somedir\*.txt" "\\server2\somedir"

But if there were no files copied, the step failed because the return code was 1 instead of 0. It succeeded in copying all the files that were there (all zero of them), but apparently that’s unacceptable.

I then tried using robocopy. Robocopy is the “robust copy” command for Windows. It’s got all the bells and whistles… so I tried:

robocopy "\\server1\somedir\" "\\server2\somedir\" *.txt /mov

but the return code for robocopy is very complicated. Even more so than regular move. I’m not the first to deal with this headache… Check out this topic on SQL Server Central’s forum. The solution there uses a batch script to accomplish the task. If it’s successful, it returns a consistent error code. But doesn’t it seem like there should be a better way?

Only One Successful Return Code For Cmd Steps

Yep, the problem stems from the fact that only one return code is allowed for SQL Agent Job steps that are Operating System commands. That’s shown on this screen:

Powershell Beats ‘em All

Long story short… I went with a powershell script. It was the simplest:

move-item "\\server1\somedir\*.txt" "\\server2\somedir"

And I don’t worry about return codes here. The powershell script succeeds or it doesn’t. Times when the script fails include when the move would overwrite an existing file or when permissions prevent the file move and that’s what I wanted.

In powershell, simple things are simple. Hard things are possible.