Transactions/Sec and Batch Requests/Sec performance counter in SQL Server

PerformanceCounterPerformance counter is one of the biggest weapon system administrator and Database administrator can have. I have already written few things about performance counter in past article mentioned below:

  • Performance Counter in SQL Server (Click Here)
  • Performance Counter in Microsoft Windows Server 2008 (Click Here)
  • Repl. Pending Xacts performance counter and high value in SQL Server (Click Here)

As long as SQL Server concern, “Transactions/Sec” and “Batch Requests/Sec” are two of my very favorite performance counter and I used to keep my close eye on this in each server I manage. The main reason to keep eye on both these performance counter is to get an idea about how much work my SQL Server can perform per seconds. As a DBA it is mandatory to know this so that you can plan out capacity of server accurately.

“Transaction/Sec” tracks down all DML statements like Insert/Update/Delete along with SELECT statement executed between transaction whereas “Batch Requests/Sec” counter gives information about each batch sends to SQL Server no matter whether it is between transaction or not so, in my opinion, “Batch Requests/Sec” can gives you more idea about throughput being performed on server.

Sometime I also want to see which is the peak time for my database and it requires to have data captured for complete 24 hours for few weeks or even month so that we can aggregate data we have and find out peak period of day, peak day of week and so on.

Let us create environment to capture all these information by following the steps given:

Note: Please test script given here in your development/beta environment first.

Step 1:

We have to create one table in database which keeps all the captured data for our counters we have created.

[sourcecode language=”sql”]
–Step1
CREATE SCHEMA MONITOR
GO

CREATE TABLE [Monitor].[PerformanceCounterHistory](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [nvarchar](256) NULL,
[CounterType] VARCHAR(100),
[CounterName] VARCHAR(100),
[CounterValue] [bigint] NULL,
[CreatedDate] [datetime] NULL
)
GO
ALTER TABLE [Monitor].[PerformanceCounterHistory] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO
[/sourcecode]

Step 2:

Create one Stored Procedure which will insert data into “[Monitor].[PerformanceCounterHistory]” table we have created above.

[sourcecode language=”sql”]
–Step2
CREATE PROC [Monitor].[SPInsertPerfStats]
AS

INSERT INTO [Monitor].[PerformanceCounterHistory]
(
ServerName
,CounterType
,CounterName
,CounterValue
)
Select
@@ServerName
,instance_name
,counter_name
,cntr_value
FROM
sys.dm_os_performance_counters
WHERE
(counter_name = ‘Transactions/sec’ and instance_name = ‘_Total’)
OR counter_name like ‘%Batch Requests/sec%’
GO
[/sourcecode]

Step 3:

Keep calling Stored Procedure “Monitor.SPInsertPerfStats” at certain time period. I generally prefer to call this SP at every 30 seconds, if you are sure about your business peak timing, you can call this SP in that time period only otherwise keep it for complete 24 hours cycle. You can use SSIS package and schedule that package in Windows Schedule task or you can call this SP directly from SQL Server Job Agent.

Step 4:

Since we are calling this SP at every 30 seconds interval, we have to aggregate data for every seconds and store the aggregated data in another table. Do aggregate data every morning for previous day so that we can generate different kind of report from this aggregation itself.

[sourcecode language=”sql”]
–Step4
CREATE TABLE [Monitor].[PerfStatAggr](
[Seq] [int] IDENTITY(1,1) NOT NULL,
[CounterType] [nvarchar](256) NULL,
[ServerName] [nvarchar](256) NULL,
[CounterName] VARCHAR(100),
[CounterValue] [bigint] NULL,
[CreatedDate] [datetime] NULL,
[AggrValue] [numeric](18, 3) NULL
)
GO

CREATE PROCEDURE [Monitor].[SPInsertPerfStatAggr]
/*EXEC [Monitor].[SPInsertPerfStatAggr]
@Day = 0,  –1 is for previous day
@StartHour = 4, –start capturing of data from 4 AM previous day
@EndHour = 23 –capture data of previous day till 11 */
(
–generally I used to aggregate data of previous day always
–so kept logic of day. if you pass 1, it will aggregate data of previous day only
@Day int,
–I am interested to capture business hours data
–in our environment, we get maximum traffic in application From 8AM to 6PM, so I kept
–8 & 18. you can even aggregate data from 1 to 24, as per your requirement
@StartHour int, –8
@EndHour int –18
)
AS
BEGIN

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SET NOCOUNT ON

DECLARE @StartTime DATETIME , @EndTime DATETIME

SET @StartTime =  DATEADD(hh,@StartHour,DATEDIFF(DD,0,GETDATE()- @Day))

SET @EndTime =  DATEADD(hh,@EndHour,DATEDIFF(DD,0,GETDATE()- @Day))

;WITH ctePerfStat
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ServerName,CounterName ORDER BY CreatedDate) as Rn, *
FROM [Monitor].[PerformanceCounterHistory] PerfCount1 (nolock)
WHERE (CreatedDate between @StartTime and @EndTime)
)
INSERT INTO [Monitor].[PerfStatAggr]
(
ServerName,
CounterName,
CounterValue,
CreatedDate,
–following are the aggregated fields
–I am more interested in that only
AggrValue
)
SELECT
C1.ServerName,
C1.CounterName,
cast(c2.CounterValue as numeric(18,3)) as CounterValue,
C2.CreatedDate,
cast((c2.CounterValue – c1.CounterValue)as numeric(18,3))/DATEDIFF(SS,C1.CreatedDate,C2.CreatedDate) as AggrValue
FROM
ctePerfStat C2
inner join ctePerfStat C1
ON (C1.ServerName = C2.ServerName  AND C1.CounterName =C2.CounterName AND C1.rn-1=c2.Rn )
END
[/sourcecode]

Step 5:

Keep collecting and aggregating data for a week or so to capture the transaction pattern for your database/server. You can return the result of all these capture by following Stored Procedure.

[sourcecode language=”sql”]
–Step5
CREATE PROCEDURE [Monitor].[SPGetPerfState]
/*Declare @FromDate1 datetime
Declare @ToDate1 datetime
set @ToDate1 = DATEADD(DD,0,DATEDIFF(DD,0,getdate()))
Set @FromDate1 = DATEADD(DD,0,DATEDIFF(DD,0,getdate()))
Exec [Monitor].[SPGetPerfState] @Unit = ‘day’, @FromDate = @FromDate1, @ToDate = @ToDate1*/
–it depends of the report you require,
–if you pass day, it will return the data between From/To Date
–by getting MAX day wise
@Unit VARCHAR(10), –@Unit = ‘day’, — ‘Minute,Hour,Day,Month,Year’
@FromDate DATETIME,
@ToDate DATETIME
AS
BEGIN
DECLARE
@SQL nvarchar(4000)

SET @SQL = ‘
SELECT
CounterName,
DATEADD(DD,0,DATEDIFF(DD,0,CreatedDate)) as CreatedDate,
DATEPART(‘+@Unit+’,CreatedDate) as Unit,
CEILING(MAX(AggrValue)) as AggrValue
FROM [Monitor].[PerfStatAggr] as I with (Nolock)
where CreatedDate between ”’+cast(@FromDate as varchar)+”’ and ”’+cast(@ToDate as varchar)+”’
group by CounterName,DATEADD(DD,0,DATEDIFF(DD,0,CreatedDate)),datepart(‘+@Unit+’,CreatedDate)
order by Unit

–print @SQL
EXEC sp_executesql @SQL
END
GO

—-uncomment following SP to see the aggregated result
—-ofcourse, you have to change unit as per your need.
—-you can use Minute,Hour,Day,Month,Year etc.
—-and change the from/to date too
–EXEC [Monitor].[SPGetPerfState]
–@Unit=’HOUR’,
–@FromDate=’2012-12-05 06:00:00′,
–@ToDate=’2012-12-05 23:00:00′
[/sourcecode]

So, this is how you have to capture your transaction pattern for few days. There are few different ways to capture transactions but whenever there is a question of transaction pressure on the server where I have only SQL Server installed, this is my favorite way. We will talk about different way (from windows OS) of capturing transactions information sometime later, this is all for now…..

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Transactions/Sec and Batch Requests/Sec performance counter in SQL Server”

Comments are closed.