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 |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-09-29 : 21:33:34
|
| Hi,I wanna use sql profiler to check what s causing a sql server 2000 to be slow.How do I use the profiler for this reason, the application is always running and the users re always producing, it just slows down randomly or actually most of the time, how do I use profile to inspect what s causing the problem?I ve seen a documentaion in which I have to install the blocking script SP. but we can t install that for now cause the manager is not too sure yet and there re restriction on modifying the system since it s a pharmaceuitical company.Any way, how do I use profiler to detect the query or what is causing the problem?Thanks a lot. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-29 : 22:11:55
|
| Trace all queries then analyze those with long duration. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:28:21
|
"it just slows down randomly or actually most of the time"In SQL 2000 and on a database server that is user-heavy (e.g. supporting a busy web site) this might be File Extension.The File Extension (SQL 2000) is slowish, particularly if set to the default 10% on a multi-GB database, and if users get slowdown / timeouts and start hitting REFRESH like mad it makes a bad thing worse  If you think that might be it change the file extension to a fixed number of MB, rather than 10%.There should be a "Long duration query" template for SQL Profiler, which would be a good place to start looking. But that's going to tell you that your mega import process that then re-posts to 56 other table it slow, and I'm sure you know that already ... so:Most-frequently-occurring will tell you where a little optimisation could have most bang-for-buck.But your biggest problem will probably be that when the server goes-slow then everything runs slow, so everything start s looking like a long-running query.And if you get all of that then you are back to basics: Good housekeeping and optimising things carefully in order to speed them up.Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-30 : 23:54:12
|
quote:
Originally posted by Kristen
"it just slows down randomly or actually most of the time"Good housekeeping and optimising things carefully in order to speed them up.Kristen
How do we do that Kristen?P.S: I will try the profiler Long duration query suggestion and get back to u. Thanks a lot. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-01 : 00:15:23
|
| It's most likely to be someone running something that is using a lot of resources.==========================================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. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-01 : 05:01:46
|
| Install IOstatistics and Deadlock Detector from www.sqlsolutions.comBoth utulities work for 15 days free.Deadlock Detector will record all locks and deadlocks + much more useful info, because when shit happens it is too late to check sysprocesses, syslocks etc. IOstatistics will show if if the slow down was caused but some heave running quesries. |
|
|
|
|
|
|
|
|
using sql profiler to monitor performance