Reading SQL Server DMVs for live resource cost

This page shows the exact Python needed to read SQL Server’s dynamic management views — sys.dm_exec_requests joined to sys.dm_exec_sql_text, sys.dm_exec_query_stats for cumulative CPU and logical reads, and Azure SQL’s sys.dm_db_resource_stats for DTU/vCore percentages — and turn live session consumption into a per-tenant cost signal.

Back to: System View Querying Patterns

SQL Server exposes no price anywhere in its DMVs; it exposes CPU time, logical and physical reads, and — on Azure SQL — the percentage of the instance’s provisioned DTU or vCore ceiling being consumed right now. Attributing spend means reading those signals live and mapping them onto whoever caused them. sys.dm_exec_requests is the live-session anchor, the SQL Server counterpart to Postgres’s snapshot view covered in extracting pg_stat_activity for cost tracking; sys.dm_exec_query_stats carries cumulative per-plan counters that must be differenced like the cumulative counters in querying Oracle V$SESSION for resource usage. The catch is that DMVs are memory-resident and observer-sensitive: a careless SELECT * with a plan-text cross-apply on every poll competes for the same scheduler you are trying to measure. The pattern below reads a scoped, low-overhead result, maps the program_name/login to a tenant, and prices the consumption into the same canonical usage record the rest of the aggregation tier expects.

Prerequisites

Before running the extractor, confirm the following are in place.

  • Database permissions: reading server-scoped DMVs on a box-product SQL Server needs VIEW SERVER STATE; on Azure SQL Database, the database-scoped VIEW DATABASE STATE is enough. Grant only that — never sysadmin. This least-privilege posture is part of broader access control for cost data.

    -- Box product / Managed Instance: server-scoped DMVs (dm_exec_requests, dm_exec_query_stats)
CREATE LOGIN finops_ro WITH PASSWORD = '<rotated-secret>';
CREATE USER  finops_ro FOR LOGIN finops_ro;
GRANT VIEW SERVER STATE TO finops_ro;

-- Azure SQL Database: database-scoped state is sufficient for dm_db_resource_stats
-- CREATE USER finops_ro WITH PASSWORD = '<rotated-secret>';
-- GRANT VIEW DATABASE STATE TO finops_ro;

  • Python: 3.10 or newer (the code uses modern asyncio and typing syntax).

  • Driver: the Microsoft ODBC Driver 18 for SQL Server, plus the async aioodbc wrapper and its synchronous pyodbc base.

    pip install "aioodbc>=0.5" "pyodbc>=5.1"
# and the system ODBC driver, e.g. msodbcsql18

Step-by-Step Implementation

The extractor runs a scoped join of the live-request and per-plan DMVs, differences the cumulative plan counters against a baseline with reset detection, reads the Azure SQL resource-stats view for the instance-level ceiling, and prices each tenant’s consumption. Build it in four steps.

Step 1 — Read live requests without hammering the scheduler

sys.dm_exec_requests lists every executing request; sys.dm_exec_sql_text resolves a plan handle to its query text but is a relatively expensive cross-apply, so pull it only for active user sessions and never for the poller itself. Filter server-side to session_id > 50 (user range), exclude your own @@SPID, and drop sleeping sessions so the payload is small and the observer effect stays low.

LIVE_REQUEST_QUERY = """
    SELECT
        r.session_id,
        s.login_name,
        s.program_name,
        r.cpu_time            AS cpu_ms,        -- milliseconds, this request
        r.logical_reads       AS logical_reads, -- 8 KB pages
        r.reads               AS physical_reads,
        r.writes              AS writes,
        SUBSTRING(t.text, 1, 200) AS query_head
    FROM sys.dm_exec_requests AS r
    JOIN sys.dm_exec_sessions AS s
      ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    WHERE s.is_user_process = 1
      AND r.session_id <> @@SPID          -- never bill the poller for observing
      AND r.status IN ('running', 'runnable', 'suspended');
"""

# Expected row shape (session_id, login_name, program_name, cpu_ms, logical, physical, writes, query_head):
#   (128, 'svc_acme', 'acme-api', 4820, 183422, 517, 12, 'SELECT o.* FROM orders o ...')

Mapping to a tenant keys off program_name (set by the application’s connection string) or login_name. Setting a deliberate Application Name=acme-api in each tenant’s connection string is the SQL Server equivalent of the app.cost_center GUC used on the Postgres side — it makes attribution explicit rather than guessed.

