How Many Processes at the same time?

Question

How Many Processes at the same time?

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
June 13, 2006 at 11:04 am

#118408

Hello,
I am looking at the best way to say how many processes are running concurrently based on the trace results. I have a trace that captures login/logout/login failed events with a lot of columns including StartTime, EndTime, SPID, DBID and a lot more. I do know how to extract results from the trace into a nice table or query results. I do know how many processes for 2 particular databases I had initially when the trace started - both Existing Connections and Current Activity Window give you that. Now I have to calculate how many concurrent connections I have during monitoring period based on logins /logouts/starttime/endtime.
I can estimate "no more than..." by counting Distinct SPID - obviously the max number of concurrent connections is no more then number of different SPIDS. I also can create a cursor (or no cursor ) with running totals that will take an initial number of connections, add one for each logon event and substruct one for each logoff event into a calculated running totals field.
I wonder if somebody has an elegant solution for that.
Thanks in advance,
Regards,Yelena Varsha

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Yelena

Are you looking for the maximum number of concurrent connections during a period, or the number of concurrent connections at a specified point in time?

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If you're looking for point-in-time, save your trace to a table called TraceTable and run this:

declare @PointInTime datetime

select @PointInTime = 'June 16 2006 10:55:49.890 AM'

if @PointInTime < (select min(starttime) from TraceTable)

or @PointInTime > (select max(endtime) from TraceTable)

begin

print 'Point in time specified is out of the range of the trace'

end

else

begin

-- Add up existing connections and logins and subtract logouts

select

(select count(*) from TraceTable where EventClass = 17)

+ (select count(*) from TraceTable where EventClass = 14 and StartTime < @PointInTime)

- (select count(*) from TraceTable where EventClass = 15 and EndTime < @PointInTime)

as [Concurrent Connections]

end

John

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hi John,

Thanks so much for your reply. This query works great for the point of time. I also do need the MAX number of connections and the current number of connections at any given point. I have to sort chronologically for that.

I know how to do the running totals with or without the cursor, but how would you recommend to sort? I suppose the data in the actual trace are sorted on the first come-first recorded basis. But I am afraid that once I put everything into the table or temp table the sort will go away. I do need some kind of the identity or ID field. The trick here is that event 14 (login) contains the realstart time and NULL for the endtime and event 15 (logout) contains the connection start time (login time that happened long before) for the starttime and a real logout time for the endtime. So if we have to sort chronologically we have to sort as: for the event 14 take a starttime, for the event 15 take an endtime.

I suppose I just can use CASE statement when sorting using the above logic.

Yelena

Regards,Yelena Varsha

stax68 SSChampion Points: 11711 More actions · Answer 4

For point-in-time, look at sysprocesses. If a series of snapshots rather than a complete audit is ok, you could just poll it every few seconds. I don't think that would necessarily slow things down any more than running your trace.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Yelena

To sort chronologically, use the RowNumber column. This is incremented for each event, including logins and logouts, as they happen (at least that appears to be the case in my sample - you may wish to verify on your own data).

For the current number of connections, you can use select @PointInTime = getdate() in the above query, or you can use sysprocesses as Tim suggests.

The query below will give you the concurrent connections after each login/logout; from that you can easily select the maximum.

select x.rownumber, x.eventclass,

(select count(*) from TraceTable y

where y.EventClass in (14, 17)

and y.RowNumber <= x.RowNumber)

-(select count(*) from TraceTable y

where y.EventClass = 15

and y.RowNumber <= x.RowNumber) as Connections

from TraceTable x

where x.EventClass in (14, 15, 17)

order by RowNumber

Hope that helps

John

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

John,

Thanks! This works well!

The only thing I did not find RowNumber column for the trace columns. Do you know the Column ID for setting up this column?

I found EventSequence column in 2005:

51

EventSequence

Sequence number for this event.

but did not find anything for SQL Server 2000.

I imported RowNumber when importing into the table. Please, tell me how do you get RowNumber in the table? Do you use Identity when importing or do you use something when recording the trace?

I used the following before your query replacing TraceTable with ##TraceTable and dropping the table after the processing:

select

IDENTITY(int, 1,1) AS RowNumber,eventclass,databaseID,LoginName,SPID,StartTime,Endtime

Into

##TraceTable

from

::fn_trace_gettable('C:\Temp\MyTrace.trc',default)

Yelena

Regards,Yelena Varsha

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Yelena

If you save your trace direct to a table then the RowNumber column will be there automatically for you.

John

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 8

Thanks John!

To think about it, it is logical: if we save to file, then the rows will be ordered in the chronological order by design, but if we save directly to table, they do need the rownumber!

thanks again,

Regards,Yelena Varsha