Blog

What a gem!

Minimal logging is super cool. If you follow all the rules, there’s a pretty good chance it will work! Unfortunately, it’s not always up to you. All sorts of ISVs put out all sorts of bad ideas in form of code. Sort of like in Ghostbusters, when Gozer tells the crew to choose the form of the destructor, and Ray thinks of the Stay-Puft Marshmallow Man. Except in your case, the form of your destructor is a flat file bulk loading process that you can’t change any part of.

So here’s the test setup. Usual caveats about doing this in development, alright? Deal? Deal!

Create a table and dump a million rows of crap into it. A million rows is a good starting place for table sizes. I’m leaving it as a HEAP here, because this is one of the few occasions I’m cool with people having HEAPs. Nothing is faster for dumping data into.

Good ol’ HEAPs. Everything is faster when you don’t care.

Since we need to test a bulk process, we’re using BCP. If you’re cool with xp_cmdshell, you can run this. If not, you’re gonna have to crack open a command prompt and run the bcp commands on their own. You’ll probably have to change the file destinations and server names, unless you hacked into my laptop.

This gives us a comma delimited file of all our table data, and a format file to feed to the bulk insert process. You don’t need the format file for this to work, I just prefer to use them when possible. You can do all sorts of cool stuff with format files!

If we had full control, we could very easily load data in through BULK INSERT like this:

That takes about 16 seconds on my laptop, dumping in a million row batch from the file we output. If you’re working with more rows, you may want to break the batch size down into smaller chunks.

Do anything for TABLOCK

The magic happens with the TABLOCK OPTION. Without TABLOCK specified, this runs for around 40 seconds. That’s a bit more than double the time involved when we use TABLOCK, and minimal logging occurs.

But of course, we’re working with a file loading GUI, or something that just doesn’t let us make those kind of changes. So what do we do?

There’s a silly old system stored procedure out there that lets us change certain default options for tables. It is called, shockingly, sys.sp_tableoption. How does it help us here? One of the options is “table lock on bulk load”. That would have been more dramatic if I didn’t give you the link first, huh?

This buys us just about the same real estate as if we used TABLOCK in the BULK INSERT statement. Here are the run times for each!

No TABLOCK:

TABLOCK:

No TABLOCK, table option set:

You don’t have to be an internals wizard

When minimal logging works, the difference in timing and I/O is quite apparent. There are undocumented functions you can use to further prove your point, just don’t run them unless you’re on SQL Server 2014, or 2012 SP2 or higher. On all the other versions, there’s a bug where the function creates threads that won’t go away until you restart. So, yeah. Don’t do that. Unless you’re into thread starvation.

If you’re a customer, this is a good way to prove to your vendor that minimal logging is cool thing to get working. Lots of applications rely on flat file loading. I’m a big proponent of using staging databases that sit in simple recovery model for stuff like this, so you’re not whomping your client-facing database, and you don’t have to switch it from full to bulk logged to use minimal logging.

Thanks for reading!

SQL Server 2016 is adding some really awesome stuff

Not the least of which is a ton of super cool information in execution plans, and an Extended Events session to expose the same information for all queries. The details are detailed in detail over on this blog post from MS.

For those of you with clicking allergies, a whole mess o’ run-time information is being recorded in execution plans, and by a new Extended Events session called query_thread_profile. Microsoft may be changing, but they still pick horrible names for things. Query Thread Profile sounds like one of those stores the size of a closet that sells four t-shirts that all cost $1000.

Let’s play with Extended Events

You can’t see any of this stuff in the graphical execution plan yet, only the plan XML. It’s only in actual execution plans, and in the XE session. If you read that and paused made a face, yeah, the run-time stats aren’t in cached or estimated plans. With Query Store’s capabilities, it’s probably less necessary to jam this stuff into every DMV and cache, so I’m not all that bummed about it.

Anywho! If you’ve got SQL Server 2016 RC0 or later installed, you can plug this in and run some queries. You will probably need to change the session ID I’m filtering on here.

