General Approach To SQL Server Performance Tuning
General Approach to Performance Tuning
Examine the Nature of Software/Application which uses the SQL Server.
Gather the average number of users/sessions during peak hours/non-peak hours.
Gather/Review the existing hardware
- Hard Disk
- Disk level
- Physical server or virtual server
Gather information on various network bandwidth/latency/limitations
Gather the exiting SQL Server Maintenance Plans
- Backup Strategy
- Index Rebuild
- Index Reorganise
- Update Statistics
Gather top expensive queries/long running queries over period of time
Gather all missing/unused indexes
Get the database design, so as to check whether the queries coming in are dynamic or Ad Hoc queries or using stored procedures.
Once all this is done
Tune the Software/Application by increasing the heap memory/code modification, avoid ad hoc queries, use stored procedure etc.
Try if you can upgrade the existing hardware to maximum level affordable
Build a clean backup strategy so as to have full backup outside business hours
Use Perfmon to capture DISK IO, Index Information, Page Latency, User connections, Blocked process, lock waits, memory, CPU etc
Review and rebuild the existing maintenance plans to achieve more performance
Check for blockings
Review the expensive queries, study the query plan and improve the same
RUN Database Tuning Advisor on Poor performance/long running/Expensive queries, apply the necessary changes, and do not go with all the things suggested.
At this moment of time you should be able to have fair amount of increase in performance and decision on further troubleshooting and performance tuning.