Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-11-20 : 09:53:26
|
| I have a transient unknown problem with a production box. It seems to get slow. Things like sp_who will sometimes take 30 or 40 seconds to return. PerfMon, logs, and my usual tools shows all main systems are ok.I am writing a proc to be executed by a regularly scheduled job. The purpose of the proc is to exercise sql server and capture metrics about just how fast or slow the machine is responding.What types of tests would you include in such a proc? Have you written such a thing?Jay White{0} |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-20 : 14:25:27
|
| I don't have an answer to your question, but rather just a comment about why sp_who could take 30 to 40 seconds to return. This happens when there are a large amount of locks out there on objects. |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-11-20 : 16:58:03
|
| I've had the sp_who blocked before too. Seems like it happens most often when schema changes are happening on the server. You said things like sp_who are slow. Are you talking about other system stored procs and functions? Or just things in general?Jeff BanschbachConsultant, MCDBA |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-20 : 17:39:35
|
| I don't have an answer for you question, but I agree with duggan and occasionally have the same problem. I would run sp_lock and sp_who2 at the same time and see what is going on with the locks on the database. From sp_lock you can determine the objectid and from that point find out which connection and query are causing your problem. I have never thought about coding a SP to query this information for me, good idea though. I will let you know if I look further into it.You may have to wait for a while for the sprocs to return if you have a large number of locks, it is not blocked. I have had it take over 5 minutes to return when an idiot decided to change the table schema on a large production history table. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-20 : 20:20:22
|
| I appreciate the insite guys. It's not just sp_who ... its the entire sql server is slow. Like refresh the jobs window in EM ... doing a count(*) on a table ... everything. The way you would expect a server to act when it is over loaded ... except the hardware doesn't feel overloaded ...But regardless, that's not my question. I want to get away from the anticdotal "Damn, the thing is slow again" and start saying things like "We are at 9.8. It had been below 5.0 all day until noon." etc etc ... I've written such a proc and it's in production. I was just wondering if anyone else had tried such a thing and how they approached the prob.Jay White{0} |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-20 : 21:15:03
|
| Could the server be getting hit with a lot of network traffic? I don't suppose it's running IIS in addition to SQL Server, but is it being used by an IIS server? Is the IIS machine seeing a lot of network traffic that might be bogging it down while it's got open connections to the SQL Server?I remember when Code Red was floating around, I would see major spikes for up to 2 minutes at a time, and then they'd go away. If it's not Code Red it could be another virus or worm that spews out network traffic, or could've infected another box that's hammering the SQL Server with packets. Did you happen to trace any network metrics yet? It might be worth a look. |
|
|
|
|
|
|
|
|
Monitoring Performance