|
How can I identify the more slower queries? I have a performance problems on my database server. The disk read time is very high.
(comments are locked)
|
|
For SQL Server 2000, I would suggest running a trace using SQL Server Profiler and filtering either on CPU, duration and/or reads (and possibly writes). With the filter, SQL Server Profiler will only show queries that meet your specification. For example, duration > 1000 milliseconds. Here's a good tutorial http://support.microsoft.com/%2Fkb%2F325263
Nov 16 '09 at 05:57 PM
Jim Orten
(comments are locked)
|
|
Instead of using the Profiler gui to collect this data, I'd suggest, very strongly, that you set up a server side trace. This is using the trace events that Profiler uses, but activated through a TSQL script. This puts less of a load on the server and guarantees a good set of data will be collected. Gail Shaw has two excellent articles on how to set this up over at Simple-Talk. Here's the first one. Couldn't agree more Grant. When I do performance troubleshooting I use a set of server-side trace scripts and also use perfmon logs and alerts to track performance information. I then like to load the trace into a table and query it based on cpu, reads, writes and duration to find the top n queries by any of those metrics.
Nov 17 '09 at 10:32 AM
Mark Allison
(comments are locked)
|
