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?