SQL Fascination

Time flies when you’re busy, and it has been far too long since I last posted. To business however, and I noticed a problem in query parameterization the other day which does not make much sense at first glance. To demonstrate, I will use the AdventureWorks example database and use a couple of simple queries. As with all parameterization issues, you need to make sure that you know which mode the database is within, so I’ll begin by setting it to Simple Mode

ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE

And then run two queries, separately so that they are not considered a single batch.

Select * from HumanResources.Employee where loginid like '%a%'
Select * from HumanResources.Employee where loginid like '%b%'

Under simple parameterization it should not be too surprising to see that when the query cache is inspected, the queries have not been parameterized, and 2 entries exist within the cache. So what happens when the mode is changed to Forced?

ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED

Clear down the query cache and try the two queries again, in the hope of a plan cache hit – and it hasn’t changed. Two query plans still show in the cache and there was no parameterization. Perhaps it is the existence of the 2 wildcard characters? no, altering the wild-cards makes no difference, removing them entirely still results in the query plan generating a separate plan cache entry.

Parameterization is not limited to dates and numbers, it will work on strings without any problem, but clearly the introduction of the like clause prevents the cache hit. This behaviour is on both SQL Server 2005 and 2008 – which is a bit annoying.

So how can we get around this problem?

Well bizarrely by just using a different syntax to mean the same thing. PatIndex works just like the like clause and takes a wildcard, but returns the position. In a like clause we are just interested in a match – whilst the pat index gives us a character position. If the pattern is not found it returns zero, so the simple replacement is to using patindex and look for any values greater than zero.

Select * from HumanResources.Employee where patindex('%a%',loginid) > 0 
Select * from HumanResources.Employee where patindex('%b%',loginid) > 0

In simple mode this still produces 2 cache hits, but in forced mode you get a plan cache hit finally!

If solving it was only that simple… by using PatIndex the query where clause has become non-sargable – which makes no difference if you have a wild card either side of your expression but if you only had a trailing wildcard then this would produce a very bad performance hit. The cost of the extra query plan in memory is unlikely to be more than the use of scans to resolve the query, so faced with a few additional query plans in memory using wildcards, you might be best to leave them there.

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.

This comes from a question I had a couple of days ago – the SQL Server: Buffer Manager : Page Life Expectancy provides a performance counter that indicates the current lifetime of a page within memory. As data pages and query object pages are being added to the buffer pool they will of course build up and SQL will come under memory pressure as a result. The normal advice is that this figure should be above 300 seconds, indicating that a page should stay in memory for at least 5 minutes.

This figure however, includes both the data cache and the procedure cache – which means you can not determine whether the pages being flushed are a result of churning data pages or you are in a situation where ad hoc queries are flooding the procedure cache. You can of course look at the procedure cache using DMV’s and see the number of objects grow and then shrink, but this is not particularly scientific, nor is it measurable within a trace.

The page life expectancy can easily be traced within Perfmon, but how do you measure the procedure cache? well are a couple of events you can trace in SQL profiler, the primary one I would like to be working do not seem to properly register the event, whilst the secondary does at least work. The two counters are SP:Cache Remove and SP:Cache Insert.

The SP:Cache Remove has 2 Event Sub Classes listed in documentation produced by the SQL Programmability team, sub class 2 is for a deliberate procedure cache flush, such as a DBCC FreeProcCache command, sub class 1 is for when a compiled plan is removed due to memory pressure. In testing the deliberate procedure cache flush does show up in the profiler traces, with an event subclass value of ‘2 – Proc Cache Flush’ – but after a number of tests, I can not ever get the event to be raised when the procedure cache is under memory pressure. If it did then we  would have exactly what I was after, an easy, traceable and recordable way to show a procedure cache under too much pressure.

The SP:Cache Insert is more of a backup mechanism to show the procedure cache is being flooded, but only on the basis that you would count the number of times this event shows up within a trace over a period of time. In essence a SP:Cache Insert is only going to occur if a query does not have a matching query plan within the cache. A large number of these within a short period of time is also going to be an indication that the procedure cache is potentially being flooded.

Combine a large number of SP:Cache Inserts with a low Page Life Expectancy and you can suspect you definitely have a procedure cache flooding problem.

So there is a kind of mechanism to determine whether a low page life expectancy is from data page churn or query page churn, but if the SP:Cache Remove subclass 1 event actually worked, it would be a lot easier. Once you know your plan cache is being flooded, you are then looking to check whether forced parameterization is the worth using to eliminate the issue.