Profiler trace event to capture referenced tables

  • 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

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • 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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

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