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
> > > >.
> > > >

Best way to push this data? (LONG)

There's a lot of explanation involved in this, so bear with the long
post:
Our company has offices in the US and China, and an AS400 (where the
data originates) in the US, and 3 SQL boxes (which mirror the AS400
data, with the odd extra field thrown in) - 2 in the US, and one in
China.
We're changing our item number scheme from a numeric field to a 25-
character char field. We're doing this over Memorial Day weekend...
we're dropping replication and everything on Friday, and then the AS400
starts its conversion process, and sometime late Saturday or early
Sunday the conversion process will be done on the AS400, at which point
we start copying the data over to our local SQL box, and then over to
China.
China starts its day Sunday night at 7pm. For that day, they will be in
read-only mode, so that they won't be writing changes to their old,
outdated tables. The downside to being read-only is we also cannot push
data to them (since they're using the data) until their end-of-day at
approximately 4am Monday. This gives us a very small window to push the
data over, re-establish replication, make sure everything works fine,
etc. By the way, I'm talking about several gig worth of data, at least
12gb and as much as 20gb.
One of the suggestions was to create a scratch-db containing all the
data, and disconnecting that db and transferring the mdf & ldf files
over the pond during the middle of our night, so at 5am they would be
sitting there ready for us. We could then detach our existing db, and
attach the new one in its place. The problem with that suggestion is
that once we re-establish replication, how would it know which data
already exists in China? Our fear is that replication will drop and re-
create the tables and start pushing from scratch, which defeats the
purpose of pushing the mdf/ldf files.
Another suggestion was to set up another box over there with SQL, and
putting the tables there and establishing replication to that machine...
and when China stopped for the day, changing the DNS mappings to point
to the new SQL box instead of the old box. However that suggestion
makes too much sense, and therefore will probably be shot down by
management.
I guess the nutshell version of my question is this: What's the fastest
way to get that large an amount of data over to China, and ready to
replicate without error?
Regards,
ScottI don't envy your job, but here goes a couple of
sugestions.
1) Don't send over your files. Back up the entire
database up, zip up the file then send that file over to
China. In China unzip it then use the restore from file
option.
Advantage - One file, reasonable safe transfer
Disadvantage - Restores are not the most stable of things.
Large file size being transfered. You will lose the extra
fields.
2) Change your replication to Transactional on the server
which is going to perform the upgrade. Ensure that the
location where the transactional files is accessable to
all servers.
Advantage - This will only log the changes to the data so
file size is not too big, it sould be faster to transfer.
Disadvantage - Its replication, and is sometimes not very
easy to do.
3) You can just send over the datafile, rather than both.
There is a command something on the lines of
db_attach_single_file (check it up on bol), again zip it
up and send it.
As you can see each has its advantages and disadvatages.
Personally I would go for 3 if there is time after the
update to send it to china before china comes on read-
write, otherwise 2, replication.
Anyway keep me posted on what happens. My email is
little_flowery_me@.hotmail.com.
J
>--Original Message--
>There's a lot of explanation involved in this, so bear
with the long
>post:
>Our company has offices in the US and China, and an
AS400 (where the
>data originates) in the US, and 3 SQL boxes (which
mirror the AS400
>data, with the odd extra field thrown in) - 2 in the US,
and one in
>China.
>We're changing our item number scheme from a numeric
field to a 25-
>character char field. We're doing this over Memorial
Day weekend...
>we're dropping replication and everything on Friday, and
then the AS400
>starts its conversion process, and sometime late
Saturday or early
>Sunday the conversion process will be done on the AS400,
at which point
>we start copying the data over to our local SQL box, and
then over to
>China.
>China starts its day Sunday night at 7pm. For that day,
they will be in
>read-only mode, so that they won't be writing changes to
their old,
>outdated tables. The downside to being read-only is we
also cannot push
>data to them (since they're using the data) until their
end-of-day at
>approximately 4am Monday. This gives us a very small
window to push the
>data over, re-establish replication, make sure
everything works fine,
>etc. By the way, I'm talking about several gig worth of
data, at least
>12gb and as much as 20gb.
>One of the suggestions was to create a scratch-db
containing all the
>data, and disconnecting that db and transferring the mdf
& ldf files
>over the pond during the middle of our night, so at 5am
they would be
>sitting there ready for us. We could then detach our
existing db, and
>attach the new one in its place. The problem with that
suggestion is
>that once we re-establish replication, how would it know
which data
>already exists in China? Our fear is that replication
will drop and re-
>create the tables and start pushing from scratch, which
defeats the
>purpose of pushing the mdf/ldf files.
>Another suggestion was to set up another box over there
with SQL, and
>putting the tables there and establishing replication to
that machine...
>and when China stopped for the day, changing the DNS
mappings to point
>to the new SQL box instead of the old box. However that
suggestion
>makes too much sense, and therefore will probably be
shot down by
>management.
>I guess the nutshell version of my question is this:
What's the fastest
>way to get that large an amount of data over to China,
and ready to
>replicate without error?
>Regards,
>Scott
>.
>