T-SQL Tuesday #155 – Too many single-use plans issue

This month's T-SQL Tuesday blog party is about Dynamic SQL. Lots of us have a story or a situation that worked out, and Steve Jones (b|t|L) is inviting us to share this story.

I have something recent and fresh, so ...

A few weeks ago, I read Eitan Blumin's blog post (all references are at the end of the post) about troubleshooting the query plan cache. The issue is when the query plan cache contains too many different plans for the same query hash. That could happen because of possible parameterization issues.

This situation is classic for Entity Framework heavy users. By its very nature, EF creates an execution for each query as a string and sends it to the database. To clarify, each distinct character (even space) in the query will be interpreted by the SQL Server query processor as a new query. In this situation, a lot of plans are single-use plans and become to be trash after one use in the memory.

One of my customers is not using stored procedures, by their own decision. All SQL commands are generated by Entity Framework. So, I've decided to check that script on this specific customer database environment.

The customer running on 2 Azure SQL databases, so...

The result on the first one: single-use plans take 1,079.21 MB (49.60%) of total cached plans. Most of that was taken by 6 plan hashes only! Damm!

On the second DB, even worst: single-use plans take 1,314.38 MB (68.18%) of total cached plans. ...by 3 plans only!!

After some investigation, we found that the problem in all cases is the same. The queries that executed with different sets of IDs.

For example:

(@__userID_0 int)SELECT DISTINCT [m].[Column1], [m.Table2].[Column1]
FROM [Table1] AS [m]
INNER JOIN [Table2] AS [m.Table2] ON [m].[ID] = [m.Table2].[ID]
WHERE ((([m.Table2].[Column2] = 61) AND ([m.Table2].[Column3] <> 1