SQL Stuff

Get SQL Server Version Average Transactions Per Instance

Here’s some quick logic to get you the following information about your database server.

1. Server Name

2. SQL Version (complete)

3. Total Combined Database Size (all databases)

4. Number of Days the Service has been online (this is used to determine the average transactions per day. Number of days online gives you the sample)

5. Total transactions across entire sample (days online)

6. Average transactions per day


use master;

set nocount on

declare @days smallint

declare @trans bigint

set @days = ( select datediff(d, create_date, getdate()) from sys.databases where database_id = 2 )

set @trans = ( select cntr_value from sys.dm_os_performance_counters where counter_name = 'transactions/sec' and instance_name = '_total' )

select

'server' = @@servername

, 'sql version' =

cast(substring(left(@@version, 25), 10, 16) as varchar(50)) + ' ' +

cast(serverproperty('edition') as varchar(50)) + ' ' +

cast(serverproperty('productlevel') as varchar(50)) + ' Build: ' +

cast(serverproperty('productversion') as varchar(50))

, 'total database size' = cast (convert(decimal(10,2),(sum(size*8.00)/1024.00/1024.00)) as varchar(10)) + ' GB'

, 'sample num days' = @days

, 'total trans' = @trans

, 'average daily trans' = @trans / case when @days = 0 then 1 else @days end

from

sys.master_files;

Note:

The average transactions per day can be calculated with a bit more granularity if necessary. This logic is the total transactions all inserts/updates/deletes etc. and not narrowed down by any means, but it’s sufficient to calculate the kind of usage your database instance is experiencing as a whole. For more definitive idea of average transactions per database (which I’ll be writing later) should be done per database. This logic is referencing ‘_total’ instance name which is everything under the instance. Thanks goes out to Chris Bell at Waterox for posting his write-up on collecting average transactions. More can be found at: https://wateroxconsulting.com/archives/average-transactions-per-day/

Advertisements

Categories: SQL Stuff

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