Home > Articles > Data > SQL Server

📄 Contents

  1. SQL Server Reference Guide
  2. Introduction
  3. SQL Server Reference Guide Overview
  4. Table of Contents
  5. Microsoft SQL Server Defined
  6. SQL Server Editions
  7. SQL Server Access
  8. Informit Articles and Sample Chapters
  9. Online Resources
  10. Microsoft SQL Server Features
  11. SQL Server Books Online
  12. Clustering Services
  13. Data Transformation Services (DTS) Overview
  14. Replication Services
  15. Database Mirroring
  16. Natural Language Processing (NLP)
  17. Analysis Services
  18. Microsot SQL Server Reporting Services
  19. XML Overview
  20. Notification Services for the DBA
  21. Full-Text Search
  22. SQL Server 2005 - Service Broker
  23. Using SQL Server as a Web Service
  24. SQL Server Encryption Options Overview
  25. SQL Server 2008 Overview
  26. SQL Server 2008 R2 Overview
  27. SQL Azure
  28. The Utility Control Point and Data Application Component, Part 1
  29. The Utility Control Point and Data Application Component, Part 2
  30. Microsoft SQL Server Administration
  31. The DBA Survival Guide: The 10 Minute SQL Server Overview
  32. Preparing (or Tuning) a Windows System for SQL Server, Part 1
  33. Preparing (or Tuning) a Windows System for SQL Server, Part 2
  34. Installing SQL Server
  35. Upgrading SQL Server
  36. SQL Server 2000 Management Tools
  37. SQL Server 2005 Management Tools
  38. SQL Server 2008 Management Tools
  39. SQL Azure Tools
  40. Automating Tasks with SQL Server Agent
  41. Run Operating System Commands in SQL Agent using PowerShell
  42. Automating Tasks Without SQL Server Agent
  43. Storage – SQL Server I/O
  44. Service Packs, Hotfixes and Cumulative Upgrades
  45. Tracking SQL Server Information with Error and Event Logs
  46. Change Management
  47. SQL Server Metadata, Part One
  48. SQL Server Meta-Data, Part Two
  49. Monitoring - SQL Server 2005 Dynamic Views and Functions
  50. Monitoring - Performance Monitor
  51. Unattended Performance Monitoring for SQL Server
  52. Monitoring - User-Defined Performance Counters
  53. Monitoring: SQL Server Activity Monitor
  54. SQL Server Instances
  55. DBCC Commands
  56. SQL Server and Mail
  57. Database Maintenance Checklist
  58. The Maintenance Wizard: SQL Server 2000 and Earlier
  59. The Maintenance Wizard: SQL Server 2005 (SP2) and Later
  60. The Web Assistant Wizard
  61. Creating Web Pages from SQL Server
  62. SQL Server Security
  63. Securing the SQL Server Platform, Part 1
  64. Securing the SQL Server Platform, Part 2
  65. SQL Server Security: Users and other Principals
  66. SQL Server Security – Roles
  67. SQL Server Security: Objects (Securables)
  68. Security: Using the Command Line
  69. SQL Server Security - Encrypting Connections
  70. SQL Server Security: Encrypting Data
  71. SQL Server Security Audit
  72. High Availability - SQL Server Clustering
  73. SQL Server Configuration, Part 1
  74. SQL Server Configuration, Part 2
  75. Database Configuration Options
  76. 32- vs 64-bit Computing for SQL Server
  77. SQL Server and Memory
  78. Performance Tuning: Introduction to Indexes
  79. Statistical Indexes
  80. Backup and Recovery
  81. Backup and Recovery Examples, Part One
  82. Backup and Recovery Examples, Part Two: Transferring Databases to Another System (Even Without Backups)
  83. SQL Profiler - Reverse Engineering An Application
  84. SQL Trace
  85. SQL Server Alerts
  86. Files and Filegroups
  87. Partitioning
  88. Full-Text Indexes
  89. Read-Only Data
  90. SQL Server Locks
  91. Monitoring Locking and Deadlocking
  92. Controlling Locks in SQL Server
  93. SQL Server Policy-Based Management, Part One
  94. SQL Server Policy-Based Management, Part Two
  95. SQL Server Policy-Based Management, Part Three
  96. Microsoft SQL Server Programming
  97. An Outline for Development
  98. Database
  99. Database Services
  100. Database Objects: Databases
  101. Database Objects: Tables
  102. Database Objects: Table Relationships
  103. Database Objects: Keys
  104. Database Objects: Constraints
  105. Database Objects: Data Types
  106. Database Objects: Views
  107. Database Objects: Stored Procedures
  108. Database Objects: Indexes
  109. Database Objects: User Defined Functions
  110. Database Objects: Triggers
  111. Database Design: Requirements, Entities, and Attributes
  112. Business Process Model Notation (BPMN) and the Data Professional
  113. Business Questions for Database Design, Part One
  114. Business Questions for Database Design, Part Two
  115. Database Design: Finalizing Requirements and Defining Relationships
  116. Database Design: Creating an Entity Relationship Diagram
  117. Database Design: The Logical ERD
  118. Database Design: Adjusting The Model
  119. Database Design: Normalizing the Model
  120. Creating The Physical Model
  121. Database Design: Changing Attributes to Columns
  122. Database Design: Creating The Physical Database
  123. Database Design Example: Curriculum Vitae
  124. NULLs
  125. The SQL Server Sample Databases
  126. The SQL Server Sample Databases: pubs
  127. The SQL Server Sample Databases: NorthWind
  128. The SQL Server Sample Databases: AdventureWorks
  129. The SQL Server Sample Databases: Adventureworks Derivatives
  130. UniversalDB: The Demo and Testing Database, Part 1
  131. UniversalDB: The Demo and Testing Database, Part 2
  132. UniversalDB: The Demo and Testing Database, Part 3
  133. UniversalDB: The Demo and Testing Database, Part 4
  134. Getting Started with Transact-SQL
  135. Transact-SQL: Data Definition Language (DDL) Basics
  136. Transact-SQL: Limiting Results
  137. Transact-SQL: More Operators
  138. Transact-SQL: Ordering and Aggregating Data
  139. Transact-SQL: Subqueries
  140. Transact-SQL: Joins
  141. Transact-SQL: Complex Joins - Building a View with Multiple JOINs
  142. Transact-SQL: Inserts, Updates, and Deletes
  143. An Introduction to the CLR in SQL Server 2005
  144. Design Elements Part 1: Programming Flow Overview, Code Format and Commenting your Code
  145. Design Elements Part 2: Controlling SQL's Scope
  146. Design Elements Part 3: Error Handling
  147. Design Elements Part 4: Variables
  148. Design Elements Part 5: Where Does The Code Live?
  149. Design Elements Part 6: Math Operators and Functions
  150. Design Elements Part 7: Statistical Functions
  151. Design Elements Part 8: Summarization Statistical Algorithms
  152. Design Elements Part 9:Representing Data with Statistical Algorithms
  153. Design Elements Part 10: Interpreting the Data—Regression
  154. Design Elements Part 11: String Manipulation
  155. Design Elements Part 12: Loops
  156. Design Elements Part 13: Recursion
  157. Design Elements Part 14: Arrays
  158. Design Elements Part 15: Event-Driven Programming Vs. Scheduled Processes
  159. Design Elements Part 16: Event-Driven Programming
  160. Design Elements Part 17: Program Flow
  161. Forming Queries Part 1: Design
  162. Forming Queries Part 2: Query Basics
  163. Forming Queries Part 3: Query Optimization
  164. Forming Queries Part 4: SET Options
  165. Forming Queries Part 5: Table Optimization Hints
  166. Using SQL Server Templates
  167. Transact-SQL Unit Testing
  168. Index Tuning Wizard
  169. Unicode and SQL Server
  170. SQL Server Development Tools
  171. The SQL Server Transact-SQL Debugger
  172. The Transact-SQL Debugger, Part 2
  173. Basic Troubleshooting for Transact-SQL Code
  174. An Introduction to Spatial Data in SQL Server 2008
  175. Performance Tuning
  176. Performance Tuning SQL Server: Tools and Processes
  177. Performance Tuning SQL Server: Tools Overview
  178. Creating a Performance Tuning Audit - Defining Components
  179. Creating a Performance Tuning Audit - Evaluation Part One
  180. Creating a Performance Tuning Audit - Evaluation Part Two
  181. Creating a Performance Tuning Audit - Interpretation
  182. Creating a Performance Tuning Audit - Developing an Action Plan
  183. Understanding SQL Server Query Plans
  184. Performance Tuning: Implementing Indexes
  185. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 1
  186. Performance Monitoring Tools: Windows 2008 (and Higher) Server Utilities, Part 2
  187. Performance Monitoring Tools: Windows System Monitor
  188. Performance Monitoring Tools: Logging with System Monitor
  189. Performance Monitoring Tools: User Defined Counters
  190. General Transact-SQL (T-SQL) Performance Tuning, Part 1
  191. General Transact-SQL (T-SQL) Performance Tuning, Part 2
  192. General Transact-SQL (T-SQL) Performance Tuning, Part 3
  193. Performance Monitoring Tools: An Introduction to SQL Profiler
  194. Performance Tuning: Introduction to Indexes
  195. Performance Monitoring Tools: SQL Server 2000 Index Tuning Wizard
  196. Performance Monitoring Tools: SQL Server 2005 Database Tuning Advisor
  197. Performance Monitoring Tools: SQL Server Management Studio Reports
  198. Performance Monitoring Tools: SQL Server 2008 Activity Monitor
  199. The SQL Server 2008 Management Data Warehouse and Data Collector
  200. Performance Monitoring Tools: Evaluating Wait States with PowerShell and Excel
  201. Practical Applications
  202. Choosing the Back End
  203. The DBA's Toolbox, Part 1
  204. The DBA's Toolbox, Part 2
  205. Scripting Solutions for SQL Server
  206. Building a SQL Server Lab
  207. Using Graphics Files with SQL Server
  208. Enterprise Resource Planning
  209. Customer Relationship Management (CRM)
  210. Building a Reporting Data Server
  211. Building a Database Documenter, Part 1
  212. Building a Database Documenter, Part 2
  213. Data Management Objects
  214. Data Management Objects: The Server Object
  215. Data Management Objects: Server Object Methods
  216. Data Management Objects: Collections and the Database Object
  217. Data Management Objects: Database Information
  218. Data Management Objects: Database Control
  219. Data Management Objects: Database Maintenance
  220. Data Management Objects: Logging the Process
  221. Data Management Objects: Running SQL Statements
  222. Data Management Objects: Multiple Row Returns
  223. Data Management Objects: Other Database Objects
  224. Data Management Objects: Security
  225. Data Management Objects: Scripting
  226. Powershell and SQL Server - Overview
  227. PowerShell and SQL Server - Objects and Providers
  228. Powershell and SQL Server - A Script Framework
  229. Powershell and SQL Server - Logging the Process
  230. Powershell and SQL Server - Reading a Control File
  231. Powershell and SQL Server - SQL Server Access
  232. Powershell and SQL Server - Web Pages from a SQL Query
  233. Powershell and SQL Server - Scrubbing the Event Logs
  234. SQL Server 2008 PowerShell Provider
  235. SQL Server I/O: Importing and Exporting Data
  236. SQL Server I/O: XML in Database Terms
  237. SQL Server I/O: Creating XML Output
  238. SQL Server I/O: Reading XML Documents
  239. SQL Server I/O: Using XML Control Mechanisms
  240. SQL Server I/O: Creating Hierarchies
  241. SQL Server I/O: Using HTTP with SQL Server XML
  242. SQL Server I/O: Using HTTP with SQL Server XML Templates
  243. SQL Server I/O: Remote Queries
  244. SQL Server I/O: Working with Text Files
  245. Using Microsoft SQL Server on Handheld Devices
  246. Front-Ends 101: Microsoft Access
  247. Comparing Two SQL Server Databases
  248. English Query - Part 1
  249. English Query - Part 2
  250. English Query - Part 3
  251. English Query - Part 4
  252. English Query - Part 5
  253. RSS Feeds from SQL Server
  254. Using SQL Server Agent to Monitor Backups
  255. Reporting Services - Creating a Maintenance Report
  256. SQL Server Chargeback Strategies, Part 1
  257. SQL Server Chargeback Strategies, Part 2
  258. SQL Server Replication Example
  259. Creating a Master Agent and Alert Server
  260. The SQL Server Central Management System: Definition
  261. The SQL Server Central Management System: Base Tables
  262. The SQL Server Central Management System: Execution of Server Information (Part 1)
  263. The SQL Server Central Management System: Execution of Server Information (Part 2)
  264. The SQL Server Central Management System: Collecting Performance Metrics
  265. The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
  266. The SQL Server Central Management System: Reporting the Data and Project Summary
  267. Time Tracking for SQL Server Operations
  268. Migrating Departmental Data Stores to SQL Server
  269. Migrating Departmental Data Stores to SQL Server: Model the System
  270. Migrating Departmental Data Stores to SQL Server: Model the System, Continued
  271. Migrating Departmental Data Stores to SQL Server: Decide on the Destination
  272. Migrating Departmental Data Stores to SQL Server: Design the ETL
  273. Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
  274. Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
  275. Tracking SQL Server Timed Events, Part 1
  276. Tracking SQL Server Timed Events, Part 2
  277. Patterns and Practices for the Data Professional
  278. Managing Vendor Databases
  279. Consolidation Options
  280. Connecting to a SQL Azure Database from Microsoft Access
  281. SharePoint 2007 and SQL Server, Part One
  282. SharePoint 2007 and SQL Server, Part Two
  283. SharePoint 2007 and SQL Server, Part Three
  284. Querying Multiple Data Sources from a Single Location (Distributed Queries)
  285. Importing and Exporting Data for SQL Azure
  286. Working on Distributed Teams
  287. Professional Development
  288. Becoming a DBA
  289. Certification
  290. DBA Levels
  291. Becoming a Data Professional
  292. SQL Server Professional Development Plan, Part 1
  293. SQL Server Professional Development Plan, Part 2
  294. SQL Server Professional Development Plan, Part 3
  295. Evaluating Technical Options
  296. System Sizing
  297. Creating a Disaster Recovery Plan
  298. Anatomy of a Disaster (Response Plan)
  299. Database Troubleshooting
  300. Conducting an Effective Code Review
  301. Developing an Exit Strategy
  302. Data Retention Strategy
  303. Keeping Your DBA/Developer Job in Troubled Times
  304. The SQL Server Runbook
  305. Creating and Maintaining a SQL Server Configuration History, Part 1
  306. Creating and Maintaining a SQL Server Configuration History, Part 2
  307. Creating an Application Profile, Part 1
  308. Creating an Application Profile, Part 2
  309. How to Attend a Technical Conference
  310. Tips for Maximizing Your IT Budget This Year
  311. The Importance of Blue-Sky Planning
  312. Application Architecture Assessments
  313. Transact-SQL Code Reviews, Part One
  314. Transact-SQL Code Reviews, Part Two
  315. Cloud Computing (Distributed Computing) Paradigms
  316. NoSQL for the SQL Server Professional, Part One
  317. NoSQL for the SQL Server Professional, Part Two
  318. Object-Role Modeling (ORM) for the Database Professional
  319. Business Intelligence
  320. BI Explained
  321. Developing a Data Dictionary
  322. BI Security
  323. Gathering BI Requirements
  324. Source System Extracts and Transforms
  325. ETL Mechanisms
  326. Business Intelligence Landscapes
  327. Business Intelligence Layouts and the Build or Buy Decision
  328. A Single Version of the Truth
  329. The Operational Data Store (ODS)
  330. Data Marts – Combining and Transforming Data
  331. Designing Data Elements
  332. The Enterprise Data Warehouse — Aggregations and the Star Schema
  333. On-Line Analytical Processing (OLAP)
  334. Data Mining
  335. Key Performance Indicators
  336. BI Presentation - Client Tools
  337. BI Presentation - Portals
  338. Implementing ETL - Introduction to SQL Server 2005 Integration Services
  339. Building a Business Intelligence Solution, Part 1
  340. Building a Business Intelligence Solution, Part 2
  341. Building a Business Intelligence Solution, Part 3
  342. Tips and Troubleshooting
  343. SQL Server and Microsoft Excel Integration
  344. Tips for the SQL Server Tools: SQL Server 2000
  345. Tips for the SQL Server Tools – SQL Server 2005
  346. Transaction Log Troubles
  347. SQL Server Connection Problems
  348. Orphaned Database Users
  349. Additional Resources
  350. Tools and Downloads
  351. Utilities (Free)
  352. Tool Review (Free): DBDesignerFork
  353. Aqua Data Studio
  354. Microsoft SQL Server Best Practices Analyzer
  355. Utilities (Cost)
  356. Quest Software's TOAD for SQL Server
  357. Quest Software's Spotlight on SQL Server
  358. SQL Server on Microsoft's Virtual PC
  359. Red Gate SQL Bundle
  360. Microsoft's Visio for Database Folks
  361. Quest Capacity Manager
  362. SQL Server Help
  363. Visual Studio Team Edition for Database Professionals
  364. Microsoft Assessment and Planning Solution Accelerator
  365. Aggregating Server Data from the MAPS Tool

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.

Overhead Model
Fixed Costs: Monthly Variable Costs Yearly Costs Monthly Costs
100,000.00 2,500.00 26,000.00 2,166.67

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.

Fixed Rate Model
Fixed Costs: Monthly Variable Costs Yearly Costs Monthly Costs Hours Per Month, All Users Hourly Charge
100,000.00 2,500.00 26,000.00 2,166.67 6000 .36

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.

Fixed Rate Model

Fixed Costs:

Monthly Variable Costs

Yearly Costs

Monthly Costs

Unit Estimates, All Units

Unit Charge, Cumulative

100,000.00

2,500.00

26,000.00

2,166.67

15,000

.14

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020