Recently, my friend Mike Fal ( b | tÂ ) released a PowerShell script that can sample the counter inside SQL Server that one of my favorite databaseÂ benchmarking utilities, HammerDB, uses to produce the transactions per minute counter value that the benchmarking utility outputs.
I thought I’d counter with a means to perform this task directly within SQL Server, and store the results inside a database so that a mathematical analysis can be performed quite quickly and repeatably. I always like to have multiple methods to perform any given action, so that you can select the best tool for a given scenario.
As Mike notes, the best counter used to store this value is “Batch Requests/sec“, and is very straightforward to collect.Â To perform this automated collection, follow these easy steps.
Create a container database, or opt to use one of your utility databases.
Create a table to store the results. Use or adapt the following DDL for your uses.
|CREATE TABLE [dbo].[BatchReqPerSecLogger](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[BatchReqPerSec] [bigint] NULL,
[DT] [datetime] NULL CONSTRAINT [DF_BatchReqPerSecLogger_DT] DEFAULT (Â GETDATE() ),
CONSTRAINT [PK_BatchReqPerSecLogger] PRIMARY KEY CLUSTERED
Next, create a SQL Server Agent job that executes the following command against this new database every ten seconds.
|INSERTÂ INTO dbo.BatchReqPerSecLogger (BatchReqPerSec)
counter_name = ‘Batch Requests/sec’
Disable the job until you need it, and enable it when you begin your tests.
You can create a view on this data that gives you the insight into the database performance trends with a simple query. This query leverages statistics functions found in SQL Server 2012 and beyond. This view shows you the batch requests per minute numbersÂ whenÂ you estimate yourÂ batch requests per minute by sample period.
|CREATEÂ VIEW dbo.vBatchReqPerSec AS
ID, BatchReqPerSec,Â DT,
LAG(batchreqpersec) OVERÂ (ORDER BYÂ DT) AS PrevSample,
LAG(DT) OVERÂ (ORDER BYÂ DT) AS PrevSampleDT,
DATEDIFF(ms,LAG(DT) OVERÂ (ORDER BY dt),DT)/1000. AS SecDiff,
(batchreqpersec – LAG(batchreqpersec) OVERÂ (ORDER BY DT)) /
(DATEDIFF(ms,LAG(DT) OVERÂ (ORDER BY dt),DT)/1000.) as TPS,
((batchreqpersec – LAG(batchreqpersec) OVERÂ (ORDER BY DT)) /
(DATEDIFF(ms,LAG(DT) OVERÂ (ODER BYÂ DT),DT)/1000.)) * 60 ASÂ TPM
You can now query against this data based on the date and time ranges for your test, and perform whatever analysis you like!
DT >= CAST(‘2015-03-03Â 7:00’ ASÂ DATETIME)
ANDÂ DT < cast(‘2015-03-03Â 7:00’ ASÂ DATETIME)
Now you can quickly perform any sort of analytics that you want on the raw data. Just remember to turn off the Agent job when you are done.