SQL 2005 or above – Find Transaction Rate on a busy OLTP Database

Change Database name of a below mentioned query

DECLARE @cntr_value1 bigint
DECLARE @cntr_value2 bigint

SELECT ‘BEFORE’

SELECT @cntr_value1 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘transactions/sec’
AND object_name = ‘SQLServer:Databases’
AND instance_name =’ABCD‘ /*DB NAME*/

select ‘ABCD’ DBNAME, @cntr_value1 ‘Lower Limit’

WAITFOR DELAY ’00:00:30′

SELECT ‘AFTER’

SELECT @cntr_value2 = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘transactions/sec’
AND object_name = ‘SQLServer:Databases’
AND instance_name =’ABCD’ /*DB NAME*/

select ‘ABCD’ DBNAME , @cntr_value2 ‘Upper Limit’

Select @cntr_value2 – @cntr_value1 ‘Transactions per 30 secs’

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s