We have moved to GitHub Issues
Created by Shannon Deminick 24 Jan 2017, 08:07:22 Updated by Claus Jensen 29 Mar 2017, 12:46:41
Subtask of: U4-9432
Create non clustered index on these columns due to the way queries work for content
I'm running the database tuning advisor now, i can't remember which queries I've seen or which advisor mentioned this one but for now i'll wait until i know before adding this.
When the cmsDocument table contains a lot of data and we run this query:
SELECT * FROM cmsDocument WHERE nodeId = 1834 AND published = 1
which is a query that is run during updating an entity, the sql profiler tells us:
/* The Query Processor estimates that implementing the following index could improve the query cost by 99.6907%. */ /* CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[cmsDocument] ([nodeId],[published]) GO */
We also run this one just after that one:
SELECT * FROM cmsDocument WHERE nodeId = 1834 AND newest = 1
which of course tells us this:
/* The Query Processor estimates that implementing the following index could improve the query cost by 99.6965%. */ /* CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[cmsDocument] ([nodeId],[newest]) GO */
However, when there aren't a ton of versions, there is no perf penalty. As far as I can tell this index has been suggested at some point because it's used during updates, however adding both of these indexes to this table will make updating slower. I've checked most of our queries in the query analyzer with a node that has a ton of documents and no other ones show up suggesting adding these or other indexes.
If/when we implement max versions then this won't really be an issue anyways.
Backwards Compatible: True
Due in version:
Sprint: Sprint 52
Story Points: 0.5