Sql-server – Getting Batch Requests Per Second On a Particular Database

monitoringsql server

Does anyone know how to find Batch Requests/Sec on particular database (database specific, not server level)?

This is how to find Transactions/Sec on a particular database.

DECLARE @Counter bigint
SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND object_name='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name

-- Wait for 1 second

WAITFOR DELAY '00:00:01'

SELECT cntr_value = @Counter FROM sys.dm_os_performance_counters
WHERE counter_name ='Transactions/sec'
AND object_name ='SQLServer:Databases'
AND instance_name ='VirendraTest' -- Your DB Name

Best Answer

'Batch Requests/Sec' is a server-level counter, not available for individual database instances. Keep in mind that single batch can access multiple databases.

You can get an aggregated count of batches and RPCs executed per database with an XE trace with a histogram target like the below example. This will count only the context database where the batch was executed, not all databases touched by the batch.

CREATE EVENT SESSION [sql_batch_completed_by_database] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.sql_batch_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

CREATE EVENT SESSION [rpc_completed_by_database] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_name))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.rpc_completed',source=N'sqlserver.database_name')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO

--get snapshot of aggregated counts
WITH
      sql_batch_completed_by_database AS ( 
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet  
        JOIN sys.dm_xe_sessions AS xe  
            ON (xe.address = xet.event_session_address)  
        WHERE xe.name = 'sql_batch_completed_by_database'
    )
    , rpc_completed_by_database AS ( 
        SELECT CAST(xet.target_data AS xml) AS event_data
        FROM sys.dm_xe_session_targets AS xet  
        JOIN sys.dm_xe_sessions AS xe  
            ON (xe.address = xet.event_session_address)  
        WHERE xe.name = 'rpc_completed_by_database'
    )
    , requests_by_database AS (
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM sql_batch_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
        UNION ALL
        SELECT
              slot.value('(./value)[1]', 'sysname') AS DatabaseName
            , slot.value('@count', 'bigint') AS RequestCount
        FROM (SELECT event_data FROM rpc_completed_by_database) AS xe(event_data)
        CROSS APPLY event_data.nodes('/HistogramTarget/Slot') AS histogram_target(slot)
    )
SELECT DatabaseName, SUM(RequestCount) AS RequestCount
FROM requests_by_database
GROUP BY DatabaseName;
GO
Related Question