HumanEvents_Blocking fails in query about darlingdata HOT 13 CLOSED

I have a fix for this. Can you grab the version in the dev branch? Thanks.

from darlingdata.

I'll do that

from darlingdata.

Installed new version of SP

deleted the view: dbo.HumanEvents_Blocking;

ran
EXEC sp_HumanEvents @output_database_name = N'HumanEvents', @output_schema_name = N'dbo';

new version of view was created.

Query still fails!

from darlingdata.

Hm, weird. The fix is here. I definitely know what you're talking about.

84581a6#diff-bcf3f7d484f6222d3a4ba16693ba0bfe

If you can, make sure you've got the right view version by checking the definition. It should start like this:

    WITH pablo_blanco
      AS (
         SELECT DISTINCT
                b.event_time,
                b.object_id,
                b.transaction_id,
                b.monitor_loop,
                b.spid,
                b.ecid,
                b.activity
         FROM [dbo].keeper_HumanEvents_blocking AS b
         WHERE b.activity = N'blocking'

The most important thing being the where clause at the end

Then you should see this below:

                 FROM [dbo].keeper_HumanEvents_blocking AS b
                 JOIN pablo_blanco AS p
                     ON  p.event_time = b.event_time
                     AND p.object_id = b.object_id
                     AND p.monitor_loop = b.monitor_loop
                     AND p.spid <> b.spid
                 WHERE b.activity = N'blocked'

If you don't see those, you've got the wrong version. If you do see those, you're gonna have to hit me up with repro data so I can take a closer look.

Thanks!

from darlingdata.

As far as I can tell that matches. What should I send you?
The keeper_HumanEvents_blocking table has only 460 rows

PS: I'm just trying this out. No urgency here. But I like what you are doing!

from darlingdata.

Also, the changes as listed in your commit are in my version

from darlingdata.

Ah, okay, sorry about that.

If you can, send me the table data in an Excel file, just without the longer text data like query text and the blocked process report XML.

Thanks!

from darlingdata.

Here is the data attached
HumanEvents_sampledata.xlsx

from darlingdata.

Got it, thanks! I'll take a look soon.

from darlingdata.

First, you have some very weird blocking going on. If you look at:
SELECT * FROM keeper_HumanEvents_blocking ORDER BY id;
It's a bunch of crazy system stuff.

Second, I totally ate it on my recursive CTE. It worked fine on the blocking demos I have, but yours created an infinite loop. I don't see an easy way to fix that, and I don't want to keep hitting it.

There's a new version in the dev branch that creates a much simpler view with no recursion. It's not as cool as the recursive version for simpler blocking scenarios, but cool didn't get me very far anyway.

Thanks for letting me know about this.

from darlingdata.

Yeah, I was trying to figure out why my create database was failing. Turns out that the script I was running to find fragmentation in a database locked 'model'.

from darlingdata.

That'll teach you to go looking for fragmentation 😃

from darlingdata.

Its all using stackoverflow...just learning how stuff works.

Thanks for looking at this

from darlingdata.