SQL Fascination

A surprising facet of the query plan cache is that it matches ad-hoc queries not only on their text, but the case of the query text must exactly match as well. This is documented quite clearly on MSDN although it is a bit of a surprising behaviour. It also does not change based on whether the collation of the server is case-sensitive or not.

The documentation gives a statement on case-sensitivity in the plan cache, but no mention of whether the behaviour changes under ‘Forced’ parameterization, which asks SQL to be more aggressive in extracting query literals and generating a query cache hit – so I decided to have a look and see how it acted in ‘Forced’ vs ‘Simple’.

Whether the database was in ‘Simple’ or ‘Forced’ the behaviour did not change – but it turns out that it is not case-sensitive on keywords,  just on the object names.

To show the case sensitivity behaviour I have used the AdventureWorks sample database as a testing ground. Prior to each test I cleared the procedure cache using DBCC FreeProcCache.

I then issued two very simple queries:

SELECT * from humanresources.employee Select * from humanresources.employee

When the query cache is inspected, there are two entries – it remains case-sensitive.

sql_statement                                 execution_count
--------------------------------------------- --------------------
SELECT * from humanresources.employee         1  
SELECT * from humanresources.employee         1

The query so simple and has no parameters so I suspect the simple / forced parameterization routines must not activate.

If we add a parameter to the query, then the parameterization activates and gets to work on the query string prior to the cache lookup. Both simple and forced are able to cope with such a simple query so both performed the parameterization.

SELECT * FROM humanresources.employee WHERE employeeID = 1
Select * From humanresources.employee Where employeeID = 1

Inspect the query plan cache when running in forced:

sql_statement                                             execution_count
--------------------------------------------------------- --------------------
(@0 int)select * from humanresources . employee           2
where employeeID = @0

Inspect the query plan cache when running in simple:

sql_statement                                             execution_count
--------------------------------------------------------- --------------------
(@1 tinyint)SELECT * FROM [humanresources].[employee]     2
WHERE [employeeID]=@1

The results show a plan cache hit, but more importantly show up a rather obvious difference in the parameterization routines for each mode:

• Simple changes keywords to upper case, Forced changes them to lowercase.
• Simple places square brackets around the objects, forced does not.
• Simple chooses to replace the literal with a tinyint, Forced uses an Int.
• Simple starts the parameters at @1, forced starts at @0

The differences can be filed under bizarre, strange and just inconsistent, although they do both get the job done, which counts at the end of the day.

What is then disappointing is that the same is not true for the tables and fields named in the query. Changing the case of one of the objects prevents the caching again.

select * from humanresources.employee where EmployeeID = 1
Select * From humanresources.employee where employeeID = 1

Inspect the query cache (this one from forced mode):

sql_statement                                            execution_count
-------------------------------------------------------- --------------------
(@0 int)select * from humanresources . employee          1
where EmployeeID = @0
(@0 int)select * from humanresources . employee          1
where employeeID = @0

So we are back to the situation of no cache hit.

It seems very strange that the parameterization only ensures the casing of the keywords is consistant to give it a better chance of a query plan cache hit – if this was a case-insensitive server than it is a valid optimization to try increase the chances of a plan cache hit.

The converse you would think, is that it would be an inherently risky optimization on a case-sensitive database? – but in fact it is an optimization that would never be needed or made – if anything a case-sensitive database server will have a better chance of making a query plan cache hit since all the tables names and field names have to exactly match the stored object names – and so the queries will which have a greater chance of matching each other.

It could clearly do more to try give a match, but I suspect the complications and edge cases, such as  database / server case-sensitive collation mis-match account for why it might seem easier than it really would be to make better.