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.

Previous Post
How to Choose Between RCSI and Snapshot Isolation Levels
Next Post
Faking Read and Writes in SQL Server’s Index DMVs (Trainer Resource)

Related Posts

7 Comments. Leave new

  • Thanks, very interesting!

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

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

    Reply
    • 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!

      Reply
      • 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

        Reply
  • 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).

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu