High amount of Full Table or Full Index Scan

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paolo Taverna - 07 Mar 2008 08:17 GMT

Hello
The Installation is SQL Server 2005 Enterprise Edition with 8 Processor.
With permon and the performance counter "SQLServer:Access Methods\Full
Scans/sec" i monitor the value 80 -180. Every time these values are there
the 8 CPU have a workload of 80 Percent in Total. With the SQL Server
Profiler i found the Query who made the Full Table/Index Scans over table's
with 8 Million rows.
So now i have to proof that these full table/index scan are the cause of the
high CPU Workload and the bad performance.

How can i use the DMV View's to analyze which "Select" are really causing
Full Table/Index scan ath the Moment. Are there tools that i can use for
these analyze ?

regards
Paolo

Reply to this Message

TheSQLGuru - 07 Mar 2008 19:23 GMT

Profiler is probably best for that task. If it is a sproc causing the big
reads you can either review the code and figure it out yourself (using
profile-captured input parameters for the sproc). You could also step down
to statement level tracing inside the sproc call, but be warned that this
can lead to significant performance hit for the profiler data.

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Hello
> The Installation is SQL Server 2005 Enterprise Edition with 8 Processor.
[quoted text clipped - 12 lines]
> regards
> Paolo

Reply to this Message