Category Archives: Performance Counter

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.

--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

Step 2:

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

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.

--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

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.

--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

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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

Performance Counter in Microsoft Windows Server 2008

Performance Counter in Microsoft Windows Server 2008 :

As I have promised in my article “Performance Counter in SQL Server“, herewith, I am providing introduction about how to run Performance Counter from Microsoft Windows?

As a Database Administrator or System Administrator, you always have responsibility to monitor system performance, find bottleneck of performance which may be related to network, disk, memory, IIS, SQL Server and much more. Each admin have different responsibility to monitor different part of system, depends of the domain s/he working one. These monitoring responsibility can greatly  be done with help of Performance Counter as it cater the needs of DBA, System Admin, IIS Admin and much more.

You can create your own “Data Collector Set” in “Performance Counter” with the counters you need. You have variety of choices to select in form of counter. some of the important counters are as follows:

Processor counter

Memory counter

Logical Disk counter

Physical Disk counter

SQL Server Agent counter

.NET CLR Memory counter

.NET CLR Networking counter

ASP.NET counter

Database counter

Internet Information Services counter

Network Interface counter

There are just few of the counters which you can use. All of the above counter has its own sub counter category. For details information about few of the important counter can be found here.

By default, when you create your own “Data Collector Set”, it will save all information in .BLG file in binary format but you can change it to other user readable format like .CSV (Comma Separated Value).

Anyway, it is up-to your choice which counter to use. I have created step wise demo which generate one “Data Collector Set” for few counters related to Network, Logical Disk & Processor.

1.) Click on Start Menu->Run and type “PerfMon.msc” and click on “Ok” button.

Perfmon.msc

 

 

 

 

 

 

 

 

 

 

2.)  Right click on Performance Monitor to create your own new data collector set and click on “Next” button

Data Collector Set

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.) Give name to your Data Collector Set, for eg: “Disk_Processory_Memory” and click on “Next” button

Disk_Processory_Memory

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.) Give the path where you wanted to save your performance counter file and click on “Next” button

path   to save your performance counter file

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.)  select radio button of “Start this data collector set now” and click on “Finish”

Start data collector set now

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6.) Now, if you see in main window of Performance Counter, you can see your file is generated in binary format (.blg) so right click on it and click on “Property” from pop up menu.

.BLG (Binary format)

 

 

 

 

 

 

 

 

 

 

7.)  You will get pop up window which shows selected counter, you can add as much counter as your want after clicking on “Add” button from “System Monitor Log Property” popup window.

Select Performance Counter

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

8.) Once you are done with adding counter , click on “Ok” button so that you are returned back to “System Monitor Log Property” popup window.

Save counter data in Comma Separated File

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

9.) Select “Comma Separated” log format from “System Monitor Log Property” popup window as give in above image and click on “Apply” first and then “Ok”. Now right click on your data collector set and stop collecting data and start it again so that your data will be captured in CSV file rather then in BLG file.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You are done now. You can keep collecting data as much time as you want and have a look at .CSV file in the path you have given.

 

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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

 

Repl. Pending Xacts performance counter and high value in SQL Server

Repl. Pending Xacts performance counter and high value in SQL Server:

 

After publishing Performance Counter article yesterady, one of my regular blog reader come up with very interesting question. He don’t have replication enabled in his production server yet “Repl. Pending Xacts” performance counter was giving very high value (4543857846) to him. Since this is in billion, I have asked him to provide me with the result of Performance Counter  view I had in my article so he provided me with the following screen shot.

 

Note: If you have directly encountered this article, I would like to request you to please visit Performance Counter in SQL Server article to get the script which has raised this post. click here to visit that article.

Performance Counter Result

After confirming that the value he said is really there, I asked him to provide me further information about his environment by executing following query so that I can see which databases are causing this issue.

SELECT
RTRIM(INSTANCE_NAME) AS DBName,
RTRIM(COUNTER_NAME) AS CounterName,
RTRIM(CNTR_VALUE) AS CounterValue
FROM MASTER..SYSPERFINFO
WHERE COUNTER_NAME like '%xacts%'

After executing above given TSQL statement, he again provided me the following screen shot.

Repl. Pending Xacts bug

If you observe above given screen shot, you come to know that this problem is being initiated by MODEL and MASTER database. If I would have found any user databases, I would have investigated more to find whether replication is enabled intentionally or unintentionally but these are system database and can’t be replicated at all. This is bug of performance counter so you don’t need to worry about this one.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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

Performance Counter in SQL Server

Performance Counter in SQL Server

Performance Counter always helps DBA as well as system administrator to keep watch on performance of Operating System as well as SQL Server. There are thousands of performance counter offered by Windows but I mostly interested in some of the important performance counter related to SQL Server and hence I have created one view which I can use directly from SSMS or from one of web page for monitoring I have.

Keeping a watch on performance, especially in business hours , is one of the mandatory task for any DBA or system administrator. Herewith, I am sharing the TSQL script which can be helpful to any DBA or system administrator who wants to monitor performance of SQL Server & Database along with some of the crucial information about buffer & memory usage, lock & dead lock details and compilation-recompilation & caching.

You can run performance counter from GUI in Windows as well as from DMVs of SQL Server (Given in this article). Performance counter from GUI in windows give lot more counters then we are provided in DMV of SQL Server. Windows’ performance counter intend to keep an eye on every aspect of Windows not only SQL Server but DMV provided in SQL Server will have only those performance counter which are important and necessary to keep a watch on performance of SQL Server instance and its databases. I will cover Performance Counter from Windows GUI sometime later.

