SQL Server Reference Guide

I’m continuing my previous article on a method to create a charging system for SQL Server. In this article, I’ll explain a little more about the methodology to develop your own model for charging the cost of the system to the business units that use it. I’ll show you some fictional costs associated with a system, how SQL Server fits into it, and which tools and processes you can use to create your system.

The chargeback system is something you have to create. Any chargeback system depends on two things: the cost of implementing a project or service, and a measurement of the use of that system. Since no vendor can know those two things ahead of time, it’s almost impossible to sell a chargeback “system” — it’s something you’ll design. What the vendor can do is to instrument their software so that you can track its use.

In many ways, this is similar to what happens when you place your data space in the “cloud,” such as with the Amazon, Google or Microsoft solutions. All of these have a fee based on time of use, space, or some other metric. In effect, you’re looking to do the same thing with your business units. You are the “cloud” for your organization.

As I mentioned in the last article, the database is often a good place to begin when you’re creating your chargeback system. Databases are central to an application, and Microsoft SQL Server has a lot of metrics you can capture, from time on the system to individual process’ use of resources such as memory, CPU, storage (I/O) and to a lesser extent, network traffic. And you can use the tools to “tie” each component’s use back to a user or application, assuming they are logging in individually.

All of these models can be mixed and matched — you might decide to track one application with a certain method, and another with a mix of two or three of them.

Project and Operation Cost Estimating

The first area to research to develop your plan is to work with your business to find out how they currently estimate costs for charging the business units for the resources they use. There are three methods many companies use for this purpose, with many more variations based on each company’s situation. They include:

• Overhead
• Fixed Rate
• Standard Component or Resource Rate (SRR)

I’ll explain each of these methods briefly, since they have a direct bearing on the process you’ll choose to charge for your system.

In any case, the argument for using the data tier as a charge vector should not be confused with a charge for the database system alone. The database exists to serve an application, and an application has costs involving more than just the database server or servers. Those costs include, but are not limited to:

• Hardware Cost (including initial purchase and maintenance)
• Software Licenses and Maintenance
• Personnel
• Power
• Other infrastructure requirements (extended security or fire suppression systems)
• High Availability
• Disaster Response

All of these costs, for all components within the system, should be considered. You may decide to charge for the database alone as part of a larger system, or amortize the costs across database use.

I’ll explain each of the methodologies I’ve mentioned, and then show a few examples of how you can instrument them.

Overhead

The simplest, and probably the most widely used method of charging the cost of a system back to users is called the “overhead” method. To create this method, you take the cost of the hardware, software and other “fixed” costs and add them to the cost of running the system such as personnel, license renewals and maintenance, power, rack space rental and so on. This is called the “fully burdened” cost. You then simply charge that amount back to the department or business unit, normally amortized over a period of time.

Charging Model for the Overhead Model:

The billing for this model is quite simple. Taking all of the fixed costs, such as initial hardware purchases and so on, and add that to the variable costs, such as the staff time to implement and manage the system, the licensing and maintenance costs and so on multiplied by twelve months, or if you have calculated the variable costs on a yearly basis, by one. Divide that by the lifetime of the system (the time when you think you’ll replace it) and you have a yearly charge number. Divide all that by twelve to get a monthly charge for the system, or by whatever period you charge the department per year.

(Fixed Costs) + (Variable Costs per month * 12) / Life of the System in Years

Example:

In this example, I’ve paid 100,000 dollars for all the hardware and software I need for a department’s new application. I estimate that my staff time and the monthly software maintenance add up to about 2,500 dollars. That gives me a yearly cost of 26,000 dollars, and based on experience I’ll replace this system in about five years.

This gives me a figure to work with — about twenty-one hundred dollars a month charged to the business unit.

Use the Overhead Chargeback Model When:

• The system is used by a single application, or a single group of users
• The fixed and variable costs are well known
• The life of the system is relatively simple to predict
• You want a simple fee structure for the application
• You do not want to monitor the system for a fee structure, or the organization cannot agree on what that fee structure should be

Fixed Rate (Time)

The next method of chargeback is to use the time on the system. This is often a good choice, because it is fairly simple to track the time the user spends in an application, and there is only one variable to deal with from the user’s perspective. It can be a problem because if the estimate is off, the department can pay a high price for the application, often outweighing its value to the organization.

Once you have your costs, you simply track the use back to the department, either by user name, application name, or if the database is single use for that department, the database name. This is the method used by several “cloud” database vendors.

The difficulty in using this model is that many modern applications use a middle layer or data tier. The application and not the users log in to the database, so you can’t always tell who is logged in to track their time. Adding to this difficulty, many applications do not set the application name in their connection string, so you can’t track the application use that way either. Also, some applications make single calls back to the database and then cache the data locally, so you don’t get an accurate per user or per call that you need for the billing number.

