Problem
I want to view the performance of my SQL Server indexes across the database farm and determine the top instances by index disk usage. I also want to look for servers which encountered problems when running the update index statistic job.
Solution
dbWatch is capable of monitoring your server’s index usage rate and perform maintenance routines to make sure that your servers are running smoothly.
In this blog you will learn how you can analyze indexes within your database farm and determine which instance has its maintenance jobs running longer than the usual by using dbWatch’s available views.
Example 1: Index Analyze Statistics View
At the left side choose the Monitoring tab > Go to the Maintenance tab and select the Analyze Index tab.
Within this view you will be able to see useful information such as the database count per instance, index count per instance and total index analyzed by the task. It will also show you the number of errors within the execution of update statistics task if there are any.
You can easily sort the columns in ascending or descending order. This will help to determine which instance within the farm has the longest/shortest elapsed time when analyzing indexes, this is useful if some jobs take longer time to finish than usual.
An example is when you have a scheduled index rebuild job weekly, from Week 1 to Week 7 your average elapse time was around 15-20 mins. to finish. But suddenly when you look at the dbWatch view it shows that this week (Week 8) it took 80-90 mins. to finish.
The average time increased 4 times its usual execution, this tells you that there is something going on within your servers – from there on you will start investigating the cause of the problem.Â
Going back to the views, If you right click on a selected instance you have an option to execute the task and update the index statistics. You can also configure the tasks. If you choose Details, you will see a report regarding the history of the update index statistics execution and the total number of indexes scanned and error history of the update statistics task.
Example 2: Index Reorganize and Rebuild View
At the left side choose the Monitoring tab > Go to the Maintenance tab and select the Reorganize Index or Rebuild Index tab.
This view displays the index rebuild/reorganize statistics. Here you will see the list of instances and the number of database checked within the instance, and it also shows which databases was rebuilt and which was ignored.
In this view you will be able to sort the column to determine which instance consumes the most disk space.
The important part of this view is the size before and after the index rebuild took place and the disk usage reduced by the operation. In this example – the highlighted instance which you can see was reduced from 24.5Gb to 23.2Gb. A total of 1.3Gb was achieved.
If you right click on a selected instance, you have an option to execute the task and start an index reorganize/rebuild task on the instance databases’ or configure the task and its schedule.
If you choose Details, you will see a report regarding the history of the reorganize or rebuild execution within that instance, it also shows the information about the table and index fragmentation average and the disk usage before and after the reorganize or rebuild operation.
With the following examples provided you will be able to determine which instances has the biggest index disk usage within your database farm. You will also be able to assess if a certain index operation schedule should be adjusted based on the performance and other details shown in the reporting view.
