Better explanation of the "Scan:Started" Event's "Mode" data column in SQL Profiler?

A scan is a scan is not a full scan?
I am trying to monitor full table or clustered index scans. In SQL
Profiler, we have the "Scan:Started" event class. It has a
corresponding Mode data column that represents "Scan Mode" (duh). The
only information I have been able to find about what each of the modes
means is the very terse description for each mode supplied in SQL BOL.
Can anyone point me to better descriptions of these scan modes?
Maybe more importantly, what are the ones I should look out for that
represent full scans.
TIA,
-Peterit depends what you mean by full scan,
the entire contents of the table?
this would be table or clustered index scans, having
indexID 0 or 1
a scan could apply to just a nonclustered index, which is
all rows but not the full contents of the table
also, suppose your query was select top x,
the plan could show a scan, but stops after x rows are
retrieved
or are you wanting to exclude range scans? thats a term
used in other products, SQL Server considers retrieving an
indexed block of rows an index seek, not a scan
>--Original Message--
>A scan is a scan is not a full scan?
>I am trying to monitor full table or clustered index
scans. In SQL
>Profiler, we have the "Scan:Started" event class. It has
a
>corresponding Mode data column that represents "Scan
Mode" (duh). The
>only information I have been able to find about what each
of the modes
>means is the very terse description for each mode
supplied in SQL BOL.
> Can anyone point me to better descriptions of these scan
modes?
>Maybe more importantly, what are the ones I should look
out for that
>represent full scans.
>TIA,
>-Peter
>.
>|||I'm performace tuning and monitoring, so I am interested in
determining the "bad" kinds of scans. Even if the execution plan
shows only an index seek, there is still a "Scan:Started" event event
in profiler and a "Scan count 1" coming out of "SET STATISTICS IO" ON.
How dow I monitor for only the bad types of scans? I was guessing
that the Scan Mode in profiler was the right direction.
Where can I find a better description for the scan mode in profiler?
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message news:<062f01c3dbd7$b4af6130$a301280a@.phx.gbl>...
> it depends what you mean by full scan,
> the entire contents of the table?
> this would be table or clustered index scans, having
> indexID 0 or 1
> a scan could apply to just a nonclustered index, which is
> all rows but not the full contents of the table
> also, suppose your query was select top x,
> the plan could show a scan, but stops after x rows are
> retrieved
> or are you wanting to exclude range scans? thats a term
> used in other products, SQL Server considers retrieving an
> indexed block of rows an index seek, not a scan
> >--Original Message--
> >A scan is a scan is not a full scan?
> >
> >I am trying to monitor full table or clustered index
> scans. In SQL
> >Profiler, we have the "Scan:Started" event class. It has
> a
> >corresponding Mode data column that represents "Scan
> Mode" (duh). The
> >only information I have been able to find about what each
> of the modes
> >means is the very terse description for each mode
> supplied in SQL BOL.
> > Can anyone point me to better descriptions of these scan
> modes?
> >Maybe more importantly, what are the ones I should look
> out for that
> >represent full scans.
> >
> >TIA,
> >
> >-Peter
> >.
> >|||I'd still love a response to this one.
Thx.
nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0401160937.396fecf4@.posting.google.com>...
> I'm performace tuning and monitoring, so I am interested in
> determining the "bad" kinds of scans. Even if the execution plan
> shows only an index seek, there is still a "Scan:Started" event event
> in profiler and a "Scan count 1" coming out of "SET STATISTICS IO" ON.
> How dow I monitor for only the bad types of scans? I was guessing
> that the Scan Mode in profiler was the right direction.
> Where can I find a better description for the scan mode in profiler?
> "joe chang" <anonymous@.discussions.microsoft.com> wrote in message news:<062f01c3dbd7$b4af6130$a301280a@.phx.gbl>...
> > it depends what you mean by full scan,
> > the entire contents of the table?
> > this would be table or clustered index scans, having
> > indexID 0 or 1
> > a scan could apply to just a nonclustered index, which is
> > all rows but not the full contents of the table
> > also, suppose your query was select top x,
> > the plan could show a scan, but stops after x rows are
> > retrieved
> > or are you wanting to exclude range scans? thats a term
> > used in other products, SQL Server considers retrieving an
> > indexed block of rows an index seek, not a scan
> >
> > >--Original Message--
> > >A scan is a scan is not a full scan?
> > >
> > >I am trying to monitor full table or clustered index
> scans. In SQL
> > >Profiler, we have the "Scan:Started" event class. It has
> a
> > >corresponding Mode data column that represents "Scan
> Mode" (duh). The
> > >only information I have been able to find about what each
> of the modes
> > >means is the very terse description for each mode
> supplied in SQL BOL.
> > > Can anyone point me to better descriptions of these scan
> modes?
> > >Maybe more importantly, what are the ones I should look
> out for that
> > >represent full scans.
> > >
> > >TIA,
> > >
> > >-Peter
> > >.
> > >|||Instead of searching for "bad scans" you may want to instead look for
long-running queries with the profiler. After all, a sequential scan of a
small table is not really a "bad scan."
Once you have found the long-running queries, see what you can do to speed
them up.
If you want some automated assistance, use the index tuning wizard on a
tracefile you collect with the profiler.
Eric
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0402111609.180fabbb@.posting.google.com...
> I'd still love a response to this one.
> Thx.
> nospampedro@.yahoo.com (Peter Daniels) wrote in message
news:<2fd8f155.0401160937.396fecf4@.posting.google.com>...
> > I'm performace tuning and monitoring, so I am interested in
> > determining the "bad" kinds of scans. Even if the execution plan
> > shows only an index seek, there is still a "Scan:Started" event event
> > in profiler and a "Scan count 1" coming out of "SET STATISTICS IO" ON.
> > How dow I monitor for only the bad types of scans? I was guessing
> > that the Scan Mode in profiler was the right direction.
> >
> > Where can I find a better description for the scan mode in profiler?
> >
> > "joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:<062f01c3dbd7$b4af6130$a301280a@.phx.gbl>...
> > > it depends what you mean by full scan,
> > > the entire contents of the table?
> > > this would be table or clustered index scans, having
> > > indexID 0 or 1
> > > a scan could apply to just a nonclustered index, which is
> > > all rows but not the full contents of the table
> > > also, suppose your query was select top x,
> > > the plan could show a scan, but stops after x rows are
> > > retrieved
> > > or are you wanting to exclude range scans? thats a term
> > > used in other products, SQL Server considers retrieving an
> > > indexed block of rows an index seek, not a scan
> > >
> > > >--Original Message--
> > > >A scan is a scan is not a full scan?
> > > >
> > > >I am trying to monitor full table or clustered index
> > scans. In SQL
> > > >Profiler, we have the "Scan:Started" event class. It has
> > a
> > > >corresponding Mode data column that represents "Scan
> > Mode" (duh). The
> > > >only information I have been able to find about what each
> > of the modes
> > > >means is the very terse description for each mode
> > supplied in SQL BOL.
> > > > Can anyone point me to better descriptions of these scan
> > modes?
> > > >Maybe more importantly, what are the ones I should look
> > out for that
> > > >represent full scans.
> > > >
> > > >TIA,
> > > >
> > > >-Peter
> > > >.
> > > >|||Thanks for your response, but I want to use BOTH angles for my query
analysis. Duration is only one aspect of the performance of a query.
I understand that full scans on small tables may not be a bad thing,
but SQL Server Profiler provides the scan mode data column, so I just
want a better description of what those scan modes are that what is
provided in SQL BOL:
Scan mode. Can have these values:
1 = Normal
2 = First
4 = Back
8 = Unordered
16 = No data
32 = Reserved
64 = Exlatch
128 = Index supplied
256 = Marker
Thanks,
-Peter
nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0402111609.180fabbb@.posting.google.com>...
> I'd still love a response to this one.
> Thx.
> nospampedro@.yahoo.com (Peter Daniels) wrote in message news:<2fd8f155.0401160937.396fecf4@.posting.google.com>...
> > I'm performace tuning and monitoring, so I am interested in
> > determining the "bad" kinds of scans. Even if the execution plan
> > shows only an index seek, there is still a "Scan:Started" event event
> > in profiler and a "Scan count 1" coming out of "SET STATISTICS IO" ON.
> > How dow I monitor for only the bad types of scans? I was guessing
> > that the Scan Mode in profiler was the right direction.
> >
> > Where can I find a better description for the scan mode in profiler?
> >
> > "joe chang" <anonymous@.discussions.microsoft.com> wrote in message news:<062f01c3dbd7$b4af6130$a301280a@.phx.gbl>...
> > > it depends what you mean by full scan,
> > > the entire contents of the table?
> > > this would be table or clustered index scans, having
> > > indexID 0 or 1
> > > a scan could apply to just a nonclustered index, which is
> > > all rows but not the full contents of the table
> > > also, suppose your query was select top x,
> > > the plan could show a scan, but stops after x rows are
> > > retrieved
> > > or are you wanting to exclude range scans? thats a term
> > > used in other products, SQL Server considers retrieving an
> > > indexed block of rows an index seek, not a scan
> > >
> > > >--Original Message--
> > > >A scan is a scan is not a full scan?
> > > >
> > > >I am trying to monitor full table or clustered index
> scans. In SQL
> > > >Profiler, we have the "Scan:Started" event class. It has
> a
> > > >corresponding Mode data column that represents "Scan
> Mode" (duh). The
> > > >only information I have been able to find about what each
> of the modes
> > > >means is the very terse description for each mode
> supplied in SQL BOL.
> > > > Can anyone point me to better descriptions of these scan
> modes?
> > > >Maybe more importantly, what are the ones I should look
> out for that
> > > >represent full scans.
> > > >
> > > >TIA,
> > > >
> > > >-Peter
> > > >.
> > > >