Is there a way to find the transaction count on a sql table?

I need to find out the count of inserts, updates and deletes on a SQL tables in a given day or today? All counts together is fine too, if individual counts are not possible. Is there a way to find this stats information in SQL Server 2012?

You want to use the sys.dm_db_index_operational_stats DMF.

SELECT *
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(N'schema.table'), NULL, NULL);

There is a lot of very interesting and useful info in that DMF, but with respect to this particular question, the following columns should have what you are looking for:


leaf_insert_count -- bigint -- Cumulative count of leaf-level inserts.


leaf_delete_count -- bigint -- Cumulative count of leaf-level deletes. leaf_delete_count is only incremented for deleted records that are not marked as ghost first. For deleted records that are ghosted first, leaf_ghost_count is incremented instead.


leaf_update_count -- bigint -- Cumulative count of leaf-level updates.

It won't show history, so take a sample once per day. And like all DMVs and DMFs, it probably gets recent upon service restart.

StackExchange Database Administrators Q#152890, answer score: 2