When we think about the cost of the query we typically think about the cost which SQL Server assigns to execute the query. However when we look at performance tuning queries there is another cost that we need to think about. This additional cost is the cost to the company while the query is running. As we start looking into these costs we can start to see just how important it is to performance tune every query within our applications.
Say we have a query which takes 0.5 seconds to run. We might think that is pretty good and we don’t need to do any more performance tuning on it. Now lets assume that this query is the customer lookup query that every one of our customer service agents uses every time that a customer calls into the call center. As a large company we have on average 1500 customer service reps on the phone at any time (working three shifts), and they each take 10-15 calls per hour. That query is now being run 18000 times per hour (give or take). Assuming that our customer service reps make $10 per hour it is costing us ~9000 seconds per shift or ~7.5 hours of staff time per day. That’s a cost of $75 per day just to run this specific query. Multiply this out for a year and that’s ~$27,375 per year, just to watch the query run. By tuning the query so that it runs within 0.1 seconds we can cut these costs down to ~$5,475 per year. That is a decent amount of financial savings to the company for just one query.
As we start looking at the rest of the queries in the application and we start adding up the costs for those queries suddenly we can easily be looking at $100,000 a year or more is employee costs just to use the application.
Wouldn’t it be nice to be able to tell management that you’ve just saved the company $100,000 a year just by adding a couple of new queries to the database?
This is why we need to do performance tuning.
Denny
