High amount of Full Table or Full Index Scan

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
PaoloProfiler 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.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Paolo Taverna" <nomail@.nomail.nml> wrote in message
news:Oe%23mzuCgIHA.3940@.TK2MSFTNGP05.phx.gbl...
> 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
>