Exciting New Doodads
When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those great features finally made it to 2014. Let’s talk about what they mean for you, and what they mean for us.
For you, dear reader
If you’re rocking the second-newest version of SQL Server, you can get all sorts of cool new insights into what your queries are doing when you’re not watching, as well as when you are. There are some great additions to execution plans baked in here, too. Some, like the TempDB spills, are in the actual execution plan only, but don’t let that get you down. This stuff was all included in 2016, so don’t feel like you’re going to miss out if you set your upgrade sights on the brand new. Note to self: someday this blog post is going to sound hopelessly outdated.
Seriously, if you build applications that hit SQL Server, get them tested on these newer versions. I’m not just trying to line Microsoft’s licensing pockets here, I’m trying to help you out. Many of these things are awesome for tuning performance problems. They will make your life easier. You’ll look like a hero when you can quickly diagnose why something is slow on a client’s kajillion dollar server that they don’t have any monitoring for and their DBA is a SAN admin who restarts SQL every morning because it’s using too much memory.
For us
As consultants who build tools to make your SQL Server life easier, they’re great opportunities for us to not only see how these new features work, but also to figure out ways to let you know how to read, interpret, and troubleshoot problems with new information. We’ve been hard at work updating sp_BlitzCache and sp_BlitzFirst to keep you a sane and functioning SQL user.
Having this information standard across the three most recent versions makes presenting and analyzing it much easier. So what did we do?
sp_BlitzFirst
If you run this with @ExpertMode = 1, you get a snapshot of what’s currently running at the beginning and end of the window you’re measuring. So, if you use @ExpertMode = 1, @Seconds = 30, you’ll see what was running on both sides of that 30 second window.
Pretty sweet. If you’re on a version that supports it, you’ll also get these columns in your results.
If your query isn’t running for the first time, or coming back from a recompile, these columns can have some useful information in them about all the many resources they’re using. You may be able to spot variations in here that can explain why things are slow ‘sometimes’. You may also be able to spot large memory grants on rather small queries, and/or rather large queries that for some reason aren’t going parallel, etc. Good stuff! Thanks, Microsoft.
sp_BlitzCache
Everyone’s favorite tool (poll in company chat) for spelunking your plan crevasse got some new columns and some new sort modes specifically for memory grants. Again, only if you’re on a supported version. You’ll see columns for Min and Max Memory Grants, Min and Max Used, and Average Memory Grant. There’s a new warning for when queries use <= 10% of their memory grant, which is configurable.
|
1 2 3 |
EXEC dbo.sp_BlitzCache @SortOrder = 'memory grant' EXEC dbo.sp_BlitzCache @SortOrder = 'average memory grant' |
Sorting by memory grant will sort by the maximum grant requested column, and average memory grant will sort by the largest average, determined by max grant / executions.
New columns:
New warning:
If you’re living in the present
Give these new versions a shot, and if you’re giddy about GitHub, drop us a line with any issues, or code contributions.
Thanks for reading!
