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!

  • I wonder if it might make sense to create a covering index with releaseDate as the leading key column. If you are often querying by this column I think you'll have much better luck with a seek predicate there compared to a regular predicate. In addition you should be parameterizing the predicates (or using stored procedures) as Tara suggests. – Aaron Bertrand Jul 25 '17 at 17:42
15

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.