Identify Table Scan/Recompile Source

[benvegiard]

Using PerfMon, Profiler, and some other tools, I can see that Full Table Scans are popping off. However, I cannot find an easy way to determine WHICH queries are forcing these scans. My SQL2K db has about 1,900 procs in it! I am currently going through them based on things such as high duration, I/O and CPU costs, but it seems there should be a way to use profiler to see what is causing the table scans.

Additionally, I know that sometimes SQL Server will simply perform a table scan on “small” tables (tables with only a few hundred rows) rather than “bother” to use an appropriate index. Are these included in the Table Scan counters/flags?

As a side note, I have basically the exact same question regarding linking recompiles to their source procedures.

Thanks,
Ben

 

[ptheriault]

Ben,
It sounds like you have tables without Clustered Indexes. Since the data is actually stored in the leaf level of the Clustered index you should at worst get a clustered index scan. Tables that don't have a Clusterd index are called heap tables. Here is a query you can use to look for Heap tables.

SELECT sysobjects.name TableName,
   (SELECT rows FROM sysindexes
WHERE id = sysobjects.id
AND indid = 0) Rows
FROM sysobjects
WHERE type = 'U'
   AND OBJECTPROPERTY(sysobjects.id , 'TableHasIndex' ) = 0
ORDER BY TableName

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[benvegiard]

There are a handful of such tables that have high ratio of inserts of data that may be inserted in the middle of the list, therefore they do not have clustered indexes on them.

However, even tables w/clustered indexes can be "scanned" when the row-count is small. I kid you not. A MS engineer was here and turned me on to that.

At any rate, does anyone have a way to associate the scans/recompiles with the source table(s)?

Thanks,
Ben

 

[ptheriault]

Ben,
I haven't run into tables without clustered index being scanned but I'll take your word for it. I've certainly seen stranger things happen. The only thing I could suggest here is starting with the queries that have a high CPU time I would put them in QA to exec with show plan. Not very fast. Sorry.
: (

- Paul
- If at first you don't succeed, find out if the loser gets anything.