locked
Suggested index wants to index entire table? RRS feed

  • Question

  • I queried the Suggested Indexes DMV on a SQL 2008 R2 box and found that in some cases, the index suggested contained several columns in the index but every other column in the table is in the Include section.

    Example:

     

    CREATE NONCLUSTERED INDEX IX_150 ON [FL_MPI].[dbo].[InstMAIN] ([ClaimStatusCode], [ClaimCodeType], [RecordStatusID], [AmountPaid], [FirstServiceDate])

    INCLUDE ([MainID], [RecipientID], [ClaimID], [FormerClaimID], [NumDetails], [ClaimStatusTypeCode], [ProviderPatientID], [BillTypeCode], [AdmissionDate], [AdmitHour], [AdmitTypecode], [PatientStatusCode], [RecipientPayAmount], [DateBilled], [AmountBilled], [LastServiceDate], [ProviderID], [AltProviderID], [AltProviderID2], [ProviderSigned], [ClerkID], [ReimbursementAmount], [DispropShareAmount], [StmntPeriodDays], [PotentialElecBiller], [CertCode], [OverheadAmount], [ProvScopeCode], [ProvTypeCode], [ProvCountyCode], [AdmissionCode], [MedRecNum], [ClaimFrequency], [OtherInsuranceDenied], [DaysNotCovered], [FinalizeDateFirstRun], [FinalizeDateLastRun], [EncounterInterchangeStatus], [IsEncounter], [ProviderBillingOverride], [ProviderFacilityOverride], [OtherProviderOverride], [ProviderMedicaidID], [FacilityMedicaidID], [OtherProvMedicaidID], [DetailCount], [OccurrenceCount], [INPCount], [DiagXCount], [ValueCount], [ConditionCount], [HeaderCount], [ICD9Count], [PayerCount], [CrossOverCount], [NHCount], [TreatmentCount], [JobKey], [InsertDateTime]) WITH PAD_INDEX, FILLFACTOR = 90

     

    Considering this, and the fact that it will effectively double the space used by this table, wouldn't it be wiser to create my clustered index with the suggested columns of the above index and leave the Included columns out, since they don't count for the sort, and can't be included in a clustered index anyway?

    My current clustered index on that table:
    CREATE CLUSTERED INDEX [IX_InstMAIN_NumICN] ON [dbo].[InstMAIN]
    (
        [ClaimID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Primary]

    Note: ClaimID is not the Identity column, but a column of user data.

    I cannot effectively test this hypothesis due to lack of dev space and lack of test traffic in the dev DB.

    Thanks,
    Chris


    Thursday, July 28, 2011 2:25 PM

Answers

  • There are bugs with the Missing Indexes DMVs, so be careful.  There are a couple of different routes you can take as follows.

    Option 1 (recommended) is to create a trace (either via Profiler or SPs) to capture all the queries being executed against that table during a normal work cycle (e.g. day, work-week, 7-day week, month, etc.) and then either run a DTA against it or manually determine usage patterns to figure out the best candidate for a clustered index.

    Option 2 is to convert the most heavily used non-clustered index into the clustered index.  This article has a straight-forward script that you can use to determine the best index to convert into a clustered index, but it's only based upon indexes that currently exist. A variation to this is you can create all the indexes the DMV recommends, wait a normal work cycle and then run the script to determine the best index to use.

    Once you determine a good candidate for the clustered index, create it (your non-clustered indexes will be automatically rebuilt in the process) and test, test, test.

    Good luck,

    John

    Monday, August 8, 2011 7:05 PM

All replies

  • INCLUDE will add those columns key to the leaf level of the index only..... But certainly you can create CI , but can you show us your query?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 28, 2011 6:02 PM
  • There is no particular query involved here. The Missing Indexes DMV was used to retrieve the indexes SQL Server thinks would be useful for its past work load. There are lots of different queries run on the tables and I do not know which query, or queries, SQL Server used to generate its index suggestion.

    Mostly this is a theoretical question, if the DMV suggests an index that includes all of the columns in a table, wouldn't it make more sense to create a clustered index in the image of that suggested index from the DMV, but without the Includes, or course?

    To me, this makes perfect sense, but I was trying to get input from others who may have run into this situation themselves, and what they decided to do in their situation. It's starting to look like I am a pioneer in this area.

     

    Thanks,
    Chris

    Thursday, July 28, 2011 6:23 PM
  • My two cents... Every column in a clustered index affects the where the entry will be placed, so it probably doesn't make sense to do that.  It'll be better if you follow the auto-generated suggestion, instead of making guesses as to what might help or not if you don't have specific queries that you're trying to speed up.
    Thursday, July 28, 2011 6:59 PM
  • My two cents... Every column in a clustered index affects the where the entry will be placed, so it probably doesn't make sense to do that.  It'll be better if you follow the auto-generated suggestion, instead of making guesses as to what might help or not if you don't have specific queries that you're trying to speed up.


    And double the amount of space I am using for this table, which currently stands at 24G, plus 11G worth of indexes already on this table? This is in addition to the 11 other indexes the the Missing Indexes DMV suggests.I don't even know how big it would end up being with all those indexes.

    While I am not an indexing MVP, I do have enough knowledge to know that this is excessive indexing and would hinder performance more than help it, plus it would be a great way to show how NOT to use the SQL Server suggestions. This is why I am looking for someone who has been in this situation for themselves, or maybe a developer on the SQL Server team at Microsoft.

    The way I figure it, the Missing index DMV may have some good ideas, but with the number of indexes it recommends, 12, over the existing 7 on this table, now some human logic must be brought into this situation. I realize that it would be good to tune an index to a particular query, but in this case there is no one query I am trying to tune. My goal is to increase performance if possible, beyond the existing indexes on the table, by using data from the DMV to tell me what SQL Server sees when it runs its workload. I would temper this list of indexes with the list of Index Usage DMV to tell me if any of my existing indexes are not being used by the query engine, or seldom used.

     

    Thanks,
    Chris

     

    Thursday, July 28, 2011 8:00 PM
  • There are bugs with the Missing Indexes DMVs, so be careful.  There are a couple of different routes you can take as follows.

    Option 1 (recommended) is to create a trace (either via Profiler or SPs) to capture all the queries being executed against that table during a normal work cycle (e.g. day, work-week, 7-day week, month, etc.) and then either run a DTA against it or manually determine usage patterns to figure out the best candidate for a clustered index.

    Option 2 is to convert the most heavily used non-clustered index into the clustered index.  This article has a straight-forward script that you can use to determine the best index to convert into a clustered index, but it's only based upon indexes that currently exist. A variation to this is you can create all the indexes the DMV recommends, wait a normal work cycle and then run the script to determine the best index to use.

    Once you determine a good candidate for the clustered index, create it (your non-clustered indexes will be automatically rebuilt in the process) and test, test, test.

    Good luck,

    John

    Monday, August 8, 2011 7:05 PM
  • My two cents... Every column in a clustered index affects the where the entry will be placed, so it probably doesn't make sense to do that.  It'll be better if you follow the auto-generated suggestion, instead of making guesses as to what might help or not if you don't have specific queries that you're trying to speed up.

    I wouldn't recommend taking the advice of the Missing Indexes DMV as gospel, especially when it makes recommendations like those presented by the OP.
    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    Tuesday, August 9, 2011 8:26 AM