Strange to me procedure

Question

Strange to me procedure

lrosales

SSCommitted

Points: 1821
More actions
October 29, 2009 at 12:00 pm

#139247

does anyone know what the exec #am_generate_waitstats purpose is:-P
"We never plan to Fail, We just fail to plan":)

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

lrosales SSCommitted Points: 1821 More actions · Answer 2

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":)

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 3

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.

lrosales SSCommitted Points: 1821 More actions · Answer 4

I have Attempted that, with no luck. 😉

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

George M Parker SSCertifiable Points: 5412 More actions · Answer 5

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%'

GSquared SSC Guru Points: 260824 More actions · Answer 6

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

lrosales SSCommitted Points: 1821 More actions · Answer 7

a trace is correct. 🙂

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

lrosales SSCommitted Points: 1821 More actions · Answer 8

I have schedule no such job. 😉

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

GSquared SSC Guru Points: 260824 More actions · Answer 9

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

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]

lrosales SSCommitted Points: 1821 More actions · Answer 11

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":)

GSquared SSC Guru Points: 260824 More actions · Answer 12

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

Hansiehans SSC-Addicted Points: 454 More actions · Answer 13

You are probably running the Activity monitor which generates:

EXEC #am_generate_waitstats

entries in the Profiler after an executed query

GregoryF SSCertifiable Points: 6296 More actions · Answer 14

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!