Database Transaction count for a given date range

Question

I want to find out how many times a SQL Server database was hit on a given date range. Is there any query which I can use to get hourly transactions (db hits - Select/Insert/update/Delete) for a given date range?

E.g I want to find hourly transaction count on my db on the first of the month from 8 am to 6 pm

The output is expected to be something like

Range	TXN Count
8-9	xxxxxx
9-10	yyyyyy
.	.
17-18	ZZZZZ

Answer 1

You can use the master tables to have the number of connections and sessions Then, you just need to do a group by the column connect_time or login_time / user

Here you have all master dm exec types https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver15

SELECT * from master.sys.dm_exec_sessions
SELECT * from master.sys.dm_exec_connections

---Sample
SELECT 
    count(*) as numberConnections, 
    CAST(login_Time as date) as mydate, 
    login_name
from 
    master.sys.dm_exec_sessions
Group by
    CAST(login_Time as date), 
    login_name