Step 2 — Extract asynchronously with a synchronous fallback

Production polling must survive transient network drops and failovers without losing a billing window. The extractor uses aioodbc for non-blocking I/O with exponential backoff and falls back to a synchronous pyodbc query on the final attempt — the same resilience posture applied in error handling in cost pipelines, here guarding a live DMV read.

import asyncio
import logging
import os
from typing import Any

import aioodbc
import pyodbc

logging.basicConfig(level=logging.INFO,
                    format="%(asctime)s | %(levelname)s | %(message)s")
logger = logging.getLogger("mssql_cost_extractor")


async def fetch_async(dsn: str) -> list[dict[str, Any]]:
    """Run the scoped live-request query over an aioodbc connection."""
    async with aioodbc.connect(dsn=dsn, timeout=10) as conn:
        async with conn.cursor() as cur:
            await cur.execute(LIVE_REQUEST_QUERY)
            cols = [d[0] for d in cur.description]
            rows = await cur.fetchall()
            return [dict(zip(cols, row)) for row in rows]


def fetch_sync(dsn: str) -> list[dict[str, Any]]:
    """Synchronous pyodbc fallback for a degraded async environment."""
    with pyodbc.connect(dsn, timeout=10) as conn:
        cur = conn.cursor()
        cur.execute(LIVE_REQUEST_QUERY)
        cols = [d[0] for d in cur.description]
        return [dict(zip(cols, row)) for row in cur.fetchall()]


async def extract_with_retry(dsn: str, max_retries: int = 3,
                             base_delay: float = 1.0) -> list[dict[str, Any]]:
    """Async extraction with backoff, then a sync driver fallback."""
    for attempt in range(max_retries):
        try:
            records = await fetch_async(dsn)
            logger.info("Async extraction succeeded: %d active requests", len(records))
            return records
        except (pyodbc.OperationalError, asyncio.TimeoutError) as exc:
            logger.warning("Async attempt %d/%d failed: %s", attempt + 1, max_retries, exc)
            if attempt == max_retries - 1:
                logger.info("Falling back to synchronous pyodbc extraction")
                return fetch_sync(dsn)
            await asyncio.sleep(base_delay * (2 ** attempt))  # 1s, 2s, 4s ...
    return []

The diagram below traces the extraction path from the live-request read through delta and resource-stats correlation to the priced per-tenant charge.

SQL Server DMV extraction flow: join live requests to sessions and query stats, delta vs baseline, correlate resource stats, price, attributesys.dm_exec_requests is the live anchor. It joins sys.dm_exec_sessions on session_id for tenant identity and sys.dm_exec_query_stats on plan_handle for cumulative CPU and logical reads. The joined snapshot is differenced against a baseline with resets clamped to zero, correlated with the Azure SQL dm_db_resource_stats ceiling, priced into cost units, and attributed to a tenant and quota.on session_idon plan_handle− baseline× ceiling× ratesJoined live snapshotdm_exec_requestslive requestsession_iddm_exec_sessionslogin + program namedm_exec_query_statscumulative CPU + readsDelta vs baselineclamp resets → 0dm_db_resource_statsDTU / vCore ceiling(Azure SQL)Attributetenant + quota

Step 3 — Difference cumulative counters and correlate the ceiling

Two counter shapes coexist. dm_exec_requests.cpu_time is per current request and resets when the request ends; dm_exec_query_stats accumulates per compiled plan and only climbs until the plan is evicted from cache. Difference the cumulative per-plan counters against a baseline, clamp resets to zero exactly as the Oracle pattern does, then scale the raw consumption against the instance ceiling that Azure SQL reports in dm_db_resource_stats so a tenant’s cost reflects its share of a bounded resource.

RESOURCE_STATS_QUERY = """
    SELECT TOP 1 avg_cpu_percent, avg_data_io_percent, avg_log_write_percent
    FROM sys.dm_db_resource_stats
    ORDER BY end_time DESC;      -- most recent 15-second Azure SQL sample
"""


