I am working on Consolidation INDEX. I have 3 index on a table

Create NONCLUSTERED INDEX IX1 on TableA(a asc,b asc) INCLUDE(U,V,X,Y,Z)
Create NONCLUSTERED INDEX IX2 on TableA(a asc,b asc) INCLUDE(K,L,X,Y,Z)
Create NONCLUSTERED INDEX IX3 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z)

So as the composite key is same and the order is also same. I thought we can have 1 index rather than 3

 Create NONCLUSTERED INDEX IX4 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z,K,L)

So , i have 2 Question:
1 : Will creating new index for the 3 will work?
2 : Some developers tell me that difference in include statement as now I have (U,V,X,Y,Z,K,L) all in one can have significant difference in performance so ,I can't club them in 1?

  • 1. Yes; 2. Yes, you can. Assuming that all 3 indexes are used regularly, having one larger index would be space/memory efficient than 3. – Alex Jul 24 '19 at 6:38
  • How many columns does TableA have? If all the columns that TableA has are U,V,X,Y,Z,K,L (and a,b,c) then consider removing the include statement. – Alex Jul 24 '19 at 6:40
  • TableA , has many columns – user185981 Jul 24 '19 at 6:48
  • Analyze ,TableA will be query on which columns frequently and how many rows each rows such can fetch.So your answer also depend these question.You can read this dba.stackexchange.com/questions/242673/… – KumarHarsh Jul 26 '19 at 5:26
  1. Yes it will work.

  2. It will have some difference in performance but will it be significant or not depends on the indexes usage. Scan operation will become more expensive because an index row is wider on the leaf level. On the other hand you will have one index in your buffer pool instead of three.

I would start from investigating the indexes current usage to make a reasonable decision.

Check user_seeks, user_scans, user_lookups columns:

    SELECT i.name, s.*,si.*
    FROM sys.dm_db_index_usage_stats s
        JOIN sys.indexes i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        JOIN sys.sysindexes si ON si.id = i.object_id
            AND si.indid = i.index_id
    WHERE s.object_id = OBJECT_ID('YourTableName')
        AND s.database_id = DB_ID()
    ORDER BY i.name

Check query plans for a particular index:

DECLARE @IndexName AS NVARCHAR(128) = 'IX1';

SET @IndexName = QUOTENAME(@IndexName); 

-- Dig into the plan cache and find all plans using this index 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')    
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text, 
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName, 
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName, 
obj.value('(@Table)[1]', 'varchar(128)') AS TableName, 
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName, 
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind, 
    SELECT try_CONVERT(XML, qp.query_plan) AS query_plan, cp.plan_handle, OBJECT_NAME(qp.objectid, qp.dbid) AS ObjectName, qp.objectid, qp.dbid
    FROM sys.dm_exec_cached_plans AS cp 
        JOIN sys.dm_exec_query_stats s ON s.plan_handle = cp.plan_handle
        cross apply sys.dm_exec_text_query_plan (s.plan_handle, s.statement_start_offset, s.statement_end_offset) AS qp 
) x
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj) 

1) Yes you can combine them but shouldn't use them unless you have to 2) They may be right or wrong, need to test it in dev with same queries/workload to see.

I've heard that composite indexes are only helpful if you have a lot of queries hitting multiple columns at once. Like:

select item from table where item => column1 and column2= 6 and column3 = 8000

It would make sense to combine three different indexes into one with most-used column1 first and include column2 and then column3 last. ORDER DOES MATTER AND ALWAYS PUT MOST USED FIRST TO LEAST USED LAST

If you are mostly hitting tables with only 1 where clause, you should keep them separate from what I've heard.

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.