Charging Model for the Fixed Rate Model:

To create this method for your organization, you’ll need to develop your fixed and variable costs once again along with the lifetime of the system, but you’ll add in two more factors. You need to know how much the time the system is used, and by how many users. That gives you a 100% time rate — in other words, the amount you would charge if someone used the system 100% of the time. That doesn’t happen, so you’ll need to factor in a value to make that use an average.

( (Fixed Costs) + (Variable Costs per month * 12) / Life of the System in Years) / Average Estimated Use in Hours Per Month, Per User

Example:

In this example, I’ve paid 100,000 dollars for all the hardware and software I need for a department’s new application. I estimate that my staff time and the monthly software maintenance add up to about 2,500 dollars. That gives me a yearly cost of 26,000 dollars, and based on experience I’ll replace this system in about five years. I have 50 users on the system, and they are on the system about six hours a day each.

This gives me a figure of around thirty six cents per hour, per user.

Use the Fixed Rate Chargeback Model When:

• The system is used by a multiple applications, or a multiple groups of users
• The fixed and variable costs are well known
• The life of the system is relatively simple to predict
• You want a simple fee structure for the application
• You are able to predict the usage rate of the system
• The users log directly in to the database using their own names, or a defined application name

Tools to Measure Use for the Fixed Rate Chargeback Model:

It’s relatively simple to track usage for the time-based charge of system use, at least at the database layer. The simplest method is to architect the cost right into the application. The application simply sets the logon time when the user connects, and then tracks and records the time.

Of course, this isn’t always possible. Perhaps you have a vendor-supplied application that does not record user logins, or the application you’ve developed doesn’t have that feature.

There are several tools you can use to track user time on the database, including:

• SQL Server Profiler and SQL Trace (SQL Server 2000 and higher)
• Session Dynamic Management Views (SQL Server 2005 and higher)
• The Data Collector and the Management Data Warehouse (SQL Server 2008 and higher)
• Extended Events (SQL Server 2008 and higher)
• SQL Audit (SQL Server 2008 and higher)
• The Resource Governor (SQL Server 2008 and higher)

For this example, I’ll show you a trace example that collects the logoff SQL Trace event, which has elapsed time in it. For more information on SQL Trace, check out my article here.

This example tracks more than just time, and you can use it for the next example as well:

/* Solution One - Monitoring Logins and Logouts using SQL Trace*/

/* Find a database to work with */
SELECT name
, database_id
FROM sys.databases
ORDER BY name;
GO

/* Set Up the Variables Needed for the trace */
DECLARE @traceHandle INT; 
DECLARE @traceID INT;
DECLARE @maxFileSize bigint; 
DECLARE @traceFile NVARCHAR(128); 
DECLARE @onBit bit;
/* Set values on those variables */ 
SET @maxFileSize = 5; 
SET @traceFile = 'C:\temp\ChargeBack'; 
SET @onBit = 1; 
/* Create a trace - more here:
http://msdn.microsoft.com/en-us/library/ms191443.aspx
 */
EXEC @traceHandle = sp_trace_create @traceID OUTPUT 
, 0
, @traceFile
, @maxFileSize
, NULL;

/* Fill it up with the data - more here:
http://msdn.microsoft.com/en-us/library/ms186265.aspx
and here: http://msdn.microsoft.com/en-us/library/ms190762.aspx
*/

EXEC sp_trace_setevent @traceID, 15, 10, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 9, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 18, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 3, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 35, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 13, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 15, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 27, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 51, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 8, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 60, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 11, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 41, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 7, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 6, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 16, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 49, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 26, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 64, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 12, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 14, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 23, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 17, @onBit; 
EXEC sp_trace_setevent @traceID, 15, 66, @onBit; 

/* Set a filter for the database to only include databaseId from above, I'll use 8 */ 
EXEC sp_trace_setfilter @traceID, 3, 1, 0, 8;

/* Start the trace */
EXEC sp_trace_setstatus @traceID, 1;
/* Show the trace id */
SELECT @traceID;

/* Work is done in another query */
USE AdventureWorks;
GO
SELECT * FROM Person.Contact;
GO

/* Stop the trace */ 
EXEC sp_trace_setstatus 2, 0
/* Close the trace */ 
EXEC sp_trace_setstatus 2, 2

 /* Read the data */ 
SELECT DatabaseID	
, NTUserName	
, NTDomainName	
, HostName	
, ClientProcessID	
, ApplicationName	
, LoginName	
, SPID	
, Duration	
, StartTime	
, EndTime	
, Reads	
, Writes	
, CPU	
, SessionLoginName	
FROM ::fn_trace_gettable('C:\temp\TestTrace.trc', DEFAULT)
WHERE CPU IS NOT NULL;

