on February 22, 2016
Patching isn’t fun. It’s time consuming, thankless, and easy to break stuff.
But you can’t skip it. For critical systems, you need to subscribe to patch lists for SQL Server and review issues that are fixed regularly.
Here’s an example of a SQL Server fix that came out this week.
KB 3138319 - FIX: Numeric overflow when you run a query that spills more than 65,535 extents to tempdb in SQL Server 2014
Read the details of the fix first here.
Unpacking the KB:
- This impacts queries with spills to tempdb where the spill uses more than 4GB (65,535 extents)
- The query doesn’t fail (this is implied but not explicitly stated)
- The query returns incorrect results. Just plain wrong stuff to your users.
Well, then. Incorrect results is kind of a big deal.
Has this happened to you?
Well, it’s hard to say. Tempdb spills are fairly common– all you need is a sort or a memory-requiring join where the optimizer underestimated significantly how much data it had to work with. With plan re-use, estimation problems in optimization, and increasing data sizes, I believe 4GB spills are probably not uncommon.
And because the query doesn’t fail, this is super hard to monitor for.
Subscribe to Get the Most Recent KBS for SQL Server (RSS)
Use this link to subscribe to the RSS feed: Most Recent SQL Server KBs
Cumulative Updates are important - but don’t forget to test them
For mission critical data, you should have a release cycle for SQL Server version updates that includes development, test or pre-production, and finally a staggered release to production – just like any other code change.
It’s a dirty job, but someone really has to do it.
Note on SQL Server 2016 Execution Plans (post updated 2/23/2016)
On the topic of spills and extents, I noticed a cool new perk in SQL Server 2016. We’re getting TONS of new information in actual plans if a tempdb spill occurred! Look at this:
Very cool to be getting improved tools for tuning in the area of spills.