I’ll be running stuff against StackOverflow, as usual. If you write dumb enough queries, you can really keep a server (laptop) busy for a while. A quick word of warning: this session will store one row per node, per thread, per query. You can really quickly end up with a lot of data in here. You’ll see in a minute what I mean, but let’s run some queries first. You will be sorely disappointed with the data if you don’t run any queries.

Code to parse the session data will be at the end, as usual. Good grief, I have an “as usual” for Extended Events posts. Here’s what we get back.

That thing I said about nodes and threads, here’s a good example of it. The query plan and text columns will all look about the same, because we ran basically the same query two times twice. Node three of the first query used four threads. Remember that in every parallel execution, there’s one coordinator thread that doesn’t do any actual query work, that’s why you have 0-4 for thread IDs, totaling five.

Looking at the plan from our XML column confirms most of this. But again, cached plans offer less information overall. If you get the actual plan here, you’ll be able to see degree of parallelism information.

Checking out the rest of the results, all the promised information is in there: CPU time, total time, rewinds, rebinds, row information, IO information, and so forth. Very powerful stuff. I hope this turns into an easy way to detect imbalances in parallel plans — when one or more threads process way more rows than the rest. That can really hurt you if, say, one thread ends up processing a million rows, and the rest aren’t doing anything. Not very parallel, that.

But one thing I think is really cool, and it’s not just information you can get from this XE session! You can get it from a couple others that I’ll blog about in the future, is what input(s) queries ran with when they’ve been parameterized, or run as stored procedures with inputs passed in. Not just the compiled value, the run-time value! Hooray. Think of all the nights and weekends you can spend fixing things! Just like I pictured the 21st century.

For our simple queries against the user table, here’s what you get for the execution plan. You can see it’s been, simple… simply… parameter… ized? There’s been simple parameterization. There we go. That’s the @1 smallint business going on in there.

And here’s what the XE query brings back for query text.

More trouble coming every day

SQL Server 2016 is looking pretty promising in quite a few areas. I’ll be blogging about all the stuff that catches my eye, so stay tuned.

Thanks for reading!

Begin code!

This year, we gathered in Napa, California for our annual company retreat. Here’s some of our favorite memories:

 

Here’s to another fun year of working together and making a difference in the SQL Server community.

With all the trouble NULLs cause…

You’d think people would be more inclined to avoid them. Slap a NOT NULL constraint and a default value on your column and call it a day. I’m perfectly fine with bizarro world canary values. If it’s an integer column, some really high (low?) negative number. If it’s date-based, why not have it be the lowest value your choice accomodates?

But no, no, no. Instead of stopping the bleeding, developers tend to just avoid getting blood on the new carpet. Some of the worst performing queries I see have ISNULL(something, '') = ISNULL(somethingelse, '') either in the JOIN or WHERE clause. Still. To this day. In the age of the hoverboard, people are still putting functions where they don’t belong. And I know they have the internet.

But did you really mean that?

Most people will slap together their ISNULL manifesto and call it the dog end of a day gone by. But most of them don’t realize how that changes the logic of the query.

For instance, I see ISNULL(intcol, 0) = 0 pretty often. Most people are surprised that rows with 0 in them will also be included here.

But for joins, well, let’s look at what’s really going on.

Teeny tiny tables, appear!

We don’t even need 100 rows for this, but whatever. Let’s join these muddy truckers. Here’s a pretty average inner join, on both ID and OrderDate. It returns 53 rows; all 100 IDs match, but the NULL dates prohibit some joins from occurring.

The results will be something like this. IDs are missing where dates are NULL. For us, that’s every 3rd column in t1, and every 5th column in t2.

Most people will think this will get them out of the woods, but it only fixes six of the NULL joins.

We’ll call them the FizzBuzz six, because…

You guessed it, only numbers divisible by three AND five get joined on our canary value. If we switch over to a left join, we’ll at least see where the gaps are.

Since t1 is the left side, the gaps are every 3rd ID. With t2 on the right side of the join, every 3rd and 5th ID is NULL. To really get the full picture, we’ll need to switch to a full join. This is instructive because it gives us ALL the unmatched rows.

