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.k...@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' <gfke...@gmail.com>; Greg Low <g...@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 <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/
