SQL Server 2012 SP3 has just been freshly released! Not only does this release contain bug fixes, it also contains a slew of performance tuning related features.
Many of these features are about memory grants. This is NOT the memory used in the buffer pool/ data cache by the query — it’s the memory also known as Query Workspace Memory. Start reading more here.
Check these out:
KB 3107398 – Improvements to sys.dm_exec_query_stats
This is kind of a big deal. The sys.dm_exec_query_stats DMV will now report on total, last, min and max statistics for granted workspace memory, ideal workspace memory, degree of parallelism, and reserved threads. These columns are in SQL Server 2016 CTP3, but it’s really awesome that they’ve been backported town to 2012!
KB 3107397 – “Actual Rows Read” Added to Query Execution Plans
This is really exciting! It’s hard for people to read execution plans. You may have a very large index seek or scan that reads a ton of rows, and it has a hidden filter in the seek or scan. But it may be hard to diagnose because you only see the number of rows to come OUT of the seek or scan operator. This lets you know not only the rows that exit the operator, but how many it had to consume, too. It’s sorta like having a little bit of STATISTICS IO in your execution plan!
KB 3107401 – New Query Hints for min_grant_percent and max_grant_percent
Previously, if you identified that a query was asking for a problematic memory grant, your options were pretty limited – code changes, index changes, resource governor, or server wide settings. These options let you shoot yourself in the foot tune this just for a single query. Check out the KB– there is a safety in the feature. If the minimum required grant is higher than max_grant_percent, it will still get the minimum.
KB 3107172 – hash_spill_details Added to Extended Events to Diagnose tempdb Spills, Improvements to sort_warning and hash_warning
Ever seen sort or hash spill warnings in execution plans and wondered how much of a difference they were making to the runtime of the plan? That may be less of a mystery with these improvements.
KB 3107173 – query_memory_grant_usage added to Extended Events
This XEvent allows tracing for the ideal memory grant for queries, what they actually got, and the degree of parallelism they used.
What About SQL Server 2014?
I don’t believe any of these are currently available for SQL Server 2014, but looking at past release patterns it’s likely they’ll be available soon.
PSA: Don’t Forget to Test Your Service Packs
Even the best laid plans can sometimes go awry.
9 Comments. Leave new
How come these features have been added to such an old version first? I assume they will soon make their way into the newer versions. Any hunch on why this is?
I’m so excited that this got backported to 2012! But I don’t know any specifics as to the timings of release cycles other than what Microsoft publishes about them.
tobi, it’s based solely on lifecycle of each major version. Most of these enhancements were already in SQL Server 2016, and are now starting to be back-ported. SQL Server 2012 happened to have a service pack on schedule anyway; I suspect we’ll see some of these added in the next SQL Server 2014 service pack (though some could appear in a cumulative update).
As Aaron pointed out, these fixes are already available for SQL Server 2016 CTP releases and some of them have been available in the form of trace flags. These are not features but improvements which existing workloads could benefit from implicitly or improve troubleshooting/monitoring experience. The backport landed on the SQL Server 2012 SP3 as this was the first Service Pack to ship. We will keep the community updated through online documentation, blog posts etc. as we provide such updates/enhancements in the future.
Very cool stuff indeed!
BTW, it’s weird that it doesn’t appear on the “Microsoft SQL Server Release Services” blog — I use its RSS + IFTTT to get alerts by email. How do you guys find this out? (And yes, I subscribed to your sqlserverupdates site as well.)
I found out about this via the SQL Server KBs blog feed. I opened up feedly and was like “OH WOW, THIS IS AWESOME!” http://support.microsoft.com/common/rss.aspx?rssid=1044
Cool, thanks. It’s now up on the release services blog as well.
Alex, I can only speak for myself, but I have various sources for new or pending updates – the MVP list, the product group, and premium support.
You might also want to add the Tiger Team’s blog to your RSS collection; they’re the first group to blog about this SP: http://blogs.technet.com/b/dataplatforminsider/archive/2015/11/24/sql-server-2012-sp3-is-now-available.aspx
You should find the SQL Server 2012 Service Pack 3 post on the Release Services blog at http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/11/24/sql-server-2012-service-pack-3-is-now-available.aspx