def compute_deltas(current: list[dict], baseline: dict) -> tuple[list[dict], dict]:
    """Difference cumulative per-session counters, clamping resets to 0."""
    events, next_baseline = [], {}
    for r in current:
        key = r["session_id"]
        prev = baseline.get(key, {})
        cpu_d = max(0, r["cpu_ms"] - prev.get("cpu_ms", 0))          # reset -> negative -> 0
        lr_d = max(0, r["logical_reads"] - prev.get("logical_reads", 0))
        pr_d = max(0, r["physical_reads"] - prev.get("physical_reads", 0))
        events.append({
            "session_id": key,
            "program_name": r.get("program_name") or "untagged",
            "cpu_ms": cpu_d, "logical_reads": lr_d, "physical_reads": pr_d,
        })
        next_baseline[key] = {
            "cpu_ms": r["cpu_ms"], "logical_reads": r["logical_reads"],
            "physical_reads": r["physical_reads"],
        }
    return events, next_baseline

Step 4 — Price the delta into a tenant cost signal

Each primitive gets a rate; a session’s interval cost is their weighted sum, optionally scaled by how close the instance sits to its DTU/vCore ceiling so a saturated instance’s minutes are priced as the scarce resource they are. Logical reads are counted in 8 KB pages, so convert before pricing.

from datetime import datetime, timezone

# Illustrative normalized compute units — calibrate against your Azure SQL / RDS invoice.
RATES = {"cpu_s": 0.0006, "logical_gb": 0.02, "physical_k": 0.004}
PAGE_BYTES = 8192


def price(event: dict, ceiling_factor: float = 1.0) -> dict:
    """Attach cost_units scaled by the instance ceiling utilization."""
    logical_gb = event["logical_reads"] * PAGE_BYTES / 1_073_741_824
    units = (
        event["cpu_ms"] / 1000 * RATES["cpu_s"]           # per CPU-second
        + logical_gb * RATES["logical_gb"]                # per GiB read from cache/disk
        + event["physical_reads"] / 1000 * RATES["physical_k"]
    ) * ceiling_factor
    event["cost_units"] = round(units, 6)
    event["timestamp"] = datetime.now(timezone.utc).isoformat()
    return event


async def poll_once(dsn: str, baseline: dict) -> tuple[dict[str, float], dict]:
    """One interval: extract, delta, price, and roll up to per-tenant charges."""
    rows = await extract_with_retry(dsn)
    events, baseline = compute_deltas(rows, baseline)
    # A ceiling_factor > 1 makes minutes on a saturated instance cost more.
    charges: dict[str, float] = {}
    for e in (price(ev) for ev in events):
        tag = e["program_name"]
        charges[tag] = charges.get(tag, 0.0) + e["cost_units"]
    return charges, baseline


async def main() -> None:
    dsn = os.environ["MSSQL_DSN"]  # e.g. "Driver={ODBC Driver 18 for SQL Server};Server=...;"
    _, baseline = await poll_once(dsn, {})      # first poll baselines every session
    await asyncio.sleep(30)
    charges, baseline = await poll_once(dsn, baseline)
    logger.info("Interval charges by tenant: %s", charges)


if __name__ == "__main__":
    asyncio.run(main())

The first poll always yields zero-cost events because every session is being baselined; steady-state costs appear from the second poll onward — the same property as the Oracle cumulative-counter pattern.

Expected output on the second interval:

Interval charges by tenant: {'acme-api': 0.0184, 'globex-batch': 0.0091, 'untagged': 0.0007}

Verification

Confirm the delta and pricing logic before the extractor guards real quotas.

  1. Exercise the reset clamp without touching SQL Server. A two-sample fixture where the counter drops must bill zero, not negative.

    first  = [{"session_id": 128, "program_name": "acme-api",
           "cpu_ms": 4820, "logical_reads": 183422, "physical_reads": 517}]
second = [{"session_id": 128, "program_name": "acme-api",
           "cpu_ms": 500,  "logical_reads": 10000,  "physical_reads": 12}]  # plan evicted

events, baseline = compute_deltas(first, {})
assert events[0]["cpu_ms"] == 4820, "first sample baselines from zero"
events, _ = compute_deltas(second, baseline)
e = price(events[0])
assert e["cpu_ms"] == 0 and e["cost_units"] == 0.0, "a reset must never bill negative"

  2. Cross-check the live count against the engine. The extractor’s row count should match a direct query for active user requests, minus the poller:

    SELECT COUNT(*) FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID;

  3. Inspect one priced event. A healthy steady-state event is shaped like {"session_id": 128, "program_name": "acme-api", "cpu_ms": 320, "logical_reads": 4210, "physical_reads": 63, "cost_units": 0.00042, "timestamp": "..."} — feed it straight into strict schema validation for billing data before it reaches the ledger.

