In-Depth Analysis
SQL diagnostic manager can tell you just about anything you need to know about the SQL Servers in your environment. And although there is a lot of information revealed within the product, it is intuitive and easy to navigate. For example, in the screenshot below, if you want to change the view to another server, you don’t have to go back to the initial window, you can just change the server from the drop-down list. In addition, from the tool bar you can view the error log of the SQL Server that you have selected.
Here are a few other handy features that I think any DBA will benefit from:
- Click any SQL Server on the tree to get a Server Overview (below). You will see a complete list of all managed databases with critical status information. If there is a problem with a database it will be highlighted in red, so that you can identify quickly and easily any databases that may be experiencing problems.
If you want to view information about a particular database — down to the most minute detail — simply double-click any database in the grid (shown above). You’ll be able to view a list of tables and many other helpful details in the Database window:
- On the Backups/Restores tab, you can view all backup jobs and critical backup information like backup type, date and time of the backup, size and device name, etc.
- On the Files/Devices tab, you can view a graph of the database data file and log file with valuable analysis information such as used and unused space — from which you will be able to plan for future capacity.
The screen below shows you the depth of information that SQL diagnostic manager provides about tables, indexes, and how they are stored. SQL diagnostic manager will also allow you to view the fragmentation status of each table, which is especially helpful because fragmentation can diminish the performance of your applications. Normally, as a DBA or developer, we use the DBCC command to reduce the fragmentation. With SQL diagnostic manager, you can save time by simply clicking the Re-index icon from within the user interface to correct a fragmentation issue automatically. SQL diagnostic manager will actually re-index the table in the background using the DBCC REINDEX command.
Clicking the Dependencies tab will display all the dependences of a particular table, such as foreign keys, views, triggers, check constraints, defaults, and most importantly, stored procedures. While most of this information may be easy to get, information on stored procedures is often difficult and time consuming to retrieve. SQL diagnostic manager will save DBAs a lot of time in this respect. For future releases, I would like to see Idera show the dependencies within DTS packages and scheduled jobs.
Historical Database Analysis
Although DBAs rarely track it, historical database activity is critical to the success of your SQL Server enterprise. It allows you to get a better understanding of how SQL Server is used in your environment, understand where you may need to add or reduce capacity and in general, plan more effectively for future growth. SQL diagnostic manager will automatically gather a plethora of vital database activity statistics over time and generate easy-to-understand reports and graphs. The window below shows SQL Server activity, physical disk activity, and the network traffic of your database server. SQL diagnostic manager allows you to customize the view so that you can quickly see SQL Server database activity for any timeframe.
A good way to use this data is to view the transaction per minute. If it is decreasing over time on a specific database, the DBA may conclude that additional hardware resources are not needed. Conversely, if the transaction per minute is rising, then the DBA should consider purchasing additional hardware resources.
