Terrific Trifecta of T-SQL on Tuesday

Much Monitoring in the Month of May


Sometimes tooling can be easy, helpful and cheap. You usually only get to pick 2 legs of that triangle. So instead of a triangle you get a less than sign, as in less than awesome. This quick bit of code gets all legs of the Triforce.

I'm better at T-SQL than I ever was at Legend of Zelda
I’m better at T-SQL than I ever was at Legend of Zelda

First leg – Easy.
One create table script and one Agent job and you are ready to go with this. The table we will store the results in is a mirror of sp_Who2, plus an insertdate column with the default of GETDATE().

CREATE TABLE [dbo].[Who2](
[spid] [int] NULL,
[status] [varchar](50) NULL,
[loginname] [varchar](100) NULL,
[hostname] [varchar](100) NULL,
[blkBy] [varchar](50) NULL,
[dbname] [varchar](50) NULL,
[cmd] [varchar](200) NULL,
[CPUTime] [int] NULL,
[DiskIO] [int] NULL,
[LastBatch] [varchar](50) NULL,
[ProgramName] [varchar](200) NULL,
[spid2] [int] NULL,
[request_id] [int] NULL,
[insertdate] [datetime] NOT NULL
ALTER TABLE [dbo].[Who2] ADD CONSTRAINT [DF_Who2_insertdate] DEFAULT (getdate()) FOR [insertdate]

Pretty simple and easy there. Set this up in a utility/admin database and you’re off to the next step, the Agent job. The INSERT script below puts the results of sp_Who2 into your new table. All you need to do is determine the timing. I have it setup for every 10 minutes.

INSERT INTO Who2(spid,[Status],[LoginName],hostname,blkBy,dbname,[cmd],CPUTime,DiskIO,LastBatch,Programname,spid2,request_id) EXEC sp_who2

Second leg – Helpful
Not all homegrown or off the shelf apps have ways of measuring usage. This is how this came about for me. One application had a hard limit on number of concurrent users and we wanted warnings when we were close and be able to look at it over time.

DECLARE @maxLicensedUsers AS INT = 10 /*What's your app limit? I hope it is more than 10, but who am I to judge.*/
DECLARE @currentLicensedUsers AS INT
DECLARE @emailMessage AS VARCHAR(100)

SET @currentLicensedUsers = (
WHERE ProgramName IN (”) /*Add your app name(s) here*/
AND DBName = ” /*Add your db name here*/
AND insertdate = (SELECT MAX(insertdate) FROM Who2)

IF @currentLicensedUsers >= @maxLicensedUsers
SET @emailMessage = ‘Your app is super popular and and has ‘ + CAST(@currentLicensedUsers AS VARCHAR(4)) + ‘ sessions.’
EXEC msdb.dbo.sp_send_dbmail @profile_name=”, /*Go set up a db mail profile if you don’t already have that*/
@recipients=”, /*Someone important should get these emails. DBAs are important, maybe one of those people.*/
@subject=’Super popular app license warning’,
@body= @emailMessage

This app also has an issue with users opening up duplicate sessions. That doesn’t help with the hard limit on concurrent users. An alert on duplicate sessions is also easy, helpful and cheap.

Working for a global agency means you never sleep or maybe that is your app never sleeps. Logging this data regularly helps point out when the application is most used. This query gives me average and max session counts by hour for the last month. This helps plan maintenance windows, which are generally scheduled for Friday evenings. Ah, the exciting life of a DBA.

, ROUND(AVG(SessionCount),0) as S_AveCount
, MAX(SessionCount) AS S_MaxCount
DATEPART(YEAR,insertdate) AS yr,DATEPART(MONTH,insertdate) AS mn,DATEPART(DAY,insertdate) AS dy,DATEPART(HH,insertdate) as hr,
DATEPART(minute,insertdate) as 'minute'
, COUNT(DISTINCT loginname) as loginCount
, COUNT(DISTINCT loginname + ' ' + ProgramName) as programCount
, COUNT(spid) as SessionCount
FROM dbo.Who2
WHERE dbname = ''
and ProgramName IN ('')
AND insertdate > DATEADD(M,-1,GETDATE()) --Get data for previous month
AND DATEPART(WEEKDAY,insertdate) <> 7 --Saturday is a low activity day in most countries so we're filtering that out
GROUP BY DATEPART(YEAR,insertdate),DATEPART(MONTH,insertdate),DATEPART(DAY,insertdate),DATEPART(HH,insertdate), DATEPART (minute,insertdate)) AS a

Third leg – Cheap
This is super cheap. You found some code on the internets, you have a SQL Server (presumably) and there isn’t anything to license. I’m sure you could find a way to send me money if you really wanted to, but there are better things to do with your cash. Like helping out in Nepal

Perfection is not one of the legs
This solution hits easy, helpful and cheap for me, but it is not perfect. It is a snapshot of activity every 10 minutes. It is not real time or complete monitoring. Are there users logging in and out between the 10 minute samples? Could be, but I’m not capturing that. This works as long as I and the business owners are clear about what is being measured and what is possibly being missed. Will this satisfy auditors who need to verify system access? I doubt it, but you would need to ask an auditor about that.


One thought on “Terrific Trifecta of T-SQL on Tuesday

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