SQL Server Performance Tuning 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 is the quick list of the pointers which one should consider for performance tuning.
Note: This is running draft for the tips to be kept at single place. Please send me email at pinal ‘at’ SQLAuthority.com if think I have missed anything and I will append it.
Index Analysis and Extremely Effective Index Created
Indexes are considered valuable for performance improvements. Analyze all your indexes to identify non-performing indexes and missing indexes that can improve performance.
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 for Performance
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. The goal is to reduce I/O performance bottlenecks and set 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 and optimize your database server.
TempDB Space Review
TempDB is the most important database in performance tuning and it should be reviewed to tune your database overall performance.
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.
DBCC Best Practices Implementations
Every DBA must know the best practices for DBCC commands. 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 one must practice with great caution.
