Estimate the effectiveness of an index in SQL.

Posted on 2011-10-28
Last Modified: 2012-05-12
I have been advised that adding an index to a table in SQL Server will increase performance in my database when it is queried. I have no doubt that this is true, but I would like to know a way to prove the effectiveness of the index - whether this be a before and after report... I want a way of documenting the effectiveness of an index. Any ideas?

I have tried using a query execution plan, but the results are identical before and after
Question by:Blowfelt82
    LVL 43

    Expert Comment

    Many years ago I made a table filled with many millions of records and a query to filter records.
    Then I ran the query.
    When the field was NOT indexed it took it a couple of minutes.  
    When the field was indexed it took less than one second.
    LVL 5

    Accepted Solution

    I also tested index performance on that table contains records in millions and there also that table contain recursive foreign key so when i executing query with some self join make much time but after implementing index it shows difference in fetch of data with less time.  Try to use index on that table which have records in millions and the probability of change (Insert,Update etc.) in table data is less because such type of operations takes more time because of indexing.
    Here are some useful links


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now