July 6, 2015 at 8:36 am
Hi geeks,
We have a table in our shop that I believe some of the indexes are not good. so I looking to redesign the indexes at some points and need some advices on which would be the best for these indexes.
Here are columns in the table:
ID BIGINT Identity(1,1)
[ParentID] UniqueIdentifier,
[ChildID] UniqueIdentifier,
[RelDef] UniqueIdentifier,
[ChildTitle] nvarchar(50)
*ChildTitle always goes with ChildID
*ParentID, ChildID, RelDef,ChildTitle combination is assume to be unique
*Assume lots of update & delete going on with this table
Below are few most common search queries I have seen:
WHERE ParentID=someguid
AND ChildID=someguid
AND RelDef=someguid
AND ChildTitle=somestring
WHERE ParentID=someguid
WHERE ChildID=someguid
Below are my plan for indexes:
Index1: ID (clustered)
Index2: ParentID (nonclustered)
Index3: ChildID, ChildTitle (nonclustered)
Index4: RelDef (nonclustered)
Thanks in advance
July 7, 2015 at 2:22 pm
In general it's hard to answer your question without knowing more, such as what the selectivity of the various columns is. I would start with this: what is it that makes you think the indexes need to be changed? Are you observing poor query performance, and if so, what do the plans tell you?
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
July 8, 2015 at 10:49 am
Thanks for following up. I came cross an instance that we have to update and delete 20+ millions of records from the table. and that has been extremely slow, although it is not something we do on a regular basic but if anything helps improve general performance will go for it.
the table in question current has indexes designed in a strange way as follow:
Index1: ParentID, ChildID, RelDef, ChildTitle (unique clustered)
Index2: ParentID (nonclustered)
Index3: ChildID (nonclustered)
Index4: RelDef (nonclustered)
Index5: ChildTitle (nonclustered)
Index6: ID (nonclustered)
July 8, 2015 at 2:35 pm
haiao2000 (7/8/2015)
Thanks for following up. I came cross an instance that we have to update and delete 20+ millions of records from the table. and that has been extremely slow, although it is not something we do on a regular basic but if anything helps improve general performance will go for it.the table in question current has indexes designed in a strange way as follow:
Index1: ParentID, ChildID, RelDef, ChildTitle (unique clustered)
Index2: ParentID (nonclustered)
Index3: ChildID (nonclustered)
Index4: RelDef (nonclustered)
Index5: ChildTitle (nonclustered)
Index6: ID (nonclustered)
According to the posted queries, you might only need indexes 1 and 3.
However, I'm uncertain on having the clustered index the same size as the table. I can't understand the need of an identity column either.
July 9, 2015 at 8:26 am
So if I understand, the process that is running slow is the mass delete of the rows, but regular queries do OK? If that's the case, my advice in general would be: disable all indexes on the table (except for one that might be useful for selecting data for purging) and delete rows in batches (i.e. 1M or so at a time) to avoid long running transactions. If there are other queries that are running slowly I'd really need to see a plan to be of much help, since there's many factors that might explain that beyond the indexes themselves (i.e. the index might not even be chosen by the optimizer for various reasons). I'd recommend using SQL Sentry Plan Explorer to capture an actual plan, anonymize it, and posting it if that's something you are looking for. Hopefully I'm understanding you correctly?
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply