Strange to me procedure

  • does anyone know what the exec #am_generate_waitstats purpose is:-P

    "We never plan to Fail, We just fail to plan":)

  • Looks like you have a temporary proc there. Kind of like a temp table, but it's a proc instead.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I am aware of that, the store procedure is created in the tempdb, but still does not answer the Question of how what and why is created or the Purpose of it.

    thanks 🙂

    "We never plan to Fail, We just fail to plan":)

  • If it's a temp proc, it was created on your system. We have no way of knowing what it is.

    You could try scripting it to see the code.

  • I have Attempted that, with no luck. 😉

    "We never plan to Fail, We just fail to plan":)

  • From the name of it, I'd have to take a wild guess and think that it's something a DBA came up with the gather wait stats of some sort. If that's the case, I'd look at the scheduled jobs on the box to see if I can find it (you can also try querying the command column in msdb..sysjobsteps). I'd also look at the auto startup stored procedures to see if it's being kicked off from there.

    As a last resort, I'd search through the syscomments table in all databases for something where text like '%wait_stats%'

  • Where are you seeing the exec command on it? In a trace?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • a trace is correct. 🙂

    "We never plan to Fail, We just fail to plan":)

  • I have schedule no such job. 😉

    "We never plan to Fail, We just fail to plan":)

  • Take a look in the trace for what commands were being run by the same SPID (if that's in the trace). That should tell you what proc or script is being run. If you have the text field in the trace (assuming that you do and that's where you got the exec command), it might even contain the create command for the temp proc. That's where I'd start.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Strictly as a pure guess -- Checking Books On Line for:

    SQL Server, Wait Statistics Object at:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/cb7f917d-4291-4115-9b78-ee7692ebbb2d.htm

    Can you associate any of the data returned with any of the items contained in the Wait Statistics Object?

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • thnks SSCertifiable

    solution:

    I did as you suggested as found out it was the admin, when the system runs the tracer it creates the procedure and there it lies hiden, temp procedre. 🙂

    "We never plan to Fail, We just fail to plan":)

  • lrosales (10/29/2009)


    thnks SSCertifiable

    solution:

    I did as you suggested as found out it was the admin, when the system runs the tracer it creates the procedure and there it lies hiden, temp procedre. 🙂

    Just a hint, "SSCertifiable" is a title for the number of posts, not the person. The username is right above that. Would be like addressing you as "Valued Member" (you'll see that right below your screen name). No big deal, but thought I'd mention it.

    Traces are great for finding out "What the heck is going on in my database???"

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • You are probably running the Activity monitor which generates:

    EXEC #am_generate_waitstats

    entries in the Profiler after an executed query

  • It is activity monitor, for the wait resources. However, does anyone know what goes into the SP #am_generate_waitstats?

    I only get a couple of hits on Google for the SP and would really like to know how MSFT comes up with those values.

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply