SQL Server Performance Tuning and health check is a very challenging subject that requires expertise in Database Administration and Database Development. Here are few pointers how one can keep their SQL Server Performance Optimal. I am often asked what can one do keep SQL Server Health Optimal and SQL Server keep on running very smooth.
Here Some pointers which one should consider for performance tuning.
Server/Instance Level Configuration Check
Review all the SQL Server/Instance Level settings of the server and tune it based on system workload.
I/O distribution Analysis
Analyze the I/O of your system and decide the best distribution for the I/O load. Perform object level analysis and do performance tuning at table level. Reduce I/O performance bottlenecks and suggest optimal setting for read and write database. This is especially critical for databases that need to sustain heavy updates during peak usage hours.
SQL Server Resource Wait Stats Analysis
Wait Stat Analysis is very crucial for optimizing databases, but is often overlooked due to lack of understanding. Perform advanced resource wait statistics analysis to proactively reduce performance bottleneck.
Index Analysis
Indexes are considered valuable for performance improvements. Analyze all your indexes to identify non-performing indexes and missing indexes that can improve performance.
TempDB Space Review
Review the size and usage of your TempDB database.
Database Files (MDF, NDF) and Log File Inspection
Review all the files and filegroups of each of your databases and analysis them to identify any object or files that are causing bottlenecks.
Fragmentations and Defragmentations
Identify the optimal settings of your database files and tables to reduce fragmentation and reduce them.
Backup and Recovery health Check
Review all backup & recovery settings and procedures of your databases and understand the functions of the respective databases.
Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)
Logs reveal many hidden facts. Identity the critical errors and resolve them.
Hardware Review
Review the hardware and and verify that it positively impact the performance or scalability of the database.
DBCC Best Practices Implementations
There are few DBCC commands to be avoided and few very crucial for system. Understand the usage of DBCC FREEPROCCACHE, DBCC SRHINKDATABASE, DBCC SHRINKFILE, DBCC DROPCLEANBUFFER, DBCC REINDEX, as well as the usage of few system stored procedures like SP_UPDATESTATS. If you are currently using any of the above mentioned and a few other DBCC maintenance task commands carefully review their usage.
Deadlock Detection and Resolutions Hands-On
Detecting deadlock is not very difficult, but to learn the tips and trick to resolve them requires an understanding of the issue and some experience. Understand the locking mechanism and resolve them.