Re: SQL Server Performance monitoring

[Greg Keogh via ozdotnet]

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  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 
> *Sent:* Monday, July 31, 2023 10:27 AM
> *To:* 'ozDotNet' 
> *Cc:* 'Greg Keogh' ; Greg Low 
> *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 
> *Sent:* Monday, July 31, 2023 10:15 AM
> *To:* ozDotNet 
> *Cc:* Greg Keogh 
> *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/ 

RE: SQL Server Performance monitoring

[David Kean via ozdotnet]

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 
Sent: Monday, July 31, 2023 10:27 AM
To: 'ozDotNet' 
Cc: 'Greg Keogh' ; Greg Low 
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 
mailto:ozdotnet@ozdotnet.com>>
Sent: Monday, July 31, 2023 10:15 AM
To: ozDotNet mailto:ozdotnet@ozdotnet.com>>
Cc: Greg Keogh mailto: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/ 

RE: SQL Server Performance monitoring

[Dr Greg Low via ozdotnet]

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 
Sent: Monday, July 31, 2023 10:15 AM
To: ozDotNet 
Cc: Greg Keogh 
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/ 

SQL Server Performance monitoring

[Greg Keogh via ozdotnet]

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/