Standard Resource Rate

You may not be able to use (or just don’t want to use) either of the methods I’ve just described. Perhaps you want to add in a new SAN, and you need to have the departments bear the cost, or you want another granular method to measure one or more components in the system, such as CPU, memory or I/O transfers or size. But there are a couple of things to consider when you’re tracking at a granular level.

First, prior to SQL Server version 2008, there is no easy way to “Event” the database activities down to a granular level. Metrics are based on watching the system actively and reacting to counters after the event occurs. For instance, you aren’t easily able to trigger an event based on a CPU or Memory condition.

Using a “reactionary” model is normally acceptable in a chargeback system, since you’re not trying to prevent an activity, but merely to track it. The issue with a reaction-based system is when to monitor, and how often. For example:

A user logs in to a system that is monitored every five minutes, checking the logins, CPU and Memory. The user logs out four minutes later, is off for a minute or so, and then back in for four minutes.

In this case, the granularity of monitoring, even though it is aggressive (5 minutes), might not catch the user in the system, and never charge them. So it’s best to use methods that are event based, rather than reactionary. This is difficult in versions earlier than

Charging Model for the Standard Resource Rate Model:

To create this method for your organization, you’ll need to develop your fixed and variable costs once again along with the lifetime of the system, but you’ll add in two more factors.

The next step is the most difficult. You need to arrive at a number that divides in to the total cost of the system for the components you monitor. The easiest way to do this is to estimate when the component becomes overwhelmed, and you have to add another. Add all these together and average them for each transaction in the database, and you get a “transaction” cost, which is similar to the time charge from the last method.

The more components you want to charge for, the more complicated this process.

( (Fixed Costs) + (Variable Costs per month * 12) / Life of the System in Years) / Average Estimated Use in Hours Per Unit, Averaged Per Transaction

There’s another option here, and that’s charging different rates for the various components. In other words, you might charge one number for a CPU cycle, another for a gigabyte of space, and another for an I/O transfer or network packet. I have found that this level of detail is only needed when there is little correlation between the components for an “average” transaction, so I keep it as simple as possible.

Example:

In this example, I’ve paid 100,000 dollars for all the hardware and software I need for a department’s new application. I estimate that my staff time and the monthly software maintenance add up to about 2,500 dollars. That gives me a yearly cost of 26,000 dollars, and based on experience I’ll replace this system in about five years. I have 50 users on the system, and they are on the system about six hours a day each.

This gives me a figure of around fourteen cents per transaction.

Use the Standard Resource Rate Chargeback Model When:

• The system is used by a multiple applications, or a multiple groups of users
• The fixed and variable costs are well known
• The life of the system is relatively simple to predict
• You want an accurate, fine grained, detailed fee structure for the application
• You are able to predict the usage rate for each measured component of the system
• The users log directly in to the database using their own names, or a defined application name

Tools to Measure Use for the Fixed Rate Chargeback Model:

There are several tools you can use to track users by resource in the database, assuming you’re able to tolerate a reactionary model in versions earlier than SQL Server 2008:

• SQL Server Profiler and SQL Trace (SQL Server 2000 and higher)
• Session Dynamic Management Views (SQL Server 2005 and higher)
• The Data Collector and the Management Data Warehouse (SQL Server 2008 and higher)
• Extended Events (SQL Server 2008 and higher)
• SQL Audit (SQL Server 2008 and higher)
• The Resource Governor (SQL Server 2008 and higher)

You can use the same SQL Trace code I showed you in the last example, but for this example I’ll show you two possible tools. The first is the new Resource Governor Feature in SQL Server version 2008. I’ll show you in a second application, called SQL Audit, and both of these examples use a more powerful feature called Extended Events.

I won’t cover the Extended Events feature here because it is quite complex, so I’ll save that for another article. This feature will allow you the finest grain on the components, and is a true event-based system. I will point you to another article that covers this feature in depth in the References section below.

The Resource Governor (available in SQL Server 2008) allows you to set limits on CPU and memory use for a group of users based on a connection variable, such as a login name or application name. In this example, I’ll use the application name (MyApp) not to limit use, but merely to track it. There’s more about the Resource Governor here. I stated by right-clicking the Resource Governor object in the Management node of SQL Server Management Studio and selecting Enable from the menu that appeared. From there, I ran this script:

/* Turn on Resource Governor in SQL Server Management Studio
http://msdn.microsoft.com/en-us/library/bb933866.aspx
*/

/* Create a resource pool for the CPU and Memory */
CREATE RESOURCE POOL [TrackingMyApp] WITH(min_cpu_percent=0, 
		max_cpu_percent=100, 
		min_memory_percent=0, 
		max_memory_percent=100);
GO

