Chargeback

Overview

On a shared server, the question from finance is which database, and which team, should pay for it? CPU by Database, I/O by Database and Databases by Size each answer part of that, but only since the last restart or right now, not over a billing period.

The Chargeback report answers it for the period you pick (a month, a quarter or a custom range):

  • each database’s share of CPU, I/O, storage and buffer pool over the period,
  • a weighted share that combines them (40% CPU, 20% I/O, 30% storage, 10% memory unless you change it), and the cost that share of the server comes to,
  • a treemap of the cost by database or by cost center,
  • owner and cost center tags for each database, saved in DBHealthHistory,
  • the shared overhead of the system databases, shown on its own or spread over the others.

The Estate view lists every instance connected in the server tree over the same period, each with its own cost settings, and exports to Excel with an estate summary and one sheet per instance.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → Chargeback
Instance reports navigator Over time group
Related Links bar From CPU by Database and Workload by Application
Report arrows Previous is Bottleneck Mix by Database, next is Configuration Values

Where the usage comes from

Historic Monitoring collects each database’s usage once an hour into the instance’s own DBHealthHistory database (table ChargebackUsage, added in history version 1602). The procedure trackChargeback runs from trackEvery15Minutes (the DatabaseHealthRunEvery15Minutes job) and stores the hour that ended at least five minutes before:

Resource Where it comes from
CPU The CPU each database’s sessions used in that hour, from the Workload by Application history (WorkloadByAppHistory, collected every two minutes by DatabaseHealthRunEvery2Minutes)
I/O Bytes read and written to the database’s files since the previous run, from sys.dm_io_virtual_file_stats. A restart, a new database or a counter that went down is handled so nothing is counted twice; a gap of more than 26 hours only takes a new starting point
Storage The size of all of the database’s files at the time, data and log
Buffer pool The database’s pages in the buffer pool at the time

The history is kept for at least 400 days by the history cleanup, or longer if HistoricRetentionDays is set higher (0 keeps everything). If there is no history yet, the page says what to enable.

Picking the period

The toolbar picks Month (this month and the twelve before), Quarter (this quarter and the four before) or Custom (any range of days in that window). The usage is read once, added up by day, so changing the period, the settings or a tag recalculates without querying the server again. A period that includes today is costed up to now and marked so far.

Cost settings

Click Cost Settings… to enter what the server costs. The settings are saved in the instance’s DBHealthHistory (Settings table), so everyone who opens the report sees the same numbers.

Server cost, split by weights. Enter the server’s monthly cost and how much of it CPU, I/O, storage and memory drive (40/20/30/10 by default). A database’s cost is:

cost = monthly cost x months in the period x
       (CPU weight x CPU share + I/O weight x I/O share
        + storage weight x storage share + memory weight x buffer pool share) / sum of weights

For example, with a monthly cost of 1,000, two databases where one used 75% of the CPU and everything else is split evenly: 0.40 x 0.75 + 0.20 x 0.50 + 0.30 x 0.50 + 0.10 x 0.50 = 60%, so one is charged 600 and the other 400.

Rates for each resource. Enter a price per core per month, per GB of storage per month, per GB of buffer pool per month and per TB of I/O. A database is charged its CPU share of the cores, its average storage and buffer pool in GB, and its I/O in TB, each at its rate. The core count is the server’s own unless you enter one.

Also in the settings: the currency symbol or code, and whether the shared overhead is spread.

Reading the page

The banner gives the period’s total cost and how many databases it was split between, how many of the period’s hours were collected, any resource that was not collected at all, the databases with partial data, and the shared overhead.

The tiles repeat the figures: period cost, the largest database, the database count, the shared overhead, the coverage, and the CPU and I/O of all databases.

The treemap sizes each database (or, with By Cost Center, each cost center) by its cost, or by its weighted share when no cost has been entered. Pale tiles are databases with partial data, gray is the shared overhead. Click a tile to find it in the grid.

The grid lists each database: owner, cost center, CPU and I/O share, average storage and buffer pool in GB, weighted share, allocated cost, the shared overhead spread onto it, total cost, and notes.

Owners and cost centers

Right-click a database (or double-click it) and choose Set owner and cost center…. The tag is saved in DBHealthHistory.dbo.ChargebackOwner. By Cost Center on the toolbar groups the costs by cost center; untagged databases are (untagged).

How the numbers are worked out

  • A resource’s share is the database’s usage over the period divided by every database’s usage.
  • Storage and buffer pool are averaged over every hour the server was collected, so a database that existed for only part of the period (created or dropped during it) is prorated. It is marked partial data.
  • A resource nobody used in the period (for example CPU, when the Workload by Application history is not being collected) gives its weight to the others, so the whole cost is still split. The banner says so.
  • The shares come from the hours collected; the whole period’s cost is still split by them. The banner shows how many hours of the period were collected.
  • The months in a period are counted by calendar month, so a whole February is 1 month and a quarter is 3.
  • master, model, msdb, tempdb, the resource database and DBHealthHistory (and the hidden model_msdb and model_replicatedmaster on SQL Server 2022 and later) are shared overhead. With spreading on, their cost is charged to the other databases in proportion to their own cost, so the charges still add up to the total.
  • Times are the server’s own clock.

Exporting

Export to Excel writes a workbook. On the instance view: a Summary sheet (server, period, cost model, total, hours collected), a sheet with every database, and a Cost centers sheet when any database is tagged. On the Estate view: an Estate summary sheet and one sheet per instance.

Permissions and versions

  • The report reads DBHealthHistory.dbo.ChargebackUsage, ChargebackOwner and Settings on the instance. If DBHealthHistory is missing, cannot be opened by your login, or is older than version 1602, the page says which.
  • Saving the cost settings or a tag needs INSERT and UPDATE on those tables; if the save fails, the settings still apply to your visit and the page says why they were not saved.
  • The collection needs SQL Server 2008 or later. Reading the buffer pool needs VIEW SERVER STATE; the collection runs as the SQL Server Agent job’s owner.