I was playing around with sp_BlitzWho and got blocked by another session. The session was from the DB were sp_BlitzWho is... I was planning to automate it and save the results to a table, so I could analyze the accumulated wait stats for different sessions (I have heard it does that in contrast to sp_WhoIsActive), but now it seems not to be such a good idea... Maybe it is better to just use sys.dm_exec_session_wait_stats or sp_WhoIsActive...?
1 Answer
You have a couple of different questions in here.
Can sp_BlitzWho get blocked?
Yes, any query can. There are DMVs that are known to ignore isolation level requests and incur blocking.
Should I use sp_BlitzWho to log wait stats by session?
I wouldn't recommend doing that on a scheduled basis, no. If you need wait stats by session, you're better off with a traditional monitoring tool with a higher sampling rate. You wouldn't want sp_BlitzWho running, say, every 5 seconds, but you really need data at that level of granularity if you want to analyze wait stats by session.
Why shouldn't I log wait stats by session?
Because your server can be a victim of the death-by-a-thousand-cuts scenario: a query that runs thousands of times per second, each time darting in and out, and never accumulating that much wait time each time it runs. However, in total, it can add up to a big deal.
answered Mar 29, 2021 at 19:15
-
1Thanks Brent, and thanks for the courses. I have access to Redgate, but can't seem to find useful info about wait stats. There is a top 10 wait list but it does not always work or the session I want to analyze is not in the list...– xhr489Mar 29, 2021 at 20:37
-
@xhr489 SQL Monitor samples waits every 20 seconds and we do our best to associate waits with an active session, but if we don't observe a query waiting on something we don't associate the wait with a query. We do still record the waits, though. If you're not seeing waits that you think you should be seeing, please file a support ticket so we can fix that for you.– anonMar 30, 2021 at 13:10
-
@JeremiahPeschka: Hi, are the wait stats only in the Global DashBoard? Or can one also find them other places... Because I can only see wait stats in the Global Dashboard and they are hidden next to the Top 10 queries (where one can sort based on CPU, logical reads etc.). The one can look at which queries were affected by the top 10 list (but again this is only a top 10 list)– xhr489Mar 30, 2021 at 13:23
-
Aye, that's currently a limitation of what we've got. I'd open up a support ticket about this and it'll hopefully get logged as a feature request. Then someone from my team will likely reach out to see what we can do to help out. It's a roundabout way of getting in touch, but I'm pretty sure we can't share email addresses on here :)– anonMar 30, 2021 at 13:57
-
@JeremiahPeschka: regarding the information which you samle: do you put it on a database on the instance or on another database e.g. in the cloud?– xhr489Jun 19, 2021 at 17:15