SQL Server Profiler: Help needed monitoring one specific store procedure

Question

Post reply

SQL Server Profiler: Help needed monitoring one specific store procedure

ttsang

Old Hand

Points: 361
More actions
September 2, 2008 at 10:30 am

#193103

Hi All,
I want to be able to monitor a single stored procedure via SQL Server Profiler, but I do not know where I would specifiy the SP.
background:
I have a production server for which a lot of insertion take place via a stored procedure.
There have been a few sporadic problems with some data not being inserted fully - the SP pops data into 10 different db tables - so I though I wanted to turn on the windows performance tool (to monitor mem/cpu/hd activity) and SQL Server Profiler tool to monitor when the SP was run and how long it takes, to give me an idea of where and when a problem may occur.
My problem is that I cannot seem to find where I can specify the name of the single SP.
Any pointers would be appreciated.
Thanks
TT

Viewing 14 posts - 1 through 13 (of 13 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

In the TextData column add a filter that reads like this:

Like '%yourSprocName%'

I recall trying this once and not being able to get it to work. Test it out first.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

A key point here is to make sure the events you are tracking include the TextData column. One way to get this working is to start a trace filtering on SPID and from that SPID (an already connected SSMS query window) execute your SP. Then save the trace to a file, re-open it and apply a filter on TextData until you get it right.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ttsang Old Hand Points: 361 More actions · Answer 3

Thanks guys.

I will have a play around with that today.

TT

sweetpea_3007 SSC Journeyman Points: 93 More actions · Answer 4

hai all..

i want to know, how to make sql profiler running daily?

thanks

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

sweetpea_3007 (1/7/2009)
hai all..
i want to know, how to make sql profiler running daily?
thanks

You just need to script out your profiler session with all your chosen events, columns, filters and other settings and create a job to run that script daily, say as a stored procedure. It is best to run Profiler through script, especially if it needs to be run on a regular basis.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

t berry-385482 SSC Veteran Points: 283 More actions · Answer 6

If you still need Profiler (SQL 2000), to filter on specific stored procedures, enter as a filter on TextData, for example:

exec _getStuff%

No single quotes or anything else and it will work.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Or filter by ObjectID:

use db_name;

select object_id('sprocName');

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Sudiendra SSCommitted Points: 1839 More actions · Answer 8

Is there a way to monitor more than one SPROC? In other words, can i monitor more objects..i have 9 SPROCS to be monitored..

t berry-385482 SSC Veteran Points: 283 More actions · Answer 9

Yes, in the filter you can more than one item. Matches are made using LIKE so multiple sprocs can be used.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

t berry (2/26/2009)
Yes, in the filter you can more than one item. Matches are made using LIKE so multiple sprocs can be used.

Interesting, I haven't tried that.

What would be the syntax?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

t berry-385482 SSC Veteran Points: 283 More actions · Answer 11

For syntax see my post for this topic on 18 Feb, 2009.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

t berry (2/18/2009)
If you still need Profiler (SQL 2000), to filter on specific stored procedures, enter as a filter on TextData, for example:
exec _getStuff%
No single quotes or anything else and it will work.

How would it work for multiple stored procedures, that do not start with the same set of characters?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

alinnemarie SSC Enthusiast Points: 173 More actions · Answer 13

i would like to monitor a single store procedure and all the actions inside that store procedure!

setting TextData like '%MySP%' does not help 🙁