understanding sp_who3 usage

[wbodger]

Whne I am trying to figure out what is slowing down my database, I tend to start with sp_who3 (thanks for writing it!) and I am wondering which would tend to have more of an impact on response times, CPU Time or I/O Time? I have always thought it would be I/O Time, but wasn't sure, or is it different depending on what you are doing?

Willie

 

[mrdenny]

It will be different depending on what the system is doing. If the system is a data warehouse then it's IO profile will be much different then an OLTP system.

On a heavy load system the disk IO will typically show up before CPU becomes an issue, however there is no hard fast rule about this. If all you are doing is reading and processing the same records, then everything will come from memory and there should be very little disk load, but lots of CPU load.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

Hmm... So, I've got a machine running Windows Server 2003, and SQL 2K sp4. On that machine is an old version of Onyx and 2 gb of RAM. The system houses all of our product info, all of our customer records, it handles the SN distribution, we run internal reports off of it and the Customer Service and Tech Support folks are connected to it thru the Onyx client application. Now, the client connections seem to be the most resource intensive, looking like this 39228 (CPUTime) 52110 (Disk I/O), although there are a few processes that look like this 266109 (CPUTime) 2754 (Disk I/O). So, most of the processes are sleeping, bt they still seem to be taking up quite a bit of time and Disk cycles. Am I perhaps misunderstanding the output, or is that just the way SQL Server works or might I have an issue with processes being improperly handled?

Willie

 

[mrdenny]

Keep in mind that those counters are cumulative from the time that the connection was started. It the client has been connected for several hours those numbers may not be that bad.

If a user process has issued 52k disk IOs there is probably a problem query that they are running which is causing more disk load than it needs to. Posibably an index needs to be added, stats needs to be updated, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com