SQL Server Performance Forum – Threads Archive

Capturing full table scans

Hi, How do i capture queries that use full table scans? i tried using Profiler but it doesn’t really show any of the queries. is there any other way/software that can be of use? Raymond

Profiler will show if any scans are performed. From one of the tips about PERFMON counters, it lists :
To find out how many table scans your server is performing, use the SQL Server Access Methods Object: Full Scans/sec. Note that this counter is for an entire server, not just a single database. One thing you will notice with this counter is that there often appears to a pattern of scans occurring periodically. In many cases, these are table scans SQL Server is performing on a regular basis for internal use.
What you want to look for are the random table scans that represent your application. If you see what you consider to be an inordinate number of table scans, then break out the Profiler and Index Tuning Wizard to help you determine exactly what is causing them, and if adding any indexes can help reduce the table scans. Of course, SQL may just be doing its job well, and performing table scans instead of using indexes because it is just plain more efficient. [6.5, 7.0, 2000] HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I did try using the Profiler, selected ‘Scans’ in the Events tab… when i run the trace, the only thing i see under Event Class is ‘Scan: Started’, and ‘Scan: Stopped’. In the Text Data column, there’s nothing… how do i get the query that is causing sql server to perform a table scan?

Make a note of ObjectID & IndexID for that trace from data columns.
By monitoring the Index ID default data column, you can determine the identification number of the index being used by a specific query. May also use PERFMON if only one database is involved for the fullscan. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks a bunch for the info! Raymond

]]>