Performance over time

by Sep 1, 2015

  • #2931

    Profile photo of ranger


    Hi everyone.
    I need to show how performance has been affected (hopefully improved) after the introduction of some new indexes.

    Can anyone suggest a way of doing this via SQLdm , using the reports or another method ? Currently I can’t see an easy way to show this to management other than using the top queries report and looking at avg duration.

    Is there a better way to show this to management via SQLDm ?



    Profile photo of Scott Stone
    Scott Stone


    I put this question to a few experienced support and system engineers and they provided a few suggestions. Query reporting is good, and also disk-related reporting and blocking history. Adding indexes can reduce hits to the disk for reads, so that’s a counter that should see improvement. And the query performance may also reduce the contention of blocking, so that’s good to show off too.

    1. Query history.

    If Queries are performing poorly, then they will likely be collected and displayed within the query monitoring. This poor performing query may be accessing a table, which requires an index. Through the “Query history” tab, there is the capability of reviewing the past performance to understand how the query performed before the change was made, by analyzing past execution duration, CPU, Reads, and writes. This approach will help to understand how performance was before versus after the change took place.

    2. Execution Plan statistics

    The collected execution plan will also show costs associated with unknown table Scans and lookups. After indexes are added, additional follow-up execution plan analysis can help to understand if there have been any changes in the “Costs” of each step, and whether indexing has improved performance:

    3. SQLdm Index analysis.

    SQLdm, also includes an index statistics analysis within the Databases tab and under the “Tables and Indexes” feature screen. This also can help determine whether newly added indexes are being used or not:

    4. Reduced alert activity and side effect alert trends

    Also missing indexes, can have other side effects reflected within SQL diagnostic manager, such as long running query trends and alerts as well as resulting blocking and deadlocking. Using the Alert view and the timeline view, you are also able to determine if past alert activity trends associated with those tables, has been reduced as well.

    ? Scott Stone, Idera Staff, Product Manager