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 |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
 Posted - 2005-02-03 : 05:31:23
|
| We have SQL2000, W2K, active passive clustered.We have had several occasions over the last few months where users have complained of slow performance to point of the system being unusable. Yesterday was one of those days.Because of the problems in the past I keep a profiler running (from a different server) that checks for timeouts, deadlock chains and long running queries. Normally this is pretty steady, the transaction log back-ups usually make it into the trace results along with maybe 3 or 4 user hits per hour. Yesterday was just mad, approx 1000 long running queries hitting every hour. Even the simplest was taking approx 5 times longer than usual.A colleague keeps an eye on the performance monitor on the server and has found that whilst we have 4 CPU's in the server, the second is almonst completely maxed out whilst the others hardly have any activity at all.This morning still looks pretty heavy but not as bad as yesterday. We did discover yesterday that a different server on the network (SQL7, WinNT) ran out of disk space in the early hours (drive for back-ups), this was resolved about lunch time. Could that really have affected the performance of the other server? They don't talk /write to each other at all.thoughts anyone? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-03 : 14:31:42
|
| What else is running on this machine?Do you have processor affinity setup (IE telling SQL to only use proc #2)?Do you have SQL server setup to "use all available processors"?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2005-02-04 : 03:49:25
|
| It's set to "use all available processors"I'm not too clear on the use of affinity mask. I spotted another post mentioning it and looked in BOL but it's still a bit grey. The "cost threshold for parallelism" is et to the default 5. Am I right in thinking if I reduce that to say 3, we would be more likely to use the other processors? Would this necessarily improve performance? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-04 : 04:13:04
|
| >> Would this necessarily improve performance?No - it'll probably spend more time managing threads than you will gain.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Performance - opinions please