We do a LOT of performance tuning and troubleshooting work. My rates aren’t the cheapest in the book, but you pretty much know that when you call Prodata or I’m sure any good performance consultant things are going to improve.
Often, i walk in a room with 5-10 experienced professionals and they aren't making much headway to resolving issues, but things start to go forward when we get involved. Why is this ? well here are four common troubleshooting methodologies commonly used. See if you recognise them ;-)
1. The Six Shooter
Walk into the room like John Wayne listen a bit and start making suggestions. Try them and see if it works. This is the most common approach and the fastest. if your first bullet hits – problem solved. Ride onto the next ranch and go the pub the hero. The more experienced you are the better the chance of a good hit.
The problem comes when you are reloading your gun for the 10th time and are six hours later – do you even know what your shooting at ?
Do we use this at Prodata – heck yeah. Nothing better than walking in a room, taking a shot hitting the target and walking out. Just one simple rule – you get only one bullet for every 5 man years tuning experience (or children you have).
Often I’ll break up a session and say “ok – we’ve run out of bullets guys” – lets try another approach.
The six shooter can give you the fastest possible resolution, but it can also give you the longest, or never actually succeed in solving an issue.
2. The Shotgun
This is the approach we very commonly see in the field – usually when a project manager is running the show. They have a workshop, compile a huge list of ideas. Select the top 10 and then implement them all at once.
Not my favourite approach. The benefit is fewer iterations, but the down side is even if it does fix the problem does anyone actually know what the problem was ? What are you going to do if the same problem happens again ? fire the same shotgun blast ?
Do we use this at Prodata – not usually, but we do get involved in projects where we are not running the show and the PM style leads it this way. I’ll try to steer away from this if possible.
3 The Grenade (Or Nuke)
We don’t see this one much, out side of a political threat, or attempt to motivate a vendor or six shooter team into shooting faster. It may also be seen if there is a corporate terrorist in the midst of the project with an agenda.
The benefit of this one is it clears the field. Would I use this one ? Well maybe if we were taking over a half finished project and things are in bad shape. Do we troubleshoot and finish taking ownership of the nasty stuff, or hit the nuke button and rise from the ashes with a beautiful creation.
4. The Surgical Knife
This is closer to the sort of approach a tuning professional is going to take. Start with a high level health and configuration check (preferable using automated tools) and then move onto reproducing issues, measuring the problem, identifying where it hurts and fixing those areas in an iterative style.
The advantage of this approach is obviously that recommendations are based on observable evidence, so more likely to hit the pain points.
The disadvantage is often the “measure it” is a slow and expensive process time wise and it does need some experience to jump from the where does it hurt to the fix it. The health check may throw up some quick wins, but in SQL Server terms the workload analysis is going to need a trace file or some major delving into DMV’s, perfmon counters, etc. Maybe a harness will be needed.
One major blocker for this approach is the “reproduce it” step. What if the problem only happens on the production server at 3am and no one is allowed access to the server to instrument anything ?