none
Determining how the index is performing?

    Question

  • I created an index on a transaction table after the reports were taking time to open. After creating an index, there is no improvement.

    Is there a way to determine how the index is performing and which columns actually needs to be included in the index?

    In the Actual Execution plan, it is not suggesting any missing index.

    Sunday, March 23, 2014 6:43 AM

Answers

  • So the index is scanned not seeked. Which may perfect sense. Or not be what you intended.

    I don't think the query that Latheesh posted is very useful. Partly this is due to that your question is not really a good one. An index may be excellent in one query, be only mildly helpful in another and a complete disaster on a third query. The data in sys.dm_index_usage_stats can be useful to reveal indexes that are not used much at all, but written to a lot, and therefore should be dropped.

    To progress with the issue you have at hand, I suggest that you post the query in question, and that you upload the actual execution plans with and without the index somewhere (SkyDrive, Dropbox, web site, whatever). We need the .sqlplan files, a screen shot of the graphics won't do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 1:01 PM

All replies

  • You can use the below query to find the index usage:

    SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,
    sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updates
    FROM sys.dm_db_index_usage_stats sis
    INNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_ID
    INNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_ID
    INNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_ID
    WHERE sis.Database_ID = DB_ID('dbname') AND sis.OBJECT_ID = OBJECT_ID('tablename');

    The other way is to query cachedplan for your procedure and index usage, but I do not have a handy script for the same. 

    Then, Actual execution plan for a query will not always suggest a missing index for your query. You need to look/analyze your query execution plan for its seek/scan etc and its predicates/cost etc. If you can share with us the execution plan, I guess we would be able to help you better.

    Sunday, March 23, 2014 6:53 AM
  • Generally you consider columns in ON clauses and WHERE clause for index:

    http://www.sqlusa.com/articles/query-optimization/

    Less frequently GROUP BY columns can also be considered. 

    Covering index can be used to speed up business critical queries: http://www.sqlusa.com/bestpractices/coveringindex/

    sys.dm_db_index_usage_stats system view provides usage statistics: http://technet.microsoft.com/en-us/library/ms188755.aspx

    Post query, table/index DDL for additional assistance.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Sunday, March 23, 2014 6:53 AM
  • Can you show us the actual execution plan of the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 23, 2014 8:23 AM
  • So does the execution plan use the new index at all?

    In such case, you should compare the actual execution plan with the index both present and absent.

    Finding the best index to increase the performance of a query is not always trivial. What looks like a safe bet, may prove to have very little effect. Sometimes the optimizer does not use the index at all. In such case, try to force it. You may prove the optimizer wrong - or the optimizer may prove you wrong. (Don't cry, that happens to me as well.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 10:47 AM
  • I tried the above query on a sample table and got below results:

    From this how to determine whether index is good or bad?


    • Edited by OldEnthusiast Sunday, March 23, 2014 11:57 AM Image added
    Sunday, March 23, 2014 11:55 AM
  • Why composite index?  Is this a covering index?

    More usage data is necessary (like 1 week of data) to see the usefulness of the index.

    Can you post the query & table/index DDL?  Thanks.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Sunday, March 23, 2014 12:06 PM
  • So the index is scanned not seeked. Which may perfect sense. Or not be what you intended.

    I don't think the query that Latheesh posted is very useful. Partly this is due to that your question is not really a good one. An index may be excellent in one query, be only mildly helpful in another and a complete disaster on a third query. The data in sys.dm_index_usage_stats can be useful to reveal indexes that are not used much at all, but written to a lot, and therefore should be dropped.

    To progress with the issue you have at hand, I suggest that you post the query in question, and that you upload the actual execution plans with and without the index somewhere (SkyDrive, Dropbox, web site, whatever). We need the .sqlplan files, a screen shot of the graphics won't do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 1:01 PM