Using Profiler to trace table scans

Question

Post reply

Using Profiler to trace table scans

alnj56

SSC Enthusiast

Points: 194
More actions
August 16, 2002 at 9:23 pm

#105118

Is there a way to link scan events captured in profiler to the query that caused the scan? I have been able to identify the table that is being scanned but not the query. BOL says that TransactionId is a data column for the scan:started class but the column is blank.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 1

Select the execution plans that will show on which table scan is happening........

Cheers,

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Include the objectid column in the trace, it will contain the object_id of the table scanned. Then use SELECT OBJECT_NAME(123456789) to get the name of the table, where 123456789 is of course the objectid from the scan event in the trace.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

alnj56 SSC Enthusiast Points: 194 More actions · Answer 3

I have captured the table name from the scan event and the TSQL events. I would like to somehow link the scan event with the cooresponding TSQL event that caused the scan. My goal is to review the SQL that caused the scan. I thought the TransactionID would allow me to do this but the column is blank.

TIA,

Jodie

Antares686 SSC Guru Points: 125444 More actions · Answer 4

If you add TSQL:StmtStarting I believe it will show up usually 1-3 records before the table scan event.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)