- 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.
We have to create one table in database which keeps all the captured data for our counters we have created.
--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
Create one Stored Procedure which will insert data into “[Monitor].[PerformanceCounterHistory]” table we have created above.
--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
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.
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.
--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
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.
--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'
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
Note: Microsoft Books online is a default reference of all articles .