Significance of SP_Who/Sp_Who2 in this situation seems lost

Question

Significance of SP_Who/Sp_Who2 in this situation seems lost

Sameer Raval

Hall of Fame

Points: 3214
More actions
April 6, 2006 at 8:30 am

#116865
After rebooting Production server last week I was looking at connection details
this week through sp_who/sp_who2 / sp_who_3 and obviously the CPU and IO
usage values for each connection were significantly low in comparison to the status
prior to reboot of server. This is understandable and not part of my question.

Now as time goes by I don’t see significant increase in number of open connection,
But there will be significant increase in CPU and IO values for each connection and
Values will move from 4 digit values to 7 digit values.

This behavior seems to be cumulative and adds value to usage by the same connection.

My question here is if it is so, and as you do not reboot production Sql Server
for months then the interpretation of this values through this command loose its significance. You cannot tell from 8 digit IO/CPU value associated to any connection that IO/CPU is bottleneck, because it can be due to sql server running for 6 months.

I can think of three ways in which you can bring this # of CPU/IO associated with connection down and they are
1. Reboot Sql Server frequently ---- which is hard to do for production server.
2. Reboot your Application server or any server connecting to your db and that will kill associated connection and new connection will be established.
3. In dot net application turn off connection pooling ---- which again is not the solution. If you are using connection pooling even if you close connection properly in application it can be reused from the pool.
Any knowledge based help from expert friends here will be highly appreciated.

Thanks in Advance.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Viewing 4 posts - 1 through 3 (of 3 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

They are meant as a cumulative and not for each. Only use these when you are tryin to determine what items may spend the most overall resource time an not for a single query. That is what Profiler and building traces are for, and you onl want to use then whn trying to troubleshoot an issue as capturing the information in realtime puts a strain on resources (just a bit).

Sameer Raval Hall of Fame Points: 3214 More actions · Answer 2

Thank you Antares, I agree with what you are suggesting.

But say for example, I would like to add one more situation,

say if application server is recently reboot by developer and you are not aware of that. When sql server performance problem arise you start looking at various place and one is this one. At that point of time also this sp_who2 figures will be of less significance as values of IO/CPU for conn to app server will be low, even though that is the one which is consuming most resources.

It will be nice to have your thoughts on this too.

Thanks,

Kindest Regards,

Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Then look at SPID 1 and see when th server started. This is te primary connecton by the server management account. This will give you a start date and time without havein o look in the SQL Logs. You then just adjust your thoughts based on this knowledge. If the server has been up for a few hours and you have a spid from an application (this is the reason I use the app nam option in my connections) or some other non-continual event that is in the 7 digit frame it is likely a suspect.