Workloads that generate the 0x0000000000000000 query hash value?

In the last SQLskills Insider demo video I went through a scenario where you can use the query_hash column from sys.dm_exec_query_stats to aggregate statistics for queries that only differ by literal values.

I received an interesting question from someone who watched the video, ran the demo query in production and saw a 0x0000000000000000 value as the highest consumer of worker time (and also had the highest execution count).

I asked this person to show me the query text where [query_hash] = 0x0000000000000000 and it ended up being a series of FETCH NEXT and FETCH API_CURSOR calls, for example:

FETCH API_CURSOR00000000072ADF22
FETCH API_CURSOR00000000072A0B6E
FETCH API_CURSOR000000000729B7B2
FETCH API_CURSOR000000000729030C
FETCH API_CURSOR000000000729030C

There may be other circumstances where 0x0000000000000000 may manifest – but I haven’t witnessed them or heard of them. If you have non-cursor scenarios where 0x0000000000000000 occurs, please share in the comments.

Thanks!

Other articles

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 2″

Today, Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 2“. Part two is 2 hours and 17 minutes long

New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 1”

Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 1“. Part 1 is 2 hours and 31 minutes and

SQLIntersection Post-Conference Session

I am really happy to announce that Kimberly Tripp and I will be delivering a post-conference in November’s SQLIntersection conference at the MGM Grand. The

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and

Finding Timeout-Prone Code with max_elapsed_time

Consider a scenario where your application timeout setting is 30 seconds and you’ve been asked to proactively start tuning workloads that are at risk of

Deck from “Practical SQL Server Cardinality Estimation”

I uploaded the deck from my session for SQLSaturday #287 Madison 2014 and you can download it here. It was an excellent event! Great organizers,

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.