When it comes to Performance Tuning, I have never come across DBA’s that have the same methods or step by step guide to performance tuning. The question I get asked all the time , where to begin.
There is no right or wrong answer. If you are a consultant and you get called in to a new environment, this is a Level 1 call, you don’t have the time to follow the full steps , you need a quick diagnostic short term solution, Most DBA’s would start with steps 4 -5
For Real Time solutions , most DBA’s would look at what is running now , and then try to resolve. One of the best ways of that is looking at your Waits in SQL Server. That would give you a clear indication of where the bottleneck is.
Most common top waits in SQL is related to Disk Subsystem. Before you start running perfmon and pulling out stats on your Disks presented to SQL, you need to ensure that your Data, Log, and Temp DB are on separate Disks. This can impact SQL performance.
SQL will tell you in Activity Manager, which data files have high IO Latencies. This can be related to index maintenance , missing indexes or poorly written queries.
Recently we had a performance issue on one on our core Applications. It is highly used, average 384 batch requests per second. Wait stats showed High IO Latencies and Latching.
Perfmon showed the Latencies are fine for the different drives. After further investigation, we found our normal maintenance plan which covered index maintenance was not sufficient for the increased high work load.
On another occasion , we did actually have a problem on the Disk Subsystems and the SAN vendors were called in to resolve.
If you no longer in crisis mode and want to get the best performance out of your SQL Server:
Operating System:
Must be patched with Service Packs. Ensure no errors.
SQL Infrastructure:
Data and Logs files are placed on separate disks with the correct raid levels.
Instance Configuration:
The defaults is generally fine. You may want to look at min & max memory settings. SQL will consume all the memory not leaving any for the operating system.
Maxdop is another option to look at. You can configure this to suit your environment, OLTP vs OLAP.
Default Index fill factor – default is 0. you can adjust to your environment.
Maintenance Plan:
Look at your index maintenance , update of stats , Integrity checks and backups
Blocking & Waits:
This will give you an indication of bottlenecks. A great tool to use.
See article by Paul Randall – Tell me where it hurts
Queries:
Look for long running queries , poor performing queries. Queries with missing indexes.
Databases:
Look at individual databases, tables, indexes, stored procedures and finally individual T-SQL queries.
Another Top SQL Server authority to follow is Pinal Dave – see his article on Performance Tuning