Incorrect Results: Why You Really Can’t Ignore Patches for SQL Server

Time for a Cumulative Update

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:

sql-server-2016-spill-execution-plan

Very cool to be getting improved tools for tuning in the area of spills.

, , , , ,

7 Responses to Incorrect Results: Why You Really Can’t Ignore Patches for SQL Server

  1. Naomi February 23, 2016 at 12:09 pm #

    Thanks, very interesting!

  2. Naomi February 23, 2016 at 12:13 pm #

    Also, can we expect an SP2 soon for SQL Server 2014?

    • Kendra Little February 23, 2016 at 12:18 pm #

      I haven’t heard an announcement. Usually we don’t get much advanced warning, though, so that doesn’t mean much!

  3. Dennis Parks February 23, 2016 at 3:54 pm #

    so this apparently wasn’t a problem pre 2014?? We’re still running SQL 2012.
    Thanks Kendra!

    • Kendra Little February 23, 2016 at 3:58 pm #

      It’s unclear when the issue started. This is the first instance of the fix that I’ve seen released, that’s all we know. :/

      That’s why I subscribe to those hotfix feeds!

      • Dennis Parks February 23, 2016 at 4:09 pm #

        seems like they’d have a patch for 2012 too if it’s an issue. But maybe it will take them longer to figure that out

  4. Cody February 24, 2016 at 11:36 pm #

    I find patching enjoyable but I set my standards.

    – I have reasonable outage windows. Patching fails for inexplicable reasons and I always presume it will.

    – And when it comes to AG or clusters they also need outages. I know it says “Highly Available” on the tin and it might even be sold as “uptime during patching” but there are so many caveats and ways for it to implode that I specify outages regardless.

    – If it’s outside of hours it’s not free; I get time in lieu or paid for it.

    I guess the problems come in when people try to shortcut the safety rules above, or maybe the pain comes from whoever owns the applications not having any way to test (which is almost everyone – just rolling through dev test qa prod is the most common “test” I’ve seen).

Leave a Reply