Two questions catch most broken loads before anyone opens a report: did the table change when it should have, and by about the usual amount? Here’s how to answer both in T-SQL, and where hand-built monitoring runs out.
Row counts
sys.partitions holds a row count per partition. Index 0 is a heap and index 1 a clustered index, so summing those gives each table’s count without a scan:
SELECT s.name AS schema_name,
t.name AS table_name,
SUM(p.rows) AS row_count
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY row_count DESC;Microsoft documents sys.partitions row counts as approximate. They’re right for trend monitoring. Use COUNT(*) where you need the exact figure.
Freshness
The sturdy signal is a column your load writes, such as LoadedAt:
SELECT MAX(LoadedAt) AS newest_row,
DATEDIFF(MINUTE, MAX(LoadedAt), SYSUTCDATETIME()) AS minutes_old
FROM dbo.FactSales;Without one, sys.dm_db_index_usage_stats records the last user update per index:
SELECT OBJECT_SCHEMA_NAME(us.object_id) AS schema_name,
OBJECT_NAME(us.object_id) AS table_name,
MAX(us.last_user_update) AS last_write
FROM sys.dm_db_index_usage_stats AS us
WHERE us.database_id = DB_ID()
GROUP BY us.object_id;Two catches: its counters reset when SQL Server restarts, so “no update” can mean “restarted,” and reading it needs server-state permission, which many DBAs won’t grant a monitoring login.
History and alerts
A count means nothing alone. Log it daily from a SQL Server Agent job:
CREATE SCHEMA dq;
GO
CREATE TABLE dq.row_count_history (
captured_at datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME(),
schema_name sysname NOT NULL,
table_name sysname NOT NULL,
row_count bigint NOT NULL
);
GO
INSERT INTO dq.row_count_history (schema_name, table_name, row_count)
SELECT s.name, t.name, SUM(p.rows)
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.partitions AS p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY s.name, t.name;Then flag tables whose latest count moved more than 20% from the average of the previous seven captures:
WITH h AS (
SELECT schema_name, table_name, captured_at, row_count,
AVG(CAST(row_count AS float)) OVER (
PARTITION BY schema_name, table_name ORDER BY captured_at
ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS prior_avg,
ROW_NUMBER() OVER (
PARTITION BY schema_name, table_name ORDER BY captured_at DESC) AS rn
FROM dq.row_count_history
)
SELECT schema_name, table_name, row_count, prior_avg
FROM h
WHERE rn = 1 AND prior_avg > 0
AND ABS(row_count - prior_avg) / prior_avg > 0.20;This works. It also needs a write-enabled schema in the database, an Agent job per server, and a 20% threshold that’s wrong for half your tables: too tight for a staging table that’s truncated nightly, too loose for a dimension that should never shrink.
How DataOps TestGen monitors tables
TestGen’s monitors replace the history table and the threshold:
| Monitor | How it works |
|---|---|
| Freshness | Compares a fingerprint (hash) of key columns with the previous run and records the minutes since the last change; data profiling picks the columns |
| Volume | Records each run’s row count and predicts the expected range with a model trained over about 30 runs |
| Schema | Reports every added, dropped, or changed table and column |
| Metric | Tracks a SQL expression you write, such as daily revenue |
The docs suggest running monitors at roughly twice the rate your data changes. Alerts go to email, Slack, Microsoft Teams, or a webhook. TestGen needs only SELECT, stores history in its own database, and creates nothing in yours.
Open-source TestGen is free for one user, one connection, and one project. Start at the SQL Server install page, and see the SQL Server setup answer for the grants.