SQL Centre of Excellence

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 ?

Test Post on Azure Platform

by Bob Duffy 12. February 2013 15:12

We moved our blogs over to Windows Azure now as a Pilot.

Quite a nice experience so far…

My Upcoming Seminars

by Bob Duffy 8. February 2013 10:14

So far has been a busy start to the year. I have kicked off some new BI projects in the office, lots of performance tuning, capacity planning, upgrade projects, and hopefully some exciting announcements coming soon as we launch a new company. so has been a bit quiet on the blog front ;-)

I have a few upcoming seminars that might be of interest. One in Dublin and Two at the next SqlBits in Nottingham. I’m also hoping to make it over to some SqlSaturdays – maybe the one in Edinburgh and Denmark:

- http://sqlsaturdayedinburgh.com/

- http://www.sqlsaturday.com/196/eventhome.aspx


1. Optimising the SQL Platform for dot.net Solutions – Thurs 28/02/2013

We aim to offer guidance on how to design your Data Access Strategy to make the right choices for performance, and the how to monitor and tune a SQL workload by making common change in dot.net to improve performance and scalability. A focus is on the dot.net platform but many concepts are equally applicable to Java and other platforms.

First we will demonstrate design choices dot.net offers to interface with SQL Server when using ADO.Net and benchmark the impact on performance, then we will take a sample application running in a load test harness and show common performance worst practices, how to correct them and the impact on performance.

  • Module 1 - Dot.Net Data Access Fundamentals.
  • Module 2 - SQL/dot.net Performance Tuning Methodology and Tools.

2. SQL 2012 Always on Deep Dive Fri 03/05/2013

Always On Availability Groups offer a huge leap forward in terms of high availability. This sessions is a demo based introduction to the high availability changes in SQL 2012 emphasizing key features and benefits.

During the demonstration we will show how to seamlessly upgrade a database from SQL 2005+ to SQL 2012 with ZERO down time and then use the Availability Group features to simplify reporting, availability and disaster recovery.

This session will be useful for anyone who is working in a high availability environment or currently using replication to provide reporting and looking for a neater solution.

3. Windows Azure for SQL Folk Sat 04/04/2013

So what is this cloud stuff and how does it affect database professionals ?

In this session we aim to cover the fundamental concepts that you should know on the Cloud and Windows Azure. In this demo based hour we will step through building Azure applications, deploying and managing them and what storage choices you have when developing applications in the cloud.

This sessions is Ideally suited to anyone who wants to understand more behind how Azure works, what it offers and what is probably just hype.

Page List

Page List