Monday, January 27, 2014

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
a. RAM
b. CPU
c. Hard Disk
d. Disk level
e. Physical server or virtual server

Gather information on various network bandwidth/latency/limitations

Gather the exiting SQL Server Maintenance Plans
a. Backup Strategy 
b. Index Rebuild
c. Index Reorganise
d. 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.

1 comment: