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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-18 : 06:20:03
|
| Jin writes "Several weeks ago, due to a code error in a trigger, thousands of duplicate rows were inserted to a table. The increased table size caused web servers cache refresh queries timed out, eventually all the queries supposed to get data from web server’s cache were sent to SQL Server directly. SQL Server was overloaded. CPU usage jumped from normal 25% to 80%. From my laptop, I turned SQL Profiler on to investigate the activities using standard template plus Host Name. The CPU usage dropped to 40% immediately. After several tests, I confirmed that the CPU decrease was caused by Profiler. No matter the trace result is saved as trc file or not, no matter the result is saved to my laptop, local server or another server, the CPU usage always drops about 40%.Please explain the symptom.Thanks,Jin" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-18 : 09:26:07
|
| The profiler has an impact on the processing as you would expect as it is tracing what happens.It is probably slowing things down hence the cpu decrease.==========================================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. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-18 : 12:38:09
|
| Profiler will always cause performance problems no matter where you save the trace file to.Tara |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-03-18 : 22:53:47
|
| It does sound like SQL server as a whole was probably slowed down enough by dealing with the profiler trace that the system spent a lot more time waiting on network packet sends/receives, and this the apparent CPU utilization drop. I doubt that SQL server got more efficient with profiler attached.Cheers-b |
|
|
|
|
|
|
Turn on SQL Profiler cause CPU usage decrease