SQL Server Performance Tuning

Thanks for visiting. If you’re here you and you need a performance issue solved — you’re in the right place. I’m confident I can get you up and running in a short period of time. Using proven real-world routines, I’ll put your database back on track.

Like any other technical issue, there are logical steps to follow for in diagnosing and performance tuning a system. Here’s an outline of the steps I follow:

• Prove it’s the database – I’ve seen months wasted in diagnosing the wrong system. So the first step is to prove it’s the database. This process of deduction is systematic and comes down to asking the question, “What is wrong?” Then we drill down on the dependencies one by one. If it gets to the database then we tune the database.
• Determine the physical bottleneck – This will be in the form of a smoking gun and is typically obvious if you know where to look. Most often it’s one of ten different indicators.
• Find the cause of the bottleneck – While this can be an easy process, I have seen many efforts go wrong at this point.
• Fix the biggest offenders – Once the suspected cause is found, the largest offenders are fixed to test the theory.
• Analyze performance – If performance has increased, fix the next round of top offenders.
• Iterate – Often times freeing one major bottleneck will cause another to appear. Remember all subsystems are interrelated. Sometimes freeing up Disk contention will reveal CPU contention. This is because they are highly interrelated. Understanding these relations and attacking the appropriate offenders is the key to performance tuning.

All Subsystems are Interrelated

One Vision

One of the biggest obstacles to successful performance tuning is that it often requires a single person with a good understanding of all the related subsystems to diagnose and drill down to the problem. Multiple experts of different specialties have a difficult time painting a complete picture without understanding all the interrelated dependencies. Databases are especially sensitive to this need because they are dependent on all aspects of the system including the network.

Below are some of the subsystems that can be involved in your performance bottleneck. Without an understanding of each of these systems and how they relate, you may not be able to find the solution to your problem:

OS Knowledge

• CPU
• Disk Configurations
• User mode memory
• Kernel mode memory
• Network
• Paging
• Process Trimming

SQL Engine

• Lock Escalation
• Transaction Isolation Levels
• Blocking
• Deadlocking
• Parallelism
• Recompilation
• Statistics
• Execution Plans
• Log Configurations
• Fragmentation
• Temp DB
• Batch Requests
• Buffer Pool
• Connection Settings
• Memory Pressure

N-Tier Troubleshooting

• Transport Protocols
• IIS
• Threading models
• Process Queuing / Blocking
• ODBC
• OLEDB
• Connection Pooling

Miss one of the items above and you could be left with a sub optimal tuning job or even worse seen no results at all.

---

Contact Me My experience includes scaling SQL Server to support sites that host over 8000 concurrent users. I can typically find bad performers within minutes without the need to run traces. I WILL fix your issue. Feel free to contact me [derek at sqlserverplanet.com]. I have over 11 years experience in optimizing SQL Server. I’d be happy to get you up and running.