SQL Server Assessment
The SQL Server Assessment will provide your organization with the broadest insights of any IT assessment module.
Your SQL Server Assessment will consist of the following elements:
- Routine Health Report – This report assesses the health of the SQL Server using three major categories. These include settings, file, and resources. Setting health looks for configuration issues that may go against prescribed best practices. File health looks at how the database interacts with the file system, looking for adequate space and compares the current configuration versus best practices. Resource health looks to ensure adequate resources are available to operate the SQL Server optimally and looks for indicators pointing to performance issues. Resource health comprises of three sub-categories – wait health, task health, and memory health. Wait health deals with issues with database processing waits and delays. Task health validates that scheduled tasks and jobs are working optimally. Memory health looks to ensure adequate memory is available to run the SQL Server properly.
- SQL Server Detail Report – This report details the settings and health of the SQL Server as a whole. It looks at settings, configuration, performance, and backup. Information and detailed breakdown of databases can be found in the Database Detail report.
- Database Detail Report – This report details the settings and health of individual databases that reside on the scanned SQL Server. It lists the database properties, potentially missing indexes, locks, statistics, fragmentation, and existing indexes. Without this tool, it would be a daunting task to collect all this information. Because this report documents each database individually, it can be run ad-hoc when specific database performance problems arise. But best practice is not to wait and react to these problems but plan to run this report on a regular basis (quarterly or monthly, depending upon the how critical the application is). This report will help identify opportunities to improve performance and accumulate trending data that will help you anticipate problems before they occur.
- Maintenance Plan Report – This report details all maintenance plans and their sub-plans. Maintenance plans perform routine tasks on your SQL Server. Not all maintenance plans are active and in-use, and you can use the report to document what’s in place and if adequate automation of maintenance and backups are being performed.
- SQL Server Agent Jobs Report – This report details all jobs (active and inactive) for the SQL Server agent. Some jobs may be maintenance plans and can be seen in detail in the Maintenance Plan Detail report (see above). Look in the Job History section of this report for entries in RED or that do not say “success” and see what jobs are causing errors and why. This report is so simple to generate, even non-DBA tech can use it to check for errors in jobs. And since some Remote Monitoring and Management (RMM) tools do not delve into the actual database level, it makes sense to run this report monthly to supplement your RMM tool, and also to keep it “honest.”