Performing Usage-Based Costing

My company wants to charge application owners usage fees based on consumption of database resources, such as memory, CPU, I/O, the number of queries executed, and so on. Several third-party tools have this capability, but their data is based on sampling. We have many applications that run in less than 100ms, and a sampling approach misses much of this activity. Is there a comprehensive usage-based costing tool for SQL Server?

Questions about usage-based costing are becoming more frequent as SQL Server penetrates deeper into the enterprise environment. Robust usage-based costing tools are common in the mainframe world, but SQL Server 2000 doesn't have a comparative feature. Linchi Shea, a SQL Server MVP who has real-world experience dealing with usage-based costing in a high-end SQL Server environment, has some valuable insights I'd like to share with you.

SQL Server doesn't have a native accounting system that can provide basic, accurate, and persistent information you can use to calculate the charge-back amount, but there are many workarounds. Some techniques are cruder than others, but they all fall into one of two categories: those based on SQL Server Profiler and those based on sampling of master..sysprocesses. The trade-off is between using Profiler to get accurate, granular information but adversely affecting performance or getting less accurate data from sampling master..sysprocesses with minimal performance effects.

For implementing an internal charge-back system, sampling master..sysprocesses is a better solution than using Profiler, especially if you're more concerned with charging proportionally for the SQL Server resources an application uses rather than charging for the application's absolute usage of SQL Server's resources. When building a charge- back system, you need to address many details—some business oriented and some technology focused. For instance, you need to answer the following questions:

A good charge-back system is also a valuable performance-tuning tool. In distributed computing, developers commonly do whatever it takes to meet their release deadlines. Developers don't often worry about inefficient queries as long as the application works and no one complains too much about performance—even if they know that they could take steps to improve query performance. However, developers often improve their system's performance if they receive a huge monthly bill for resource usage, so a charge-back system can provide useful performance feedback into the development team.