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)) AND ([m.Table2].[Column4] = @__userID_0)) AND [m].[Column1] IN (CAST(88067 AS bigint), CAST(92643 AS bigint), CAST(84549 AS bigint), CAST(84472 AS bigint), CAST(85696 AS bigint), CAST(32118 AS bigint), CAST(74084 AS bigint), CAST(90617 AS bigint), CAST(88642 AS bigint), CAST(12884 AS bigint))The problem is in the list of IDs that in each execution can be different.
When we found problematic queries, we got noticed another problem - the code is pretty can be broken by SQL Injection. Even the oldest and simplest database attack methods can break this syntax. That is very much a good example for another Eitahns post: "Could SQL Injection be dangerous even when perfectly safe?".
The customer really didn't want to change a code and we asked to exhaust configuration solutions first. Database scoped configuration "optimization for ad hoc workloads" has already been set to ON. So, we were left with forced parametrization only. Some tests, deploy to production... and nothing changed.
The simplest solution was to use a string of IDs with a separator and STRING_SPLIT built-in function, that returns a value and will safety to use. So the result was:
(@__userID_0 int, @p__linq__1 nvarchar(1000), @p__linq__2 nvarchar(1))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)) AND ([m.Table2].[Column4] = @__userID_0)) AND [m].[Column1] IN (SELECT [T].[value] FROM STRING_SPLIT(@p__linq__1, @p__linq__2) AS [T])That solution is not requested a lot of changes from the EF side and solved an issue.
That was an expected issue, that lead us to another security problem, and I am happy we solved it.
Hope that will help someone too.
More info: