How to start SQL Performance Tuning

Question:  How do you start SQL Server tuning?  Do you start at the wait stats and dive into query details  or do you collect a workload via  SQL Server Profiler or Extended Events?

I’ve read Performance Tuning - Save hundreds of hours which has good information

Answer: Performance tuning starts at the server. Good server configuration is important.

Gather metrics at the server level , such as wait stats. Confirm the server is stable and well configured. Document baseline metrics.

Then , if necessary, start query tuning. I have two basic approaches

1) Quick and dirty – Identify the worst performers through query sys.dm_exec_query_stats

2) Slower but more accurate – Use extended events to capture queries and aggregate to identify the worst performers

Keep repeating the process

Read More

Storage Checklist FAQ - SQL Server DBA

SQL Server Performance Checklist - SQL Server DBA

TempDB performance and strategy checklist - SQL Server DBA