To simplify the road map for DBA's health check role, we present the outline of the task by the famous Five Ws as below:
- What is a database server health check?
- Why does a DBA need to perform the check?
- When should a DBA perform health check?
- Which tools should a DBA use to perform health check?
- Where to store information collected from the check?
- How to use (analyze) data collected in a check?
A database server health check is a routine performance assessment, audit or analysis on a collection of metadata metrics. For MS SQL Server, we may have two types of metrics:
- Windows performance monitor counters (real time & log data) related to server's resources.
- Dynamic management objects (DMO) (i.e. views and functions) for SQL server instance. There are server-scoped and database-scoped DMOs with different permissions.
The reason is straightforward, performing health check can help DBA ensure the performance, security, stability of the database. It will also be helpful before auditing, migration, upgrading, backup, etc.
3. When should a DBA perform health check?
Once an initial health check is done, it should be repeated regularly. Follow-up health check usually will be faster.
4. Which tools should a DBA use to perform health check?
First, a DBA needs an inventory list on server landscape questions as below:
- Where & what are the servers? What are their domains?
- How can you connect the servers? (SSMS, RDP mstsc)
- Version, build, service pack, etc.?
- Type of authentication (Windows or SQL)?
- Clustered? virtual or physical?
- Resources & State of Health: CPU, memory and disk I/O. Usage, up-time status, etc.
- Configuration: Server info, tempdb
- Query performance: Blocking, locking, and deadlocks.
- Activity Monitor
- Activity and performance reports from SSMS + Performance Dashboard Reports
- T-SQL, DMOs (system views, functions, stored procedures)
- SQL Trace/Profiler/X-Events
- Management Data Warehouse (MDW): 2008 + :
- SSMS > Database Server > Management> Data Collection
- System and event logs
- Performance Monitor (Control Panel > System & Security > Admin Tools)
- SQL Server Performance Monitor (Data Collector): setup ODBC first.
- Performance Analysis of Logs (PAL)
- SQL Server Best Practices Analyzer (2012)
5. Where to store information collected from the check? (To be continued)
6. How to use (analyze) data collected in a check? (To be continued)