sp_HumanEvents: View Creation and Cleanup Examples

Posted on by Erik Darling

Taking A Nice Warm Bath

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

6 thoughts on “sp_HumanEvents: View Creation and Cleanup Examples”

  1. Hey Erik,
    We’re using one of the views (HumanEvents_Queries) to check on the SP calling parameters, along with the rest of that useful info. We noticed that, in case of a .Net SqlClient Data Provider (as client app), no parameters are shown in the ‘sql_text’ view column. The column value looks like this:
    (@p0 int,@p1 bit)exec dbo.MVT_ClickThroughrateGet @p0, @p1
    Would it be reasonable to assume that the active extended event (keeper_HumanEvents_queries) failed to capture the params or would it be possible to find them stored in some other extended event field ?
    Thanks !

  3. That’s pretty odd, I had a better look and I guess it’s about the way query plan is formed/saved (by the extended event session ?).
    I ran the statement directly in SSMS. Here’s the query plan:
    https://ibb.co/KK41PZH
    As you expect, when running a SELECT statement, the left most operator (NodeId = 0) is ‘SELECT’. Also, the statement is visible (under ‘Query 1’ tag).
    BUT, if I click on the ‘showplan_xml’ view column, here’s what i see:
    https://ibb.co/Y4S0bWm
    Now, the left most operator is ‘Compute Scalar’ and the statement is not visible.
    So it seems that the ‘showplan_xml’ column contains an incomplete XML.
    Still, I was able to find the parameter value, by opening the column value as a string:
    https://ibb.co/Dghx4QC
    Forgot to mention the SQL Server version:
    Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) – 12.0.6108.1 (X64)
    May 29 2019 20:05:27
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

Comments are closed.