CREATE VIEW WhatIsPerformance
AS
-- Given counter in this query are most important as per my opinion
-- so gave status "Important" and sort order 1.....
SELECT
'Important' AS CounterType
,1 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
(
([counter_name] IN(
'active transactions',
'Average Wait Time (ms)',
'errors/sec',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Repl. Pending Xacts',
'Transactions/sec') and instance_name = '_total'
)
or
[counter_name] IN (
'User Connections',
'Number of Deadlocks/sec',
'Repl. Trans. Rate',
'Bytes Sent/sec',
'Bytes Received/sec',
'SQL SENDs/sec',
'SQL RECEIVEs/sec',
'Enqueued Messages/sec',
'Send I/Os/sec',
'Receive I/Os/sec',
'lock waits',
'Network IO waits',
'Active Temp Tables',
'Temp Tables Creation Rate',
'Logins/sec',
'Logouts/sec'
)
)
AND cntr_value > 0

UNION ALL

-- important memory statistics
SELECT
'Memory Stat' AS CounterType
,2 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Connection Memory (KB)')
OR ([counter_name] = 'Optimizer Memory (KB)')
OR ([counter_name] = 'SQL Cache Memory (KB)')
OR ([counter_name] = 'Granted Workspace Memory (KB)')
OR ([counter_name] = 'Maximum Workspace Memory (KB)')
OR ([counter_name] = 'Memory Grants Outstanding')
OR ([counter_name] = 'Memory Grants Pending')
OR ([counter_name] = 'Lock Memory (KB)')
OR ([counter_name] = 'Lock Blocks Allocated')
OR ([counter_name] = 'Lock Owner Blocks Allocated')
OR ([counter_name] = 'Lock Blocks')
OR ([counter_name] = 'Lock Owner Blocks')

UNION ALL

-- buffer statistics
SELECT
'Buffer Stat' AS CounterType
,3 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Buffer cache hit ratio')
OR ([counter_name] = 'Buffer cache hit ratio base')
OR ([counter_name] = 'Page lookups/sec')
OR ([counter_name] = 'Readahead pages/sec')
OR ([counter_name] = 'Page reads/sec')
OR ([counter_name] = 'Page writes/sec')
OR ([counter_name] = 'Page life expectancy')

UNION ALL

-- total lock statistics
SELECT
'Lock Stat' AS CounterType
,4 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Lock Requests/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Timeouts/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Number of Deadlocks/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Waits/sec' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Wait Time (ms)' and [instance_name] ='_Total')
OR ([counter_name] = 'Average Wait Time (ms)' and [instance_name] ='_Total')
OR ([counter_name] = 'Average Wait Time Base' and [instance_name] ='_Total')
OR ([counter_name] = 'Lock Timeouts (timeout > 0)/sec' and [instance_name] ='_Total')

UNION ALL

-- compilation and cache stat
SELECT
'compilation and cache stat' AS CounterType
,5 AS SortOrder
,Object_Name AS CounterObject
,Counter_Name AS CounterName
,Cntr_Value AS CounterValue
,Instance_Name AS CounterInstanceName
FROM sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
([counter_name] = 'Cache Hit Ratio' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Hit Ratio Base' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Pages' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Object Counts' and [instance_name] ='_Total')
OR ([counter_name] = 'Cache Objects in use' and [instance_name] ='_Total')
OR ([counter_name] = 'SQL Compilations/sec')
OR ([counter_name] = 'SQL Re-Compilations/sec')
GO

Once you have the view created, you can query the view with the kind of counter you wanted to see based on the “CounterType” field or any other column provided by above view.

SELECT
CounterType
,CounterObject
,CounterName
,CounterValue
,CounterInstanceName
FROM
WhatIsPerformance
ORDER BY
SortOrder ASC
GO

I have some more views and script which DBA can keep handy, you never know, when you have to use it. Have a look at the articles given:

http://blog.extreme-advice.com/2012/09/14/find-currently-running-sessionprocess-in-sql-server-databaseinstance/
http://blog.extreme-advice.com/2012/09/15/use-sys-dm_exec_requests-and-sys-dm_exec_sessions-to-find-who-are-logged-in/
http://blog.extreme-advice.com/2012/09/17/find-locked-table-name-with-duration-and-count-in-sql-server/
http://blog.extreme-advice.com/2012/09/25/find-blocking-in-sql-server-and-use-it-to-send-an-alert/
http://blog.extreme-advice.com/2012/10/03/calculate-table-schema-size-in-sql-server/
http://blog.extreme-advice.com/2012/10/05/find-database-restore-date-in-sql-server/
http://blog.extreme-advice.com/2012/10/06/find-database-backup-history-in-sql-server/
http://blog.extreme-advice.com/2009/07/08/keep-watch-on-your-disk-space-in-sql-server-20082005/
http://blog.extreme-advice.com/2009/07/10/keep-watch-on-your-database-file-size-in-sql-server-20082005/
http://blog.extreme-advice.com/2012/10/10/repl-pending-xacts-performance-counter-and-high-value-in-sql-server/

http://blog.extreme-advice.com/2012/10/11/performance-counter-in-microsoft-windows-server-2008/

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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