Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Maximum Concurrent Users in a day Expand / Collapse
Author
Message
Posted Sunday, February 10, 2013 6:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
Jeff Moden (2/9/2013)
SQL Kiwi (2/9/2013)
Kwisatz78 (2/8/2013)
I have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.

This was the subject of a series of articles by Itzik Ben-Gan. The fastest solution found was submitted by, among others, our very own R Barry Young. You can read all about it here:

http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407

Be sure to read the whole thing, not just the first page. I have a SQLCLR solution that beats that by around 30% but unless you really need that extra bit of speed (and are quite expert with T-SQL and SQLCLR) I would stick with Barry's code.


Freakin' awesome link, Paul. I was able to modify Barry's code to also correctly populate the MX column for the Logoffs so that I could graph the "valleys" as well as the "peaks". I've been trying to do this solution in a similar fashion and got seriously hooked because I just didn't see the 2:1 ratio that Barry included in his final formula. Thanks for posting the link. It's definitely a keeper.

Barry, if you read this post, I know it's been 3 years since you wrote the code and that article came out but thanks a million to you for writing the code and to Itzik for 'splainin' it.


Truly an amazing solution for this problem.

+10 to Barry and Itzik!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1418174
Posted Friday, February 15, 2013 3:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:58 AM
Points: 576, Visits: 1,532
I just wanted to let you all know that I implemented the solution and it works a treat as the previous poster says many thanks to Barry and Itzik.
Post #1420470
Posted Friday, February 15, 2013 11:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 02, 2014 11:13 AM
Points: 51, Visits: 279
Perhaps a bit late but a different solution in one statement

To set the data up based on the solution of http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407:

USE tempdb;

IF OBJECT_ID('dbo.Sessions', 'U') IS NOT NULL DROP TABLE dbo.Sessions;

CREATE TABLE dbo.Sessions
(
keycol INT NOT NULL,
app VARCHAR(10) NOT NULL,
usr VARCHAR(10) NOT NULL,
host VARCHAR(10) NOT NULL,
starttime DATETIME NOT NULL,
endtime DATETIME NOT NULL,
CONSTRAINT PK_Sessions PRIMARY KEY(keycol),
CHECK(endtime > starttime)
);


GO

CREATE INDEX idx_nc_app_st ON dbo.Sessions(app, starttime) ;
CREATE INDEX idx_nc_app_et ON dbo.Sessions(app, endtime);

GO

--- Populate the table

declare @i int = 1
declare @DT_Rnd datetime

while @i < 1000
begin

Set @i = @i + 1

set @DT_Rnd = dateadd( mi , RAND()* 1440 , cast('20090212' as datetime) )

INSERT tempdb.dbo.Sessions(keycol, app, usr, host, starttime, endtime)
VALUES( @i
, 'app' + right( '00' + CAST ( 1 + cast( RAND()* 15 as int) as varchar(2)) , 2)
, 'user' + right('000' + CAST ( 1 + cast( RAND()* 150 as int) as varchar(2)) , 3)
, 'host' + right('000' + CAST ( 1 + cast( RAND()* 240 as int) as varchar(2)) , 3)
, @DT_Rnd
, dateadd( mi , 5 + (RAND()* 50) , @DT_Rnd )
);


And for the single statement solution:





Select APP
, [No of Concurrent users]
, [Point in Time]
from (

Select Toe.app
, Toe.[Point in Time]
, [No of Concurrent users] = COUNT(distinct keycol)
, RID = row_number() over ( partition by toe.App order by COUNT(distinct keycol) desc )

from ( -- Time of Events
select app , [Point in Time] = starttime from tempdb.dbo.Sessions
union Select app , [Point in Time] = endtime from tempdb.dbo.Sessions
) as TOE

inner join tempdb.dbo.Sessions as S1
on s1.App = Toe.app
and Toe.[Point in Time] >= s1.starttime
and Toe.[Point in Time] < s1.Endtime

group by Toe.app , Toe.[Point in Time]

) as c

where RID = 1


order by App , RID





Polite comments are welcome
Post #1420697
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse