Problem:
I had a query ( query available down under) that was filtering the dbo.bla based on a @begindate and @enddate variable.
The query took ages and eventually would timeout.
When I use a hard value instead of a variable the query would run in 50 seconds.
I have tested the query using hard date values and variables for begindate and enddate filtering.
I investigated both XML-query plans and noticed:
SerialDesiredMemory=”4065552″ –using variables
SerialDesiredMemory= “156856” –hard values
You can find this in the XML-lines:
The cause:
When the optimizer is using variables, then the optimizer can make the wrong memory estimate, it does not base the memory claim based on the variable range, it bases the memory claim
based on all available dates in a table. It will the wrong claim memory before execution.
If the memory is not available, then the query will wait in a waiting line. More queries claiming the wrong memory will make the line greater.
The more queries in line, the higher your CPU.
Solution:
You can either use hard values what is probably not an option or you start using OPTION(RECOMPILE) at the end of you query. This forces the optimized to have a better look on the variable range.
Example:
SELECT COUNT(DISTINCT h.blaId) * 2 AS fee
FROM bla h
WHERE datecreated between @begindate and @enddate OPTION(RECOMPILE)
So. I would like to ask developers to have a look at their execution plans and when they noticed the excessive memory desire to use OPTION (RECOMPILE) and compare the execution plans.