we have below index in one of our prod database, We are seeing performance issues on this DB, particularly on this table, I thought of creating another non clustered index as shown below in 2nd create statement, My question is what is the difference of having coulumn [IncidentID] in INCLUDE statement and creating new index keeping coulmn name in CREATE statement? Do i see any other performance issues and recommendations if any for this scenario?

Thanks in Advance

     CREATE NONCLUSTERED INDEX [ix_Incident_AgencyID_includes] ON [dbo].[Incident]
               [AgencyID] ASC
               WITH (PAD_INDEX = OFF, 
               SORT_IN_TEMPDB = OFF, 
               DROP_EXISTING = OFF, 
               ONLINE = OFF, 
               ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON, 
               FILLFACTOR = 100) ON [PRIMARY]



    CREATE NONCLUSTERED INDEX [_dta_index_Incident_37_1362103893__K2_K1_3_12_14_23] ON [dbo].[Incident]
           [AgencyID] ASC,
           [IncidentID] ASC
           WITH (SORT_IN_TEMPDB = OFF,
           DROP_EXISTING = OFF, 
           ONLINE = OFF) ON [PRIMARY]
  • Index fields are stored sorted in the order that they are specified. Usually the first field listed should have the largest number of distinct values (cardinality). Include fields just help the engine avoid needing to pull the original record for that information (secondary effect) and the include fields are not sorted like the index fields. – crokusek Oct 30 '18 at 23:58
  • 3
    Your question may not be popular because it doesn't include the actual slow query and the headline is too general. – crokusek Oct 30 '18 at 23:59
  • Better concentrate on query than index.It is query and execution plan which give idea about index. – KumarHarsh Oct 31 '18 at 3:14

The difference between an index INCLUDE column and an index KEY column is that an INCLUDE column is only stored at the leaf level of the index. It is not used to identify the row in the table based on predicates. For example, if you have a WHERE clause referencing IncidentID, the index will not use the INCLUDE column to locate this record and satisfy the query. It will only use the index KEY column.

DTA is potentially recommending this additional query because there are queries that user both AgencyID and IncidentID as predicates. By including this in the index, it can potentially improve query performance by not having to scan additional pages in the index\table to satisfy the query.

You need to assess the impact of replacing the existing index with the new one (they're near duplicates, so keeping both is potentially just a waste of space) by reviewing the affected queries and testing performance using each index.

If you get performance improvements without degradation, then apply the change. If you get a performance improvement in some queries, but degradation in others, you need to determine if the benefits outweigh the cost.

Enable Query Store on the database (if it is SQL 2016 or higher) and check out the Blitz procs to give you better insight into the performance and health of your system.

  • Thanks HandyD for your answer, So the INCLUDE column will only use whenever the IncidentID column not in WHERE clause? I got create INDEX recommendation by DTA but not shown me when i run sp_BlitzIndex – Sunny Oct 31 '18 at 13:01
  • @Sunny The INCLUDE column isn't used to find records in the index when its in the WHERE clause. It can still be used by those queries, but it won't help improve the performance of finding those records. – HandyD Nov 1 '18 at 3:30

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.