Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Profiler trace event to capture referenced tables Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 2:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:30 AM
Points: 380, Visits: 895
Hi All
if I run a query like this on database A:
select * from databaseB.dbo.Table1
and if I run a trace with filter on database DatabaseB, and I select the StmtComplete event, then any hits to DatabaseB are not captured by trace.
How can I capture the hits on DatabaseB by keeping the same filter?
thx


-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1508590
Posted Saturday, October 26, 2013 4:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 13,872, Visits: 28,270
Assuming you're running on 2008 since this is a forum for that, I don't recommend trace. Instead, use extended events. The events to capture queries against the database are rpc_completed and batch_completed. Trying to capture statement completed events is a very expensive operation (there can be lots and lots of statements) which you should only do in a very highly filtered manner.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1508650
Posted Sunday, October 27, 2013 1:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:22 AM
Points: 40, Visits: 477
I run into this problem often. Databasename filter is based on the database name specified in the connection Initial Catalog. Maybe you can use a host name or application name filter instead if you want to capture specific application queries.

I am still not using extended events, but I also read that this is the way to go if your using SQL 2008 or above.


__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
Post #1508689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse