3

So, I've ran the BrentOzar script which has identified 10195 plans for the same query!!! The query is below: 

SELECT * 
FROM [table1]
INNER JOIN [table2]
  ON [table1].[versionId] = [table2].[VersionId]
INNER JOIN [table3]
  ON [table2].[ContentId] = [table3].[nodeId]
INNER JOIN [table4]
  ON [table3].[nodeId] = [table4].[id]
WHERE ([table4].[nodeObjectType] = 'abcde123-fgh3-4ijk-8lmn-424f222332ff')
  AND ([table1].[published] = 0 
  AND [table1].[releaseDate] <= '2017-07-22 17:43:47')
  AND ([table1].[newest]=1)
ORDER BY [table2].[VersionDate] DESC, [table4].[sortOrder]

The only difference between all 10195 of them is the date field (releasedate). value which has a different date for each plan.

Regarding indexes, the following applies:

  • Table1: NC on nodeid and versionid, C on versionId
  • Table2: NC on versionID, C on ID
  • Table3: NC on nodeid, C on pk
  • Table4: NC on nodeObjectType, NC on parentID, C on ID

Anyone any ideas what's the best way to resolve this as it's getting a big ridiculous!

Thanks in advance!

Community
  • 1
Stephen Martin
  • 145
  • 1
  • 8

1 Answers1

14

You can set the Forced Parameterization option at the database level to avoid this issue, but it does have a drawback in that queries could now experience parameter sniffing issues. CPU utilization should decrease as a result of the change. I consulted on a system that had very high CPU utilization and very high compilations per second. Enabling Forced Parameterization dropped the CPU utilization by about 50%. Another system had minimal change in CPU utilization since their compilations per second wasn't that high. In both cases, the plan cache size decreased as queries were now reusing plans in cache rather than compiling a new query plan each time.

Alternatively, you could modify the application so that it is sending over parameterized queries/prepared statements instead of adhoc SQL. Or use stored procedures.

Tara Kizer
  • 5,986
  • 1
  • 17
  • 28