Gotchas & Edge Cases

  • cpu_time is milliseconds, not microseconds. dm_exec_requests.cpu_time and dm_exec_query_stats.total_worker_time differ in unit — the former is milliseconds, the latter microseconds. Mixing them silently scales cost by 1,000x. Pick one source per primitive and convert explicitly, the same unit-trap that catches Oracle’s centisecond-versus-microsecond CPU stats.
  • logical_reads counts 8 KB pages, not bytes. Every read counter in the DMVs is in pages. Multiply by 8192 before converting to GB or you under-report I/O cost by four orders of magnitude.
  • The plan cache evicts, resetting dm_exec_query_stats. A plan aged out under memory pressure disappears and its counters vanish; when it recompiles they restart from zero. The max(0, ...) clamp handles the negative delta, but a heavily-churning cache means you lose some consumption between polls — tighten the interval only enough to catch it, since the DMV read itself is not free.
  • Azure SQL vs box product changes the permission and the DTU view. dm_db_resource_stats exists only on Azure SQL Database and returns 15-second samples of DTU/vCore utilization; on a box product there is no equivalent, so drop the ceiling_factor and price raw consumption. Server-scoped DMVs also need VIEW SERVER STATE on the box product but only VIEW DATABASE STATE on Azure SQL.
  • program_name is client-supplied and spoofable. Attribution keyed on Application Name is only as trustworthy as the connection strings that set it. For hard tenant isolation, prefer per-tenant login_names and treat program_name as a secondary hint — a blank one must fall into an explicit untagged bucket so cost never silently disappears.
  • sys.dm_exec_sql_text is not free. The cross-apply that resolves plan text touches the plan cache on every row. Keep it out of the hot path for pure cost attribution (you rarely need the query text to bill CPU), or truncate it hard as the query above does, so the observer effect on the system view querying stays negligible.

Frequently Asked Questions

Which DMV should anchor cost attribution — dm_exec_requests or dm_exec_query_stats?

Use both for different questions. dm_exec_requests is the live, per-session view that tells you who is running something right now and carries the identity columns (session_id, login_name, program_name) you attribute to. dm_exec_query_stats is cumulative per compiled plan and tells you what has cost the most over the plan’s cache lifetime, ideal for finding expensive statements but blind to the current caller. Anchor attribution on requests; use query_stats to difference cumulative CPU and reads for sessions running long enough to span polls.

How is this different from reading pg_stat_activity or V$SESSION?

The identity anchor and counter shape differ. pg_stat_activity is a pure point-in-time snapshot with no cumulative counters, so cost is integrated by sampling active duration. Oracle’s V$SESSION companions are cumulative, so cost is a clamped delta. SQL Server sits between them: dm_exec_requests is snapshot-like for the current request, while dm_exec_query_stats is cumulative per plan and must be differenced. The canonical usage record all three produce is identical — only the extraction shape changes.

Do I need sysadmin to read these DMVs?

No, and you should not use it. VIEW SERVER STATE on a box product or Managed Instance, or VIEW DATABASE STATE on Azure SQL Database, grants read access to the execution DMVs with none of sysadmin’s blast radius. Assign it to a dedicated read-only login whose credential rotates through your secrets manager, never a personal or administrative account.

How does dm_db_resource_stats improve the cost signal?

It anchors raw consumption to a bounded ceiling. On Azure SQL, an instance is provisioned for a fixed DTU or vCore limit, and dm_db_resource_stats reports what percentage of that limit is being used in 15-second samples. Scaling a tenant’s consumption by how close the instance sits to saturation prices scarce minutes higher than idle ones, which reflects the real economics of a capacity-bounded database far better than raw CPU-seconds alone.

How do these charges feed quota enforcement?

The per-tenant charges are additive across intervals, so summing them over a rolling window gives running spend to compare against a policy threshold. When a tenant’s accumulated cost crosses its limit, that aggregate triggers the alert or throttle defined in database quota boundary design. Keeping attribution idempotent per session_id and interval is what stops a retried poll from double-counting and firing a false breach.

Back to: System View Querying Patterns

Microsoft’s reference for the execution-related DMVs is the SQL Server dynamic management views documentation, and the async driver used here is documented in the aioodbc project.