Performance over time

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