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!