Hi we run std 2008 r2.  I just started experimenting with some of the monitoring solutions offered at https://social.msdn.microsoft.com/Forums/en-US/5781c375-5339-47bd-9db0-43b4755a3eea/monitoring-usage-of-about-10-tables-between-11pm-and-2am?forum=sqldatabaseengine  .    I started a trace on our test server filtering db name and text data.  I entered two different db names and then in text data filtered 4 partial table names (no schemas, no periods no brackets etc) that would catch the  6 of the 10 tables I am interested in monitoring.

then in ssms I did a select top 1000 on one of those tables in one of those dbs and see nothing in my trace.  I already know that just filtering db's alone shows this select in the trace's text data column.  Is there some basic sql profiler behavior i'm missing?  Maybe i'm expecting too much out of a "like" filter on textdata and need to revert to just filtering the dbs.

Even after limiting the filter to just one partial name I still see nothing.  Also matching the case specified in my filter didn't help either.

 

• Edited by db042190 18 hours 1 minutes ago consistency

ahh maybe this is where josh's wildcard comes in.  I'll post back here.

yes, I put the % signs around the partial names and all is good.

reading more now on how/if proc sql will display.

• Edited by db042190 17 hours 53 minutes ago follow up

this is disappointing.  I added a proc in a different database on the same server i'm monitoring.  It selects top 1000 * from one of my tables cross db's.  And profiler doesn't show anything.  Even if I remove the db filter it shows nothing.  Is it impossible to use textdata to filter on a proc's sql?  Right now the events I have checked are rpc:completed, sql:batchcompleted, sql:batchstarting.

I added sp:stmtCompleted to the event list but that only helped if I left off the db filter. 

How can I ask profiler to tell me about any query coming to my target db regardless of source?  I'm guessing i'll have the same challenge for queries coming from linked servers.

You can not filter in SQL Profiler on the code executed inside a store procedure, that's not possible.

I would have to disagree as it seems profiler did filter on a proc's sql run from a different db.  The sql that proc executes showed up in text data.

I added sp:stmtCompleted to the event list but that only helped if I left off the db filter. 

How can I ask profiler to tell me about any query coming to my target db regardless of source?  I'm guessing i'll have the same challenge for queries coming from linked servers.

I haven't played much with the database filter.  Don't you want to leave it off anyway?

You can capture for all databases and filter it out later in your analysis, if there's any reason to.

Josh

• Edited by JRStern 10 hours 10 minutes ago

You can not filter in SQL Profiler on the code executed inside a store procedure, that's not possible.

That is not correct. If you are captuing SP:StmtStarting or SP:StmtCompleted, you can filter on TextData, and then for these columns you will filter on code inside the procedure.

But you cannot set up a filter on, say, SP:Completed based on the code in the procedure.

And if you have set up a filter on database name, you will only get stored procedures from that database.