Sometimes Fast & Dirty is OK. Other times, not so much.
Sometimes you just want to get an idea of what’s going on with your SQL Server.
You don’t need really accurate values, you just want a ballpark to be able to answer a question or make a decision.
This is where SQL, Fast And Dirt (F.A.D.) can help.
Average Transactions Per Day
Let’s just get right to it:
DECLARE @days SMALLINT ,
@trans INT
/* Sneaky way to get how long the server has been online, check the TEMPDB create date.
TempDB is re-created every time the SQL server service is restarted*/
SELECT @days = DATEDIFF(d, create_date, GETDATE())
FROM sys.databases
WHERE database_id = 2
/* Get the total transactions that have occurred on the system since restart.
sys.dm_os_performance_counters resets on every restart*/
SELECT @trans = cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Transactions/sec'
AND instance_name = '_Total';
/* Do some basic math to get an average number of transactions for each day the system has been running*/
SELECT @trans AS trans ,
@days AS days ,
@trans / CASE WHEN @days = 0 THEN 1
ELSE @days
END AS TransPerDay
There ya go, an approximate transactions per day for your server since it was last restarted.
But, it’s so dirty!
Yeah, I know. I said that it was didn’t I?
This captures ALL transactions that have occurred on your server. It isn’t narrowed down to a specific database, or only inserts, or anything fancy like that.
This is jsut a fast and dirty query to get you a quick answer of how much activity is on the server.
Why would this be of any use for me?
Well, let’s say you were going to be virtualizing your server, or even more amazing, moving to new hardware.
It would be good to know just how much is happening on your server to make sure you have enough resources and what to expect, right?
And admit it, odds are you don’t have detailed logging setup to track the transactions specific to what you want.
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp.
Click here to contact Chris or to follow him on twitter. [/icon_box]