What’s Worse Than a NULL? An Incorrect NULL

Recently it was brought to my attention that the query_hash and query_plan_hash columns inside of sys.dm_exec_requests were returning NULL values. I think everyone knows how I feel about NULLs already, but this was slightly more concerning. See, I work for a company that makes this wonderful tool called Database Performance Analyzer (DPA), and we rely on those columns to have known values, just as they have since SQL 2005!

The query_hash column is useful in a handful of ways. Most notably it allows for an administrator to group queries that are similar in structure but differ in only the literal values being used. There is a great post over at TechNet that describes how you can use this information, go there and check it out. I bet you’ll find something in that post that makes you want to take a closer look at a few of your servers right away.

Anyway, this is what was being returned for my instance of SQL Server 2014 CTP2, against a light workload:

A quick sanity check on Twitter was my next step, and my suspicions that this was a bug were growing:

@SQLRockstar Null on all our prod boxes here

— Nick Craver (@Nick_Craver) March 5, 2014

When SQL Server 2014 was officially released I verified that the issue was still there. However, recently the first Cumulative Update (CU1) for SQL Server 2014 was released. I checked the notes on that page, searching for “query_hash”, and down towards the bottom I found a link to this Knowledge Base (KB) article that describes the issue exactly.

The next step was to download the hotfix and apply it to my instance. I did that, and verified the version number was updated:

 

Next, I ran the same light workload and then ran the same DMV query as above:

Success!

As an production DBA for a large enterprise shop we had some unwritten guidelines about hotfixes and service packs. I figured now was as good a time as any other to share. You’re welcome.

Service Packs – When service packs (SP) are made available we would look to schedule a rollout of the SP within the next 2-3 months. The reason for the lag had to do with coordinating efforts across many teams as they would all be required to test their applications on a weekend after the SP had been applied. Also, the 2-3 months gave us time to see if the SP had any issues that needed to be remedied.

Hotfix and/or CUs – These would be applied only if doing so would fix an identified problem (for example, a query_hash value being NULL is a problem for DPA). Otherwise we would look to keep all our servers at the same build and patch level, resulting in a more stable environment. Managing many servers at different build levels may result in additional overhead for troubleshooting purposes, so we liked to keep everything the same as much as possible.

Lastly, this is a good time to remind you that it’s important you review the details of the latest CUs made available for SQL Server. You never know when you might come across something that is the fix to an issue you are seeing, or have seen previously. It’s a good habit to get into, so start now.