My finger of suspicion is pointing at SQL because the links are: An ASP.NET Web Forms at the top -> which calls a Windows Service in the middle -> which runs the SQL queries at the bottom. Both the app and service are rather "thin" and have a good track record for the last 15 years, which leaves the suspect. I did see some SQL timeout exceptions in the service log. However, spying on the GC is a good idea anyway. I'll do a practice run to remember how it works, so when they next report a slow-down I can hop in and have a look. *Greg* On Mon, 31 Jul 2023 at 10:34, David Kean <David.Kean@microsoft.com> wrote:
How confident are you that it’s a SQL query? Unpredictable “stalls” smells like garbage collection, likely a Gen2 blocking. A dump during the stall or better ETL trace using PerfView will quickly rule that out.
*From:* Dr Greg Low via ozdotnet <ozdotnet@ozdotnet.com> *Sent:* Monday, July 31, 2023 10:27 AM *To:* 'ozDotNet' <ozdotnet@ozdotnet.com> *Cc:* 'Greg Keogh' <gfkeogh@gmail.com>; Greg Low <greg@sqldownunder.com> *Subject:* RE: SQL Server Performance monitoring
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 | 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 <gfkeogh@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*