/* Create Classifier Function to trap users, and then tie it to the pool above */
ALTER FUNCTION dbo.fn_ClassifyApps() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @ret sysname
	IF (APP_NAME() LIKE '%MyApp%')
		SET @ret = 'TrackingMyAppGroup'
		RETURN @ret
END

GO

/* Turn the function on */
ALTER RESOURCE GOVERNOR
	WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifyApps)

ALTER RESOURCE GOVERNOR RECONFIGURE

/* Find out who you are */
SELECT APP_NAME()

/* Run a query from that app */
WHILE 1 = 1
BEGIN
  SELECT * from AdventureWorks.Production.Product
END 

/* Watch the results */
SELECT * 
FROM sys.dm_resource_governor_workload_groups

SELECT * 
FROM sys.dm_resource_governor_resource_pools

Of course, if you need to track more than CPU and Memory, you’ll need to use another tool. The next example uses the SQL Server 2008 SQL Audit feature, which can track more than just CPU and memory — it can even track things like SELECT and INSERT statements. To keep this example compact, I’ll just track logins and logouts, but there are many more groups and items you can track with this feature – read here for more.

/* Audit Specification 
http://msdn.microsoft.com/en-us/library/cc280386.aspx
*/
/* Create an audit log, pointed at the Windows Event Application Logs */
CREATE SERVER AUDIT ChargeBack_Audit
  TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = SHUTDOWN);

/* Create an audit that sends login and logout information to the log */
CREATE SERVER AUDIT SPECIFICATION ChargeBack_Audit_Specification
FOR SERVER AUDIT ChargeBack_Audit
  ADD (SUCCESSFUL_LOGIN_GROUP)
  , ADD (LOGOUT_GROUP);
GO

/* Enable the audit. */
ALTER SERVER AUDIT ChargeBack_Audit
WITH (STATE = ON);
GO

/* Log in, do some work, log out. 
Check the Windows Application Log for information. Can use PowerShell to read this and filter it. */

/* List all server audit specifications with details of 
the audited action groups, server audits and audit files */
SELECT sp.name AS ServerAuditSpecification, 
   CASE WHEN sp.is_state_enabled =1 
      THEN 'Y' ELSE 'N' END AS SpecificationEnabled,       
   d.AuditActions, 
   a.name AS ServerAudit, 
   a.type_desc AS ServerAuditType, 
   CASE WHEN a.is_state_enabled =1 
      THEN 'Y' ELSE 'N' END AS AuditEnabled, 
   st.status_desc AS AuditStatus, 
   a.queue_delay AS QueueDelay, 
   a.on_failure_desc AS OnFailure, 
   st.audit_file_path AS CurrentFile, 
   f.max_file_size AS MaxFileSize, 
   f.max_rollover_files AS MaxRolloverFiles, 
   CASE WHEN f.reserve_disk_space = 0 THEN 'N' 
      WHEN f.reserve_disk_space = 1 THEN 'Y' END AS ReserveSpace 
FROM sys.server_audit_specifications AS sp 
JOIN sys.server_audits a 
  ON sp.audit_guid = a.audit_guid 
JOIN sys.dm_server_audit_status st 
  ON a.audit_id = st.audit_id 
LEFT JOIN sys.server_file_audits f 
  ON a.audit_id = f.audit_id 
LEFT JOIN (SELECT server_specification_id, 
      STUFF((SELECT ',' + audit_action_name AS [text()] 
         FROM sys.server_audit_specification_details AS d2 
         WHERE d2.server_specification_id = d1.server_specification_id 
         ORDER BY audit_action_name 
         FOR xml path('')), 1, 1, '') AS AuditActions 
      FROM sys.server_audit_specification_details AS d1 
      GROUP BY server_specification_id) AS d 
  ON sp.server_specification_id = d.server_specification_id 
ORDER BY ServerAuditSpecification

In this series of articles, I’ve explained a few of the concepts around creating a chargeback system in SQL Server. I’ve explained possible costing models, and shown you some tools and code you can use to starting tracking system use in your own environment. The references section that follows has more references you can use to study further.

InformIT Articles and Sample Chapters

Rob Hawthorne has an excellent book excerpt called Using SQL Profiler to Find Errors

Books and eBooks

Ken Henderson is one of the best writers in the SQL Server area. His book on troubleshooting, SQL Server 2005 Practical Troubleshooting: The Database Engine, has good descriptions of most of these tools. (Also available in downloadable eBook format and in Safari Books Online)

Online Resources

Here’s a reference to Microsoft’s documentation on auditing in SQL Server 2008. Lots of other tools are explained there — a useful read even if you’re still using SQL Server 2005.

Here’s a reference to Microsoft’s explanation of SQL Server 2008’s Extended Events feature.

Here is a link to Paul Randal’s excellent article on Extended Events — puts it all together.