Why You Should Test Your Queries Against Bigger Data

I Like Testing Things

Especially Brent’s patience. And I like watching people test things. SQL Server is sort of perfect for that. You make some changes, you hit f5, and you wait for something to finish. If it’s faster, you ship it. Right? I mean, we all know you just added NOLOCK hints anyway.

A long time ago, at a company far, far away, I did something stupid. I was working on a dashboard report, and I used data from a past project to replicate the layout. The old project only had about 1000 users, so reporting on it was simple. We didn’t even need nonclustered indexes. Of course, the new project ended up fielding 2.6 million users, and got extended for 12 months. It ended up with close to 4 million users. The dashboard queries stopped working well after around 100k users, if you’re wondering.

Working on sp_BlitzIndex

Is fun and rewarding and you should totally contribute code. We recently added some informational checks for statistics. If they’re out of date with a bunch of modifications, if they’re using no recompute, and some other stuff. Cool! I wanted to get something started for when I find out the details on this Connect Item getting fixed. It’s easier to add information to a pull than it is to add a whole new pull of information. Just figuring out column nullability is a headache. I MEAN FUN AND REWARDING NOT A HEADACHE!

While working out some kinks, I wanted a way to create a ton of statistics objects to see how slow the queries would run under object duress. This is handy, because we may skip or filter certain checks based on how many objects there are to process. For instance, we won’t loop through all databases if you have more than 50 of them. That can take a heck of a long time. Thinking back to one of Brent’s Bad Idea Jeans posts, I decided to do something similar creating statistics.

Different Strokes

Indexes have slightly different rules. You can only have 30,000 of them per object. Well, fine, but I don’t need that many. I only want to create about 100,000 total. In my restore of the Stack Overflow database, I have 11 tables. After some tweaking to this script, I got it so that It creates 100,044 objects. I had to do some maneuvering around not touching views, and not hitting certain column data types.

The thing is, I’m also lazy. I don’t want to copy and paste 100k rows. No thank you. I’m gonna loop this one and I don’t care who knows it. You can uncomment out the EXEC if you want. There’s precious little defensive scripting in here, I know. But creating 100k stats objects is going to take long enough without me protecting myself from self SQL injection.

So What Did I Find?

Well, after adding 100k objects, the query favoring the new syntax still finished in 2 seconds. The older syntax query ran… Forever. And ever. Maybe. I killed it after 30 seconds, because that’s unacceptable. For reference, this is the ‘old query’ that doesn’t use sys.dm_db_stats_properties, because it wasn’t invented yet. I have to hit sys.sysindexes to get a little bit of the information back. Right now, this query pulls back a few more columns than necessary, but will likely be used when I finish adding gizmos and doodads. It’ll be grand.

Where’s The Beef?

The first thing I want to find out is if one of the join conditions is the culprit. I’ll usually change my query to a COUNT(*) and comment out joins to see which is the most gruesome. In this case it was, of course, the one I really needed. Joining to sys.sysindexes. Without it, the query finishes immediately. Of course, it also finishes without some really helpful information. So I can’t just skip it! I mean, I could just not give you information about statistics prior to 2008R2 SP2, 2012 SP1, etc. But that would leave large swaths of the community out. You people are terribly lazy about upgrading and patching! So the kid stays in the picture, as they say. I think.

Another thing I figured out is that if I filter out rows from sys.sysindexes that had 0 for rowcnt, the query was back to finishing in about a second. Unfortunately. that seemed to filter out all system generated statistics. I tried my best to get SQL to pay attention to them, but never got anything out of it. That sucks, because now I can’t give you any information about your system generated stats, but there’s nothing I can do about that part. Aside from being outdated, they wouldn’t get caught in any of our other queries anyway. They’re probably not going to be filtered or created with no recompute, and we can’t get rows sampled here either. So, out they go. If you ever wonder why you don’t get as much cool stats information on older versions of SQL Server, this is why.

Back To The Original Point

If I hadn’t tested this query against way more stats objects than most databases probably have, I never would have figured this out. If you have way more than 100k, let me know, and I’ll test against a higher number. The next time you’re testing a query, ask yourself if the data you’re developing with is near enough to the size of your production data to avoid missing obvious perf bottlenecks.

Thanks for reading!

Previous Post
Silent Demo: The Plan Cache
Next Post
Reminder: Rate Your Summit Sessions (and Watch Mine Now!)

2 Comments. Leave new

  • Geoff Patterson
    December 8, 2016 3:21 pm

    SELECT COUNT(*) FROM sys.stats
    — 12,193,760

    It’s a dev / unit testing playground though, not production. Up to you whether that warrants further stress testing of sp_blitzIndex 🙂


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.