Hi Greg, The performance trace procedures in SDU Tools have duration as one of the summaries provided.
Duration is a curious one though. In so many cases, it's meaningless, yet it's the one that the Microsoft tooling often worries about most. For example, if you have a query that executes, and then the client takes forever to retrieve the rowset that was produced (e.g. by reading it a row at a time and doing other things in between each row), the duration shows the entire time. But it could be a very light query. In your case though, that might well help, particularly if you find queries with long durations, but few pages read. That means that the query can't get its work done for some reason. Whenever you have that, it's blocked waiting on something else. If you can catch it while it's blocked, even Activity Monitor in SSMS can show you what's at the head of a blocking chain. sp_whoisactive (from Adam Machanic) will do a better job of that again. But that only helps if you can catch it while it's happening. That's why tracing usually helps. The other thing I've done in the past, if it becomes very hard to find, is to just leave a proc running in the background that every 5, 10, or 20 seconds, finds any process that's at the head of a blocking chain, and writes details of it out to a table. That's more work, but it shows clearly what the regular culprits are. The "Show Current Blocking" code in SDU Tools should provide an example to help get something like that going. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg: https://about.me/greg.low From: Greg Keogh via ozdotnet <ozdotnet@ozdotnet.com> Sent: Monday, July 31, 2023 10:15 AM To: ozDotNet <ozdotnet@ozdotnet.com> Cc: Greg Keogh <gfke...@gmail.com> Subject: SQL Server Performance monitoring Folks, We have a problem on a live server where web users experience occasional unpredictable "stalls". There are a few links in the chain where the problem could be, but based on some clues in logs, I suspect that it's the last link at the bottom of the chain ... SQL Server that's the source of the problem. But I need evidence. Is there some feature of SQL Server or perhaps some tool that can detect queries that are suspiciously long running? This is SQL Server full standard edition. I haven't had to poke deeply into SQL Server's machinery before, so I'm in unfamiliar territory. Cheers, Greg Keogh
-- ozdotnet mailing list To manage your subscription, access archives: https://codify.mailman3.com/
