Query Cache Hashing in SQL Server

I attended a SQL Server user group about two weeks ago, and the topic was query optimization, presented by Kevin Kline of SentryOne.

I found the subject pretty interesting – there are a lot of tricks to SQL Server queries that can really impact how quickly queries return.

I want to follow up on some of the very subtle cases he demonstrated, but one thing that really caught my eye was his explanation that query results are cached using a hash of the query string.

No problem, right? Actually, the following queries are all hashed to different keys, meaning that despite returning the exact same results, they wouldn’t use the same cached results.

/* All of the following four queries will be cached separately! */

SELECT * FROM table1;
select * from table1;
SELECT *  FROM table1;
select *
  from table1;

So if you want to take advantage of caching, consistent style really matters!

But why is this? Why isn’t SQL Server upcasing/lowcasing queries, stripping returns, and collapsing sequential whitespace before calculating the hash that will be the cache key?

Seems easy enough – does anyone know why they don’t?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s