SQL Server DBA Checklist
Backups – The backup needs to be done in a way that meets RPO/RTO/SLA objectives, but it will vary by system.
Index Maintenance – Your indexes need to be checked frequently, most likely daily based. However, you may not want to rebuild indexes every night; it depends on the system and the length of time needed to rebuild.
Update Statistics – Statistics ideally need to be checked daily. These checks depend on the system, the length of time to update stats, the amount of churn within the database objects (like tables and indexes), and the queries that will be forced to recompile the next time they are run.
Corruption Checks – Should make checking for corruption a top priority, at least once a month.
Capture Configuration Details – For both the database and the server, you want to grab details of the configuration settings daily and track when changes are made.
SQL Server Health Check Checklist
- Isolate top server waits since SQL instance start
- I/O latencies for all drives
- SQL Server Backup Health Check
- Check for Available Disk Space
- Free memory available for SQL Server
- Check the size of the transaction log
- Check for Index Fragmentation
- Write and read stalls at each DB file level
- Are there any I/O warnings?
- Which DB consumes most CPU, RAM, and I/O
- How heavy is temp-DB usage?
- Review server waits for 30-60min (ideally during production workload)
- How well do parallelism settings match workload?
- Look into the SQL Error log, is there anything unusual?
- Is deadlocking happening?