You Shoulda Brought A Bigger Int Sometimes you run a query, and everything goes fine. For a while. For example, if I run this query in the 2010 copy of Stack Overflow, it finishes pretty quickly, and without error. [crayon-6ab5d7ef31b10053908905/] If I run this query in the full version, it runs for a minute and…
For over a decade, SQL Server's handling of table variables has been legendarily bad. I've long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation:
[crayon-6ab5d7ef3bdad907779705/]
It puts a bunch of data into a table variable, and then queries that same table variable. On the small StackOverflow2010 database, it takes almost a full minute, and does almost a million logical reads. Here's the plan:
You're writing a query, and you wanna check to see if rows exist in a table.
I'm using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are:
Presented without comment:
[crayon-6ab5d7ef3ced0524693454/]
Next up, can you break up a query with spaces? Yep:
[crayon-6ab5d7ef3cedb417971924/]
Well if you can do that - can you break up a query across lines? Sure you can:
[crayon-6ab5d7ef3cede426474809/]
And now, brace yourself: this one is so weird that I can't even embed it in the blog. I'm just going to show you a picture of it first:
Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string.
For frequently executed queries, "EXEC sp_executesql @sql"is a good choice but at the risk of encountering parameter sniffing issues.
Hell Here! Get Your Fresh Hell, Here!
Let's face it -- as far as developer tools go, SSMS is pretty bad.
Intellisense? The jokes write themselves.
Method 1, Bad: ORDER BY NEWID() Easy to write, but it performs like hot, hot garbage because it scans the entire clustered index, calculating NEWID() on every row: That took 6 seconds on my machine, going parallel across multiple threads, using tens of seconds of CPU for all that computing and sorting. (And the Users…
Yello!
[crayon-6ab5d7ef3ed4b658974561/]
Thanks for reading!
Brent says: the funny part to me is that if you just try to union all of them, SQL Server throws its hands up:
[crayon-6ab5d7ef3ed55724521384/]
Result:
[crayon-6ab5d7ef3ed59440284569/]
COME ON SQL SERVER YOU JUST TOLD ME THEY WERE ALL NUMERIC, dammit, convert to numeric for me if you're so smart.
Scalar functions are the butt of everybody's jokes: their costs are wrong, their STATS IO results are wrong, they stop parallelism when they're in check constraints, their stats are wrong in 2017 CU3, they stop parallelism in index rebuilds and CHECKDB, I could go on and on.
Recently, we ran across yet another scenario where scalar UDFs were killing performance.
Say you've got an application that insists on inserting data into the database, and...you don't want the data.
You want the application to THINK it inserted the data - you don't want to roll it back or return an error to the end user. You just don't want the data, and you don't want the hassle of deleting it later.
Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key):
[crayon-6ab5d7ef3fbe5774875640/]
The first one (=) gets a clustered index seek, does just 3 logical reads, and correctly estimates that only 1 row will be returned.
The second one (LIKE) does a clustered index scan, reads the entire table, and wildly overestimates that 475,005 rows will be returned - even though only 1 row will.
Virtualization used to be a really Big Deal™ for database admins: we had to do a lot of careful planning to get a virtualization project done right. These days, virtualization is more and more of a no-brainer: most apps make the transition just fine. Every now and then, though, an exception pops up - usually…
For this month's T-SQL Tuesday, Ewald asked who's made a difference in our careers.
When I first got started out in SQL Server, all I had was books and Books Online. Back then, neither of them were particularly well-indexed, nor were they up to date.
360 Questions
We've been asking you folks a lot about what you'd add to SQL Server, and we've gotten some great answers. There are even some that have been answered in the last couple versions of SQL Server.
For instance, 2016 brought us STRING_SPLIT!
I hate that you can do this
Here's the thing: I'm mostly writing this because I didn't know you could do it.
But it's cool, because it'll reinforce some other concepts, and I'll show you why you shouldn't do it.
The short story: if your view has a scalar user-defined function it it, any query that calls the view will go single-threaded, even if the query doesn't reference the scalar function. Now for the long story.
Quite often people will inherit and rely on views written back in the dark ages, before people were aware of the deleterious effects that scalar valued functions can have on performance.
In every training class we do, I learn stuff from students. Here's last week's surprise.
Create a temp table, and put in a value with trailing spaces. Then query for it using the = and <> operators, using different numbers of trailing spaces:
[crayon-6ab5d7ef41a55463622776/]
SQL Server simply ignores the trailing spaces:
While working on a demo
I thought this might make a fun aside to share on the blog, because working with dynamic SQL can be challenging. Especially if you don't have a monitoring tool or application profiling to figure out who ran what and when, most of the time you'll have no idea what went wrong.
I'd been meaning to write this for a while I half-stumbled on the weirdness around SQL_VARIANT a while back while writing another post about implicit conversion. What I didn't get into at the time is that it can give you incorrect results. When I see people using SQL_VARIANT, it's often in dynamic SQL, when they don't…
You've probably heard about parameter sniffing
But there's an even more insidious menace out there: Parameter Snorting.
It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at you. You've seen it happen countless times with Table Variables, Local Variables, non-SARGable queries, catch-all queries, and many more poorly thunked query patterns.