Check out the next three queries, and then why each is important.

For the full join with no WHERE clause, we get back 147 rows. In the next two queries, we filter out NULLs from each of the date columns.

In the second query, 33 rows are missing. That makes sense, because there are 33 rows between 1 and 100 that are evenly divisible by 3.

For the third query, there are 20 rows missing. If you’re not a Mathlete, yes, 20 rows between 1 and 100 are evenly divisible by 5.

This goes back to our unfiltered query: 200 – 20 – 33 = 147.

There are your missing 57 rows.

So what did you really need?

Think carefully about what you’re trying to return with your query. It’s highly unlikely that ISNULL solved your problem, and that you needed all matching results, plus matching results where BOTH are NULL. If you did, this would be a better way to do that:

Killing Join

Remember that joins describe the relationship between your tables, and the where clause describes what you want (or don’t want) to keep from the table relationship.

Think about the first time you stupidly got an apartment with someone you just started dating. You joined your furniture based on the rooms in the apartment, and then filtered out what you weren’t keeping.

If you were me, all your cool guy stuff ended up at the curb. Heckuva where clause, that was.

This post goes out to Paul White, who also wants his t-shirts back. You know who you are!

@dumb_chauffeur Now reminiscing over long-lost favourite t-shirts of my youth. Where did they all go?!

— Paul White NZ (@SQL_Kiwi) February 1, 2016

Thanks for reading!

Before SQL Server 2016, you might have used a function like Jeff Moden’s DelimitedSplit8k function or Adam Machanic’s CLR version. (Aaron Bertrand has a huge post comparing performance of different string splitting methods, too.)

But there’s a new, faster game in town! SQL Server 2016 introduces a brand new STRING_SPLIT function:

Here’s how the results look:

In that example, the ‘ ‘ part at the end is me passing in a space – my list is delimited with spaces. You can do a comma-delimited list, too:

Here’s how it looks:

And is it faster? Heck yeah, it’s faster, as Aaron Bertrand proved.

You can also join to it with CROSS APPLY

Most people who need to split strings don’t just need to split one. They’ll need to do it a whole bunch of times. To call the function over a table column, you need to use CROSS APPLY.

Like most internal functions, you can’t readily get the code. That’s why I’m not going to do a benchmark test against other options here. It might be unfair if MS leveraged some fancy-pants super-secret-squirrel-sauce.

Getting row numbers using ROW_NUMBER

One thing I’ll point out though, is that if you’re used to using DelimitedSplit8K, you might miss the fact that it also returns a row number. This is particularly useful if you’re always interested in the Nth element of a returned string. In order to get that, you need to call it like so.

Since this function is of the inline table valued variety, you’re free to put all sorts of different predicates on the results it returns, which come back in a column called value. You can use ranges and not equal to constructs just as easily. For brevity I’m just throwing out a couple examples for equality and IN.

Where a lot of people will likely find this useful is for passing lists of values into a stored procedure or other piece of code. You can also perform normal JOINs to it.

A couple words of warning here, though. Even though this works, if the string is passed in with spaces, there will be spaces in your results. This doesn’t change the join working for numbers, but it may for text data. The other issue, and the reason the numeric join works fine, is that it returns an NVARCHAR datatype. That means you’ll see implicit conversion warnings in your plan if your joins are performed like in this scenario.

But hey, don’t let that stop you from having a good time. You can use the returned value in CASE statements as well.

If your needs are bit more exotic, and you need to split on CHARACTERS WHOSE NAME SHALL NOT BE PRINTED, you can pass in N/CHAR values as well.

And, of course, you can perform regular Inserts, Updates, and Deletes, with reference to STRING_SPLIT’s value column. Quick example with SELECT…INTO!

Using a prior SQL Server version?

Start with Jeff Moden’s function, but when you create it, name it as STRING_SPLIT. That way, when you move your code to SQL Server 2016 & newer, it’ll automatically work as-is, and it’ll even be faster.