Monitoring Extended SP use

Question

Monitoring Extended SP use

alanspeckman

Hall of Fame

Points: 3344
More actions
September 30, 2008 at 3:39 pm

#382898

I have tried through code and Profiler to monitor Extended Stored Procedure use and have not had any luck. When I goto execute either documented or undocumented sprocs, XP_(DLL) or SP_(BuiltIn), the trace doesn't pic up any execution.
8272, regular sprocs get picked up by profile, no trouble.
SQL Server 2005, latest patches(CUs) installed on fully updated Server 2003.
Help?
Al.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

If you monitor the SQL:StmtCompleted Trace Event, and add a filter for TextData LIKE '%sp_%' or TextData LIKE '%xp_%' it will trap them.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Same question also asked on MSDN Forums:

http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3944576&SiteID=1&mode=1

alanspeckman Hall of Fame Points: 3344 More actions · Answer 3

Thank you for the quick reply.

That solutions works for the most part, except the underscore in this case means "any single character" in the "LIKE". What it picks up is any sproc that has an SP in it.

I tried escaping the underscore, "\_" so the LIKE recognizes it as an underscore, but that didn't work.

Ideas?

Al

alanspeckman Hall of Fame Points: 3344 More actions · Answer 4

If you include the underscore within brackets, "%SP[_]", it will look for just that character.

See this section in the link from my previous post.

"Using Wildcard Characters as Literals"

alanspeckman Hall of Fame Points: 3344 More actions · Answer 5

My filter is now N'%exec%sp[_]%'

I was picking up any execution with SP_ in it.

example:

create table #tmp_sp_get_sqlagent_properties

Now I will pick up only system extended stored procedures in either the Master or MSDB database.

aL

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Thanks for sharing that. I learned something new.

alanspeckman Hall of Fame Points: 3344 More actions · Answer 7

Small correction

It picks up System Stored Procedures, not just Extended SProcs. MSDB doesn't contain extended sprocs. It is picking those up as well, which is fine in my case.