INFO/SUGGESTIONS Please : SQL performance (fragmented disk)

I'm a developer at my company, but I've worked with hardware quite abit at my last job. I'm finding it hard to talk to one of our system admins here.

I'm running into what looks like disk issues on our SQL Server, I know the DB files all reside on a large SCSI Raid, the only files on the RAID are the DB files, no OS or program files.

Also the disks have never been defragmented, he wants to say :

1. SCSI and RAID eliminate the impact of fragmentation.
2. SQL doesn't get impacted by physical fragmentation.

I know both to be false statements but don't want to start an argument, nor go over his head.

I would like to "find" some reputable articles dealing with disk fragmentation and SQL(database) performance..

If anyone has a good site that displays good information I would greatly appreciate it.

OR

If anyone has some personal experience with issues like this, please share them..

You may want to read the following white paper: 'Microsoft SQL Server 2000 Index Defragmentation Best Practices' (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx), which may give you more idea about fragmentation.

Thanks

Stephen

Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.
You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>
|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough
|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server
|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -

Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.

I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.

I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.

What could be at issue here? Is there something else that I caused in
changing the index and changing it back?

Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.

--
Andrew J. Kelly SQL MVP

"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?

Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.

But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ouch.
i hate these.

this is real world stuff, and this happens more then you think.

Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.

To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
--
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing issue, inconsistent performance

Hi -
Trying to chase down a baffling performance issue. Our database has been
running very slow lately. So we are performance tuning the database. In
doing so, we created a copy of our production database. In that database, I
changed one clustered index on a table to try to improve performance. I ran
one query - saw a slight improvement - but saw "lazy spool" in the execution
plan.
I tried to change it back to the original index by dropping the changed
index, and recreating the original index. I then ran the original query -
which now went from 5 seconds to 36 seconds.
I then ran DBCC REINDEX on that table. Performance of the query was still
markedly worse. I then reran the DBCC REINDEX on all tables, and then I
updated each tables statistics. Performance of that query has never returned
to the original 5 seconds.
What could be at issue here? Is there something else that I caused in
changing the index and changing it back?
Ideas much appreciated.You need to look at the query plan to see what it is doing. My guess is you
might be using a value the first time you call the query that forces table
scans.
Andrew J. Kelly SQL MVP
"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
> I
> changed one clustered index on a table to try to improve performance. I
> ran
> one query - saw a slight improvement - but saw "lazy spool" in the
> execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
> returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
>|||"Steph" <ss@.nospam.com> wrote in message
news:OY4MGMOMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> Hi -
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that database,
I
> changed one clustered index on a table to try to improve performance. I
ran
> one query - saw a slight improvement - but saw "lazy spool" in the
execution
> plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was still
> markedly worse. I then reran the DBCC REINDEX on all tables, and then I
> updated each tables statistics. Performance of that query has never
returned
> to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
>
I hope this is not a stupid question, but have you tried an UPDATE
STATISTICS and then running the query twice to check performance? I'm
assuming the query is SP or view, rather than passthrough|||Steph (ss@.nospam.com) writes:
> Trying to chase down a baffling performance issue. Our database has been
> running very slow lately. So we are performance tuning the database. In
> doing so, we created a copy of our production database. In that
> database, I changed one clustered index on a table to try to improve
> performance. I ran one query - saw a slight improvement - but saw "lazy
> spool" in the execution plan.
> I tried to change it back to the original index by dropping the changed
> index, and recreating the original index. I then ran the original query -
> which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables, and
> then I updated each tables statistics. Performance of that query has
> never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
Well, it could be that previously you had outdated statistics - which
this time caused SQL Server to draw the right conclusions. Once you
got the statistics up to date, the estimates came out wrong.
But without seeing queries, tables, index and query plans it's hard
to say. You will need to backtrack to the situation you started to
play with the index and compare the query plans.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steph wrote:
> Hi -
> Trying to chase down a baffling performance issue. Our database has
> been running very slow lately. So we are performance tuning the
> database. In doing so, we created a copy of our production database.
> In that database, I changed one clustered index on a table to try to
> improve performance. I ran one query - saw a slight improvement - but
> saw "lazy spool" in the execution plan.
> I tried to change it back to the original index by dropping the
> changed index, and recreating the original index. I then ran the
> original query - which now went from 5 seconds to 36 seconds.
> I then ran DBCC REINDEX on that table. Performance of the query was
> still markedly worse. I then reran the DBCC REINDEX on all tables,
> and then I updated each tables statistics. Performance of that query
> has never returned to the original 5 seconds.
> What could be at issue here? Is there something else that I caused in
> changing the index and changing it back?
> Ideas much appreciated.
Caching. Different execution plans due to differing filter criteria /
values...
robert
FUP to microsoft.public.sqlserver.server|||ouch.
i hate these.
this is real world stuff, and this happens more then you think.
Ultimately, the goal is to help the engine be teh most efficient. The
engine can be really obtuse, and sometimes you need to FORCE it to do
what is right over what it wants to do.
To do that, look at making things unique. Post some code and we can
help.

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karan
karanlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert
|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegr oups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>
|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert
|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>
|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
--
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
--
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
>> Forgive me but I don't know what "lac" means but please note that
>> partitioning in 2005 is primarily for data management not
>> performance. That does not mean that you can not get increased
>> performance from a proper partitioning scheme but that is usually not
>> the primary goal. Partitioning allow you to manage very large amounts
>> of data much more easily and efficient than if they were all in a
>> single table or partition.
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexes on SQL Server 7.0

I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.

The 2 tables are described as follows:

MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts

MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits

Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.

Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?

Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.

Thoughts? Suggestions? Thanks...On Mar 23, 12:08 pm, "Zamdrist" <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
>
The 2 tables are described as follows:
>
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
>
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
>
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
>
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
>
Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
>
Thoughts? Suggestions? Thanks...

Is this a reporting and analytical system or a transactional system?|||On Mar 23, 12:20 pm, "Brad" <Brad.Marsh...@.Teksouth.comwrote:

Quote:

Originally Posted by

On Mar 23, 12:08 pm, "Zamdrist" <zamdr...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.

>

Quote:

Originally Posted by

The 2 tables are described as follows:

>

Quote:

Originally Posted by

MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts

>

Quote:

Originally Posted by

MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits

>

Quote:

Originally Posted by

Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.

>

Quote:

Originally Posted by

Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?

>

Quote:

Originally Posted by

Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.

>

Quote:

Originally Posted by

Thoughts? Suggestions? Thanks...

>
Is this a reporting and analytical system or a transactional system?

Transactional|||"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1174669705.000774.151130@.n59g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

>I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
>
The 2 tables are described as follows:
>
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
>
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
>
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows.

Just so you know, these aren't very large tables.

However, I'd definitely agree you probably want some indexes.

However, the question you're asking is a bit too generic. You probably need
to look at what queries you're doing and optimize for those specifically.

And generally you want to find not necessarily the longest running queries,
but the ones called the most. If you have one query called 10 times a day
that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a day.

If you have one query called 10,000 times a day for a minute and optimize it
10%, you'll save 1000 minutes.

Quote:

Originally Posted by

>There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
>
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
>
Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
>
Thoughts? Suggestions? Thanks...
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>
Just so you know, these aren't very large tables.
>
However, I'd definitely agree you probably want some indexes.
>
However, the question you're asking is a bit too generic. You probably need
to look at what queries you're doing and optimize for those specifically.
>
And generally you want to find not necessarily the longest running queries,
but the ones called the most. If you have one query called 10 times a day
that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a day.
>
If you have one query called 10,000 times a day for a minute and optimize it
10%, you'll save 1000 minutes.

A million records isn't large? Ok.

An application I didn't write and have NO support for is accessing the
data, reading & writing to these tables. There *are* indexes but only
one each and only on the primary key field.

I have no access to the queries as the application is reading from the
tables, probably using in-line string & code queries. There are no
views or procedures used by the application (well very few and not in
this instance). For all I know it could be using "Select * From
TableName...".

There are only 4 fields in each table, so I suppose the query(ies)
could only so complex as four fields would allow for. I know what one
of the fields ties back into a more widely used table that does have
more indexes, ones that appear to be useful.

Thanks|||See inline

Zamdrist wrote:

Quote:

Originally Posted by

>
I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.

Don't expect SQL Server 2000 or 2005 to be any faster if you keep the
current table and index structures... The fact you are using SQL Server
7.0 is no reason to have poor performance.

Quote:

Originally Posted by

The 2 tables are described as follows:
>
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
>
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
>
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.

Those queries would most likely benefit from an index on this column.
But it depends on the selectivity of the column. If it is highly
selective (many different values), then an index is probably very
useful.

Quote:

Originally Posted by

Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?

It would surely be worth a try. Just keep in mind that there is a cost
associated with creating an additional index, because it will require
disk space, memory (for caching) and can affect concurrency because of
added blocking. Blocking could really hurt an OLTP system.

HTH,
Gert-Jan

Quote:

Originally Posted by

Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
>
Thoughts? Suggestions? Thanks...

|||"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1174675879.584481.208100@.l75g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>>
>Just so you know, these aren't very large tables.
>>
>However, I'd definitely agree you probably want some indexes.
>>
>However, the question you're asking is a bit too generic. You probably
>need
>to look at what queries you're doing and optimize for those specifically.
>>
>And generally you want to find not necessarily the longest running
>queries,
>but the ones called the most. If you have one query called 10 times a
>day
>that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a
>day.
>>
>If you have one query called 10,000 times a day for a minute and optimize
>it
>10%, you'll save 1000 minutes.

>
A million records isn't large? Ok.

Nah, rather trivial these days. ;-)

Quote:

Originally Posted by

>
An application I didn't write and have NO support for is accessing the
data, reading & writing to these tables. There *are* indexes but only
one each and only on the primary key field.

If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.

Quote:

Originally Posted by

>
I have no access to the queries as the application is reading from the
tables, probably using in-line string & code queries. There are no
views or procedures used by the application (well very few and not in
this instance). For all I know it could be using "Select * From
TableName...".

Ack.

In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.

Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.

You can also try (though generally I don't find it useful) the Index Wizard
in EM.

Quote:

Originally Posted by

>
There are only 4 fields in each table, so I suppose the query(ies)
could only so complex as four fields would allow for.

And any joins.

Quote:

Originally Posted by

I know what one
of the fields ties back into a more widely used table that does have
more indexes, ones that appear to be useful.
>
Thanks
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 23, 1:59 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>
If you have no access to these tables, you can't put indexes on it. So
perhaps I misunderstand.

No I have access to all the objects via Enterprise Manager, I just
don't have access to the application code to see what it is doing,
there are however only a handful of queries/procedures, and over 70+
tables, so I fear the worst.

Quote:

Originally Posted by

>
In any case, you probably CAN get this information via profiler and looking
at the queries as they come through the machine.
>
Also, there's some tools (only source I know of is via the SQL Server
Magazine website so they're copyrighted I believe) to follow wait
statistics, which can be VERY powerful to find out where your application is
doing a lot of querying.
>
You can also try (though generally I don't find it useful) the Index Wizard
in EM.

Thanks Greg.|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.comwrote in message
news:JkWMh.17031$Jl.7174@.newsread3.news.pas.earthl ink.net...

Quote:

Originally Posted by

"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1174675879.584481.208100@.l75g2000hse.googlegr oups.com...

Quote:

Originally Posted by

On Mar 23, 12:38 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>
Just so you know, these aren't very large tables.
>
However, I'd definitely agree you probably want some indexes.
>
However, the question you're asking is a bit too generic. You probably
need
to look at what queries you're doing and optimize for those

specifically.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>
And generally you want to find not necessarily the longest running
queries,
but the ones called the most. If you have one query called 10 times a
day
that runs for 10 minutes and optimize it 10%, you'll save 10 minutes a
day.
>
If you have one query called 10,000 times a day for a minute and

optimize

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

it
10%, you'll save 1000 minutes.

A million records isn't large? Ok.

>
Nah, rather trivial these days. ;-)

Does "trivial" mean easy or unimportant?|||"David Cressey" <cressey73@.verizon.netwrote in message
news:s2eNh.248$E46.187@.trndny09...

Quote:

Originally Posted by

>

Quote:

Originally Posted by

Quote:

Originally Posted by

A million records isn't large? Ok.

>>
>Nah, rather trivial these days. ;-)

>
Does "trivial" mean easy or unimportant?

No, in this case it means rather small which impacts how you approach
maintainence issues. And to some extent how you solve problems.

For example, for some databases, it may be "simpler" to simply through more
memory at the problem. For a database 10x the size, more memory might not
even make a dent.

Quote:

Originally Posted by

>
>

--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.comwrote in message
news:4fgNh.17267$Jl.14634@.newsread3.news.pas.earth link.net...

Quote:

Originally Posted by

"David Cressey" <cressey73@.verizon.netwrote in message
news:s2eNh.248$E46.187@.trndny09...

Quote:

Originally Posted by

Quote:

Originally Posted by

A million records isn't large? Ok.
>
Nah, rather trivial these days. ;-)

Does "trivial" mean easy or unimportant?

>
No, in this case it means rather small which impacts how you approach
maintainence issues. And to some extent how you solve problems.
>

I'd suggest that that's a misuse of the word "trivial", but that you might
have meant "small enough to be unimportant".

Quote:

Originally Posted by

For example, for some databases, it may be "simpler" to simply through

more

Quote:

Originally Posted by

memory at the problem. For a database 10x the size, more memory might not
even make a dent.

This is because a lot of the work involved in sorting and searching expands
non linearly with regard to volume of data (row cardinality in this case).
In every database I've worked on, the difference between a table scan and
an index lookup has resulted in a "nontrivial" performance difference with a
million rows in the table.

But it depends on what you mean by "non trivial", I suppose.|||Hi

Greg had very good advice that you may have missed if you have never used
SQL Profiler. SQL Profiler will show you the actual SQL code that is being
executed by the application (if it is inline SQL) or what Stored Procedures
are being called.

All this might be of limited usefulness if you don't have access to
programmers who can change poorly written code in the application. I am
thinking someone is perceiving "the appilcation is slow" and have decided to
kick the DB guy, when in fact the problem is with the application, Perhaps
the app is marching through ADO Recordsets to join data when it could be
done using basic SQL functionality.

For your original question if you have

MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts

MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits

MatterConflicts.MatterConflicts should be the Primary key of MatterConflicts
( think you said it was)

MatterConflictsHits.MatterConflictsHits should be the Primary key of
MatterConflicts ( think you said it was)

Add an index on MatterConflictHits.MatterConflicts this should speed up join
operations between the two tables.

Best of Luck.

-Dick Christoph

"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1174682594.964521.45320@.y66g2000hsf.googlegro ups.com...

Quote:

Originally Posted by

On Mar 23, 1:59 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:

Quote:

Originally Posted by

>>
>If you have no access to these tables, you can't put indexes on it. So
>perhaps I misunderstand.

>
No I have access to all the objects via Enterprise Manager, I just
don't have access to the application code to see what it is doing,
there are however only a handful of queries/procedures, and over 70+
tables, so I fear the worst.

Quote:

Originally Posted by

>>
>In any case, you probably CAN get this information via profiler and
>looking
>at the queries as they come through the machine.
>>
>Also, there's some tools (only source I know of is via the SQL Server
>Magazine website so they're copyrighted I believe) to follow wait
>statistics, which can be VERY powerful to find out where your application
>is
>doing a lot of querying.
>>
>You can also try (though generally I don't find it useful) the Index
>Wizard
>in EM.

>
Thanks Greg.
>

|||On Mar 23, 11:08 pm, "Zamdrist" <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
>
The 2 tables are described as follows:
>
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
>
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
>
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.

Drop the index on primary key and create a index on Matters field
instead since this is the column users are using to access data. Also,
Since these two tables are related, it will be worthwhile to consider
a index between the joining columns MatterConflicts of both tables.

Quote:

Originally Posted by

Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
>
Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.

no. Don't use composite indexs as they seldom are useful.

Quote:

Originally Posted by

Thoughts? Suggestions? Thanks...

By the way, these tables are fairly large and you are right that you
have started to address the performance issue.|||On 26 Mar 2007 21:29:32 -0700, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>Doesn't seem to be to be very beneficial to have an index that only
>includes the primary key. So for example I'm thinking of creating an
>index for MatterConflicts that includes the fields: MatterConflicts,
>Matters, and HitMatters.

>
>no. Don't use composite indexs as they seldom are useful.

Hi othellomy,

Do you have any source for that sweeping statement?

AFAIK, composite indexes can be VERY useful. For queries that filter or
join on two or more columns, a composite index is much better than
seperate indexes on the individual columns.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thank you all for the advice and suggestions!

I did run Profiler and found that my suspicious are correct, all
queries are being ran via in-line text statements, no application
procedures exist much less being used.

Quite a bit usage of sp_cursorprepare, exceute, close, etc. also.
Don't know if this is normal or not.

So yeah, basically I'm up a river with no paddle. We have no access to
the application code or the programmer, nor do we even have a support
license agreement anymore with the company who wrote the application.

I did end up archiving off most of the data from the tables in
question and wrote a custom interface to that using ASP.Net. Works
like a charm, no indexes whatsoever. Archiving and subsequently
deleting the data from the production tables, and rebuilding existing
indexes had little to no effect on performance unfortunately.

Again...up a river with no paddle, lol.

Thanks|||On Mar 30, 12:48 am, "Zamdrist" <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

>
I did end up archiving off most of the data from the tables in
question and wrote a custom interface to that using ASP.Net. Works
like a charm, no indexes whatsoever.

Does that mean who have no io problems if you use ASP.NET?

Archiving and subsequently

Quote:

Originally Posted by

deleting the data from the production tables, and rebuilding existing
indexes had little to no effect on performance unfortunately.
Again...up a river with no paddle, lol.

The indexes you have are useless. Therefore, rebuilding them will not
make any difference. You need to identify what columns (preferably
one, at most 2) you need your clustered indexes for. Hopefully the
system will use those indexes.|||On Mar 30, 4:54 am, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

On Mar 30, 12:48 am, "Zamdrist" <zamdr...@.gmail.comwrote:
>
>
>

Quote:

Originally Posted by

I did end up archiving off most of the data from the tables in
question and wrote a custom interface to that using ASP.Net. Works
like a charm, no indexes whatsoever.

>
Does that mean who have no io problems if you use ASP.NET?
>
Archiving and subsequently
>

Quote:

Originally Posted by

deleting the data from the production tables, and rebuilding existing
indexes had little to no effect on performance unfortunately.
Again...up a river with no paddle, lol.

>
The indexes you have are useless. Therefore, rebuilding them will not
make any difference. You need to identify what columns (preferably
one, at most 2) you need your clustered indexes for. Hopefully the
system will use those indexes.

I copied (and subsequently deleted) from production a large amount of
the data to a different table and server, and wrote an interface to
that data in ASP.Net so the users, if needed could look back on older
infrequently used data. Accessing that data works great.

I could build more intelligent indexes for the data in production,
although I doubt the application would behave any differently (faster)
as I've verified it uses no views or stored procedures...it only uses
in-line text based queries...Select fields From table Where...and so
on.

I have no access to the application code to change this behavior
unfortunately.|||On Mar 30, 5:57 pm, "Zamdrist" <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

On Mar 30, 4:54 am, othell...@.yahoo.com wrote:
>
>
>
>
>

Quote:

Originally Posted by

On Mar 30, 12:48 am, "Zamdrist" <zamdr...@.gmail.comwrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I did end up archiving off most of the data from the tables in
question and wrote a custom interface to that using ASP.Net. Works
like a charm, no indexes whatsoever.

>

Quote:

Originally Posted by

Does that mean who have no io problems if you use ASP.NET?

>

Quote:

Originally Posted by

Archiving and subsequently

>

Quote:

Originally Posted by

Quote:

Originally Posted by

deleting the data from the production tables, and rebuilding existing
indexes had little to no effect on performance unfortunately.
Again...up a river with no paddle, lol.

>

Quote:

Originally Posted by

The indexes you have are useless. Therefore, rebuilding them will not
make any difference. You need to identify what columns (preferably
one, at most 2) you need your clustered indexes for. Hopefully the
system will use those indexes.

>
I copied (and subsequently deleted) from production a large amount of
the data to a different table and server, and wrote an interface to
that data in ASP.Net so the users, if needed could look back on older
infrequently used data. Accessing that data works great.
>
I could build more intelligent indexes for the data in production,
although I doubt the application would behave any differently (faster)
as I've verified it uses no views or stored procedures...it only uses
in-line text based queries...Select fields From table Where...and so
on.
>
I have no access to the application code to change this behavior
unfortunately.- Hide quoted text -
>
- Show quoted text -

You might consider creating a new clustered index on a column that is
frequently used to join with other tables. Copy paste your code to QA
and run a showplan with no exec, on and find out if it is doing a
table scan or index search.|||On Mar 30, 6:11 am, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

>
You might consider creating a new clustered index on a column that is
frequently used to join with other tables. Copy paste your code to QA
and run a showplan with no exec, on and find out if it is doing a
table scan or index search.

There is an index already on each of the two tables in question, one
each on the primary key only, and it is clustered.

When I run my own query with Show Execution Plan on, I see that there
are several Clustered Index Scans, Index Scans & Parallelism/
Repartition operations.

But this is my code, running in QA. I have no control over how the
application accesses the data.|||Zamdrist wrote:

Quote:

Originally Posted by

On Mar 30, 6:11 am, othell...@.yahoo.com wrote:

Quote:

Originally Posted by

>You might consider creating a new clustered index on a column that is
>frequently used to join with other tables. Copy paste your code to QA
>and run a showplan with no exec, on and find out if it is doing a
>table scan or index search.

>
There is an index already on each of the two tables in question, one
each on the primary key only, and it is clustered.
>
When I run my own query with Show Execution Plan on, I see that there
are several Clustered Index Scans, Index Scans & Parallelism/
Repartition operations.
>
But this is my code, running in QA. I have no control over how the
application accesses the data.

If your query is the same as what the application runs, except for
specific values being plugged in here and there, then the application
will generally get the same execution plan that you do.

Does QA run your query reasonably quickly? Based on the Profiler
trace, does the application seem slow because it runs slow queries,
or because it runs an inefficiently large number of queries which
are reasonably fast individually?|||On Mar 30, 8:31 am, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

>
If your query is the same as what the application runs, except for
specific values being plugged in here and there, then the application
will generally get the same execution plan that you do.
>
Does QA run your query reasonably quickly? Based on the Profiler
trace, does the application seem slow because it runs slow queries,
or because it runs an inefficiently large number of queries which
are reasonably fast individually?

Here is the query I ran, it returns 70.8K rows in 2 seconds via QA:

Select M.MatterID From Matters M
Inner Join MatterConflicts MC On MC.Matters = M.Matters
Inner Join Matters M2 On M2.Matters = MC.HitMatters
Inner Join MatterConflictHits MCH On MCH.MatterConflicts =
MC.MatterConflicts

Now the application is doing all kinds of things, probably more
complicated than my query above. Honestly I don't know enough about
Profiler to isolate one operation. I did check it out and there are
many, many sp_cursorexecute, prepare and close statements, along with
many select fields from tables queries.

I dunno, I doubt I will be able to make any sort of significant impact
on performance without access to the code.

FYI: This program is a legal case management software called Prolaw by
Thomson-Elite...it *sucks* royally! LOL.|||On Mar 30, 8:51 pm, "Zamdrist" <zamdr...@.gmail.comwrote:

Quote:

Originally Posted by

On Mar 30, 8:31 am, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

If your query is the same as what the application runs, except for
specific values being plugged in here and there, then the application
will generally get the same execution plan that you do.

>

Quote:

Originally Posted by

Does QA run your query reasonably quickly? Based on the Profiler
trace, does the application seem slow because it runs slow queries,
or because it runs an inefficiently large number of queries which
are reasonably fast individually?

>
Here is the query I ran, it returns 70.8K rows in 2 seconds via QA:
>
Select M.MatterID From Matters M
Inner Join MatterConflicts MC On MC.Matters = M.Matters
Inner Join Matters M2 On M2.Matters = MC.HitMatters
Inner Join MatterConflictHits MCH On MCH.MatterConflicts =
MC.MatterConflicts
>
Now the application is doing all kinds of things, probably more
complicated than my query above. Honestly I don't know enough about
Profiler to isolate one operation. I did check it out and there are
many, many sp_cursorexecute, prepare and close statements, along with
many select fields from tables queries.
>
I dunno, I doubt I will be able to make any sort of significant impact
on performance without access to the code.
>
FYI: This program is a legal case management software called Prolaw by
Thomson-Elite...it *sucks* royally! LOL.

Cursors are slow. If it is opening cursors on tables with million rows
then I am afraid there is not much you can do. Besides, opening a
cursor on a table with million records even with indexes can be slow
and I don't think the application developers had performance issue on
their mind when they wrote the code initially. Besides, handling most
of the processing on client side without stored procedures will also
slow things down considerably especially for large tables.|||"Zamdrist" <zamdrist@.gmail.comwrote in message
news:1174669705.000774.151130@.n59g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

>I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
>
The 2 tables are described as follows:
>
MatterConflicts:
Fields: MatterConflicts varchar(16), Matters varchar(16), HitMatters
varchar(16), IsInclude varchar(1)
Index: MatterConflicts
>
MatterConflictHits:
Fields: MatterConflictHits varchar(16), MatterConflicts varchar(16),
ColumnLabel varchar(40), Hit varchar(100)
Index: MatterConflictHits
>
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
>
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
>
Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
>
Thoughts? Suggestions? Thanks...

What are you using for hardware? Something from the year 1999? Do you have
at least 512MB of RAM in it? With 512MB of RAM, unless there's a lot more
tolerably large tables involved, your entire database should fit into main
memory, which is a good place to have it. If you don't already have enough
memory on the box to comfortably fit SQL Server, the OS and any other
ancillary processes into main memory, I'd recommend you buy some. Buy a
gigabyte. In fact, if it's a decent quality server, buy a gigabyte in 4 X
256MB cards, which will usually allow maximum memory interleaving on good
hardware. Memory, nowadays, costs very little - compared to your time.

In terms of contemporary WIntel servers, your database really isn't all that
large. Guessing at like 75% use of the varchar() fields in volved, you've
got something less than 200MB in those two tables. Can you sneak a new
server in under the database? Would the app run against a SQL Server 2000
back end?

I've got a two or three year old, 4 X 700mhz CPU server here, with 16GB of
memory, and if I run a select count(*) from <tablewhere <check something
that's not indexedon a 9 million row, 5GB table, it takes 27 seconds the
first time I execute that command and the second time I run that it takes 4
seconds (the second time, the table is in memory). You don't need that much
server to get similar performance.

--
Posted via a free Usenet account from http://www.teranews.com

Indexes getting fragmented very quickly

We have been running SQL Server 2000 on a production machine for years.
Within the last 6 months, we have noticed a performance problem. The
system will suddenly slow to a crawl. We run DBCC Reindex for all of
the tables in the our database, and things go back to normal - running
very quickly. We are currently having to do this multiple times a day.
We have verified the indexes are getting fragmented by using the DBCC
showcontig. What will cause the indexes to get fragmented so quickly?
How do we prevent this from happening?
Any help would greatly be appreciated.
Thank you!
Jayme> What will cause the indexes to get fragmented so quickly?
Some possibilities:
Lots of inserts over a key that is no monotonically increasing.
Lost of updates (where value in index key changes).
Shrinking of database files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jayme" <jayloub@.comcast.net> wrote in message
news:1150568837.067145.140050@.c74g2000cwc.googlegroups.com...
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>|||Jayme wrote:
> We have been running SQL Server 2000 on a production machine for years.
> Within the last 6 months, we have noticed a performance problem. The
> system will suddenly slow to a crawl. We run DBCC Reindex for all of
> the tables in the our database, and things go back to normal - running
> very quickly. We are currently having to do this multiple times a day.
> We have verified the indexes are getting fragmented by using the DBCC
> showcontig. What will cause the indexes to get fragmented so quickly?
> How do we prevent this from happening?
> Any help would greatly be appreciated.
> Thank you!
> Jayme
>
Are you sure you're seeing index fragmentation, and not disk
fragmentation? Post the output of your DBCC command..|||Tracy McKibben wrote:
> Jayme wrote:
> > We have been running SQL Server 2000 on a production machine for years.
> > Within the last 6 months, we have noticed a performance problem. The
> > system will suddenly slow to a crawl. We run DBCC Reindex for all of
> > the tables in the our database, and things go back to normal - running
> > very quickly. We are currently having to do this multiple times a day.
> > We have verified the indexes are getting fragmented by using the DBCC
> > showcontig. What will cause the indexes to get fragmented so quickly?
> > How do we prevent this from happening?
> >
> > Any help would greatly be appreciated.
> > Thank you!
> > Jayme
> >
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..
Check out for disk fragmentation also.
If Index fragmentation , then BOL has very good sample, in DBCC
Showconting topic.
It will check index fragmentation and defrag index which are fragmented
below threshold value. Run it regularly.
Regards
Amish Shah|||This is the dbcc output before the reindex is done:
DBCC SHOWCONTIG scanning 'QCSKUTable' table...
Table: 'QCSKUTable' (32771224); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 533
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 9.01%
- Extent Scan Fragmentation ...............: 72.06%
- Avg. Bytes Free per Page................: 789.2
- Avg. Page Density (full)................: 90.25%
DBCC SHOWCONTIG scanning 'Map' table...
Table: 'Map' (101575400); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6089.0
- Avg. Page Density (full)................: 24.77%
DBCC SHOWCONTIG scanning 'Zone' table...
Table: 'Zone' (133575514); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 70
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.8
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 837.4
- Avg. Page Density (full)................: 89.65%
DBCC SHOWCONTIG scanning 'QCConfiguration' table...
Table: 'QCConfiguration' (192771794); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6976.0
- Avg. Page Density (full)................: 13.81%
DBCC SHOWCONTIG scanning 'TestQCSKUTable' table...
Table: 'TestQCSKUTable' (230395990); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 535
- Extents Scanned.......................: 68
- Extent Switches.......................: 67
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.53% [67:68]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 67.65%
- Avg. Bytes Free per Page................: 786.5
- Avg. Page Density (full)................: 90.28%
DBCC SHOWCONTIG scanning 'Bay' table...
Table: 'Bay' (325576198); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 20.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2084.0
- Avg. Page Density (full)................: 74.25%
DBCC SHOWCONTIG scanning 'ZoneMap' table...
Table: 'ZoneMap' (357576312); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 309
- Extents Scanned.......................: 39
- Extent Switches.......................: 38
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [39:39]
- Logical Scan Fragmentation ..............: 0.32%
- Extent Scan Fragmentation ...............: 69.23%
- Avg. Bytes Free per Page................: 799.9
- Avg. Page Density (full)................: 90.12%
DBCC SHOWCONTIG scanning 'Device' table...
Table: 'Device' (421576540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 293
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.34%
- Extent Scan Fragmentation ...............: 81.08%
- Avg. Bytes Free per Page................: 779.5
- Avg. Page Density (full)................: 90.37%
DBCC SHOWCONTIG scanning 'Location' table...
Table: 'Location' (501576825); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 285
- Extents Scanned.......................: 36
- Extent Switches.......................: 35
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [36:36]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 5.56%
- Avg. Bytes Free per Page................: 786.2
- Avg. Page Density (full)................: 90.29%
DBCC SHOWCONTIG scanning 'Carrier' table...
Table: 'Carrier' (580913141); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 232
- Extents Scanned.......................: 34
- Extent Switches.......................: 33
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 85.29% [29:34]
- Extent Scan Fragmentation ...............: 94.12%
- Avg. Bytes Free per Page................: 1359.3
- Avg. Page Density (full)................: 83.21%
DBCC SHOWCONTIG scanning 'NetController' table...
Table: 'NetController' (581577110); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 33.33%
- Avg. Bytes Free per Page................: 1356.0
- Avg. Page Density (full)................: 83.25%
DBCC SHOWCONTIG scanning 'QCContentDetail' table...
Table: 'QCContentDetail' (596249229); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 6
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.3
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 3306.5
- Avg. Page Density (full)................: 59.15%
DBCC SHOWCONTIG scanning 'OrderToCarrier' table...
Table: 'OrderToCarrier' (628913312); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 139
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 58.06% [18:31]
- Extent Scan Fragmentation ...............: 96.77%
- Avg. Bytes Free per Page................: 1491.1
- Avg. Page Density (full)................: 81.58%
DBCC SHOWCONTIG scanning 'WaveGroup' table...
Table: 'WaveGroup' (645577338); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7988.0
- Avg. Page Density (full)................: 1.31%
DBCC SHOWCONTIG scanning 'WorkLoadCriteriaDetail' table...
Table: 'WorkLoadCriteriaDetail' (654625375); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 9
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 4.5
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 970.7
- Avg. Page Density (full)................: 88.01%
DBCC SHOWCONTIG scanning 'Products' table...
Table: 'Products' (660913426); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8056.0
- Avg. Page Density (full)................: 0.47%
DBCC SHOWCONTIG scanning 'CloseToteData' table...
Table: 'CloseToteData' (691025743); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 17307
- Extents Scanned.......................: 2167
- Extent Switches.......................: 2166
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.86% [2164:2167]
- Extent Scan Fragmentation ...............: 18.00%
- Avg. Bytes Free per Page................: 356.5
- Avg. Page Density (full)................: 95.60%
DBCC SHOWCONTIG scanning 'OrderState' table...
Table: 'OrderState' (692913540); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7561.0
- Avg. Page Density (full)................: 6.59%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (709577566); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4796.0
- Avg. Page Density (full)................: 40.75%
DBCC SHOWCONTIG scanning 'LNG' table...
Table: 'LNG' (715149593); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 25
- Extents Scanned.......................: 4
- Extent Switches.......................: 3
- Avg. Pages per Extent..................: 6.3
- Scan Density [Best Count:Actual Count]......: 100.00% [4:4]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 916.6
- Avg. Page Density (full)................: 88.68%
DBCC SHOWCONTIG scanning 'WaveState' table...
Table: 'WaveState' (724913654); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7659.0
- Avg. Page Density (full)................: 5.37%
DBCC SHOWCONTIG scanning 'PCS_Object' table...
Table: 'PCS_Object' (747149707); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 905.0
- Avg. Page Density (full)................: 88.82%
DBCC SHOWCONTIG scanning 'WaveByProduct' table...
Table: 'WaveByProduct' (756913768); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 5
- Extent Switches.......................: 4
- Avg. Pages per Extent..................: 1.6
- Scan Density [Best Count:Actual Count]......: 20.00% [1:5]
- Extent Scan Fragmentation ...............: 80.00%
- Avg. Bytes Free per Page................: 7734.6
- Avg. Page Density (full)................: 4.44%
DBCC SHOWCONTIG scanning 'LineItem' table...
Table: 'LineItem' (762902235); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 5894
- Extents Scanned.......................: 745
- Extent Switches.......................: 744
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.93% [737:745]
- Extent Scan Fragmentation ...............: 89.13%
- Avg. Bytes Free per Page................: 1303.0
- Avg. Page Density (full)................: 83.90%
DBCC SHOWCONTIG scanning 'PCS_User' table...
Table: 'PCS_User' (779149821); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 1251.7
- Avg. Page Density (full)................: 84.54%
DBCC SHOWCONTIG scanning 'Wave' table...
Table: 'Wave' (788913882); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 8
- Extents Scanned.......................: 7
- Extent Switches.......................: 6
- Avg. Pages per Extent..................: 1.1
- Scan Density [Best Count:Actual Count]......: 14.29% [1:7]
- Extent Scan Fragmentation ...............: 57.14%
- Avg. Bytes Free per Page................: 7723.8
- Avg. Page Density (full)................: 4.57%
DBCC SHOWCONTIG scanning 'PDSynchronize' table...
Table: 'PDSynchronize' (794902349); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 3.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 66.67%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3770.7
- Avg. Page Density (full)................: 53.41%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroup' table...
Table: 'PCS_PermissionGroup' (811149935); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7319.0
- Avg. Page Density (full)................: 9.57%
DBCC SHOWCONTIG scanning 'Order' table...
Table: 'Order' (836914053); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 364
- Extents Scanned.......................: 55
- Extent Switches.......................: 54
- Avg. Pages per Extent..................: 6.6
- Scan Density [Best Count:Actual Count]......: 83.64% [46:55]
- Extent Scan Fragmentation ...............: 98.18%
- Avg. Bytes Free per Page................: 1263.0
- Avg. Page Density (full)................: 84.40%
DBCC SHOWCONTIG scanning 'Task' table...
Table: 'Task' (837578022); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 296
- Extents Scanned.......................: 42
- Extent Switches.......................: 43
- Avg. Pages per Extent..................: 7.0
- Scan Density [Best Count:Actual Count]......: 84.09% [37:44]
- Logical Scan Fragmentation ..............: 0.68%
- Extent Scan Fragmentation ...............: 64.29%
- Avg. Bytes Free per Page................: 843.2
- Avg. Page Density (full)................: 89.58%
DBCC SHOWCONTIG scanning 'PCS_Component' table...
Table: 'PCS_Component' (843150049); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 5189.0
- Avg. Page Density (full)................: 35.89%
DBCC SHOWCONTIG scanning 'QCEventLog' table...
Table: 'QCEventLog' (843918128); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3603
- Extents Scanned.......................: 465
- Extent Switches.......................: 464
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 96.99% [451:465]
- Extent Scan Fragmentation ...............: 36.99%
- Avg. Bytes Free per Page................: 575.1
- Avg. Page Density (full)................: 92.89%
DBCC SHOWCONTIG scanning 'OrderLine' table...
Table: 'OrderLine' (884914224); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6298
- Extents Scanned.......................: 798
- Extent Switches.......................: 797
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 98.75% [788:798]
- Extent Scan Fragmentation ...............: 98.37%
- Avg. Bytes Free per Page................: 255.3
- Avg. Page Density (full)................: 96.85%
DBCC SHOWCONTIG scanning 'PCS_PermissionGroupHasPL1' table...
Table: 'PCS_PermissionGroupHasPL1' (891150220); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2068.0
- Avg. Page Density (full)................: 74.45%
DBCC SHOWCONTIG scanning 'QCErrors' table...
Table: 'QCErrors' (923918413); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 10
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 5.0
- Scan Density [Best Count:Actual Count]......: 100.00% [2:2]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 848.3
- Avg. Page Density (full)................: 89.52%
DBCC SHOWCONTIG scanning 'StateTransitionTimes' table...
Table: 'StateTransitionTimes' (932914395); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 590
- Extents Scanned.......................: 100
- Extent Switches.......................: 99
- Avg. Pages per Extent..................: 5.9
- Scan Density [Best Count:Actual Count]......: 74.00% [74:100]
- Extent Scan Fragmentation ...............: 91.00%
- Avg. Bytes Free per Page................: 599.8
- Avg. Page Density (full)................: 92.59%
DBCC SHOWCONTIG scanning 'PCS_UserInPermissionGroup' table...
Table: 'PCS_UserInPermissionGroup' (955150448); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 33.33%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 869.0
- Avg. Page Density (full)................: 89.26%
DBCC SHOWCONTIG scanning 'PickMission' table...
Table: 'PickMission' (964914509); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 3950
- Extents Scanned.......................: 499
- Extent Switches.......................: 498
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.00% [494:499]
- Extent Scan Fragmentation ...............: 99.20%
- Avg. Bytes Free per Page................: 279.3
- Avg. Page Density (full)................: 96.55%
DBCC SHOWCONTIG scanning 'Mission' table...
Table: 'Mission' (965578478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 346
- Extents Scanned.......................: 52
- Extent Switches.......................: 53
- Avg. Pages per Extent..................: 6.7
- Scan Density [Best Count:Actual Count]......: 81.48% [44:54]
- Logical Scan Fragmentation ..............: 1.16%
- Extent Scan Fragmentation ...............: 82.69%
- Avg. Bytes Free per Page................: 876.8
- Avg. Page Density (full)................: 89.17%
DBCC SHOWCONTIG scanning 'QCMasterList' table...
Table: 'QCMasterList' (1003918698); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 984
- Extents Scanned.......................: 128
- Extent Switches.......................: 133
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 91.79% [123:134]
- Logical Scan Fragmentation ..............: 1.12%
- Extent Scan Fragmentation ...............: 64.06%
- Avg. Bytes Free per Page................: 753.2
- Avg. Page Density (full)................: 90.69%
DBCC SHOWCONTIG scanning 'PCS_Client' table...
Table: 'PCS_Client' (1019150676); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7427.0
- Avg. Page Density (full)................: 8.24%
DBCC SHOWCONTIG scanning 'PCS_ClientRestriction' table...
Table: 'PCS_ClientRestriction' (1051150790); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7780.0
- Avg. Page Density (full)................: 3.88%
DBCC SHOWCONTIG scanning 'PickMissionComplete' table...
Table: 'PickMissionComplete' (1060914851); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1661
- Extents Scanned.......................: 461
- Extent Switches.......................: 460
- Avg. Pages per Extent..................: 3.6
- Scan Density [Best Count:Actual Count]......: 45.12% [208:461]
- Extent Scan Fragmentation ...............: 66.81%
- Avg. Bytes Free per Page................: 2926.2
- Avg. Page Density (full)................: 63.85%
DBCC SHOWCONTIG scanning 'UISCFG_Band' table...
Table: 'UISCFG_Band' (1083150904); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6640.0
- Avg. Page Density (full)................: 17.96%
DBCC SHOWCONTIG scanning 'CurrentActivity' table...
Table: 'CurrentActivity' (1093578934); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 743.0
- Avg. Page Density (full)................: 90.82%
DBCC SHOWCONTIG scanning 'UISCFG_ComponentEntry' table...
Table: 'UISCFG_ComponentEntry' (1115151018); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 2.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 816.0
- Avg. Page Density (full)................: 89.92%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1156915193); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7690.0
- Avg. Page Density (full)................: 4.99%
DBCC SHOWCONTIG scanning 'UISCFG_CE_on_Band' table...
Table: 'UISCFG_CE_on_Band' (1163151189); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4881.0
- Avg. Page Density (full)................: 39.70%
DBCC SHOWCONTIG scanning 'UISCFG_Treeview' table...
Table: 'UISCFG_Treeview' (1227151417); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 3117.0
- Avg. Page Density (full)................: 61.49%
DBCC SHOWCONTIG scanning 'UISCFG_Events' table...
Table: 'UISCFG_Events' (1307151702); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8051.0
- Avg. Page Density (full)................: 0.53%
DBCC SHOWCONTIG scanning 'IAS_AutoInteraction' table...
Table: 'IAS_AutoInteraction' (1323151759); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 2797.0
- Avg. Page Density (full)................: 65.44%
DBCC SHOWCONTIG scanning 'WorkLoadRollup' table...
Table: 'WorkLoadRollup' (1332199796); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2370
- Extents Scanned.......................: 298
- Extent Switches.......................: 297
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.66% [297:298]
- Logical Scan Fragmentation ..............: 9.66%
- Extent Scan Fragmentation ...............: 61.07%
- Avg. Bytes Free per Page................: 696.5
- Avg. Page Density (full)................: 91.39%
DBCC SHOWCONTIG scanning 'MissionDetail' table...
Table: 'MissionDetail' (1349579846); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1494
- Extents Scanned.......................: 189
- Extent Switches.......................: 855
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 21.85% [187:856]
- Logical Scan Fragmentation ..............: 27.58%
- Extent Scan Fragmentation ...............: 89.42%
- Avg. Bytes Free per Page................: 4327.5
- Avg. Page Density (full)................: 46.54%
DBCC SHOWCONTIG scanning 'UISCFG_SystemParameters' table...
Table: 'UISCFG_SystemParameters' (1355151873); index ID: 1, database
ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7984.0
- Avg. Page Density (full)................: 1.36%
DBCC SHOWCONTIG scanning 'UISCFG_ProductInfo' table...
Table: 'UISCFG_ProductInfo' (1387151987); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8048.0
- Avg. Page Density (full)................: 0.57%
DBCC SHOWCONTIG scanning 'UISCFG_UserPreferences' table...
Table: 'UISCFG_UserPreferences' (1419152101); index ID: 1, database ID:
6
TABLE level scan performed.
- Pages Scanned........................: 3
- Extents Scanned.......................: 3
- Extent Switches.......................: 2
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 33.33% [1:3]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 66.67%
- Avg. Bytes Free per Page................: 1943.7
- Avg. Page Density (full)................: 75.99%
DBCC SHOWCONTIG scanning 'dtproperties' table...
Table: 'dtproperties' (1451152215); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7567.0
- Avg. Page Density (full)................: 6.51%
DBCC SHOWCONTIG scanning 'Configuration' table...
Table: 'Configuration' (1573580644); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7290.0
- Avg. Page Density (full)................: 9.93%
DBCC SHOWCONTIG scanning 'Instruction' table...
Table: 'Instruction' (1605580758); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 4248.0
- Avg. Page Density (full)................: 47.52%
DBCC SHOWCONTIG scanning 'Message' table...
Table: 'Message' (1669580986); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 2
- Extents Scanned.......................: 2
- Extent Switches.......................: 1
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 50.00% [1:2]
- Logical Scan Fragmentation ..............: 50.00%
- Extent Scan Fragmentation ...............: 50.00%
- Avg. Bytes Free per Page................: 3633.0
- Avg. Page Density (full)................: 55.11%
DBCC SHOWCONTIG scanning 'SequenceNumbers' table...
Table: 'SequenceNumbers' (1765581328); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6080.0
- Avg. Page Density (full)................: 24.88%
DBCC SHOWCONTIG scanning '_bufferlog' table...
Table: '_bufferlog' (1807306194); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 212
- Extents Scanned.......................: 31
- Extent Switches.......................: 30
- Avg. Pages per Extent..................: 6.8
- Scan Density [Best Count:Actual Count]......: 87.10% [27:31]
- Extent Scan Fragmentation ...............: 58.06%
- Avg. Bytes Free per Page................: 331.4
- Avg. Page Density (full)................: 95.91%
DBCC SHOWCONTIG scanning 'Technology' table...
Table: 'Technology' (1877581727); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7983.0
- Avg. Page Density (full)................: 1.37%
DBCC SHOWCONTIG scanning 'PostImportMap' table...
Table: 'PostImportMap' (1925581898); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7818.0
- Avg. Page Density (full)................: 3.41%
DBCC SHOWCONTIG scanning 'ImportErrors' table...
Table: 'ImportErrors' (1941581955); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7992.0
- Avg. Page Density (full)................: 1.26%
DBCC SHOWCONTIG scanning 'System' table...
Table: 'System' (2057058364); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 8077.0
- Avg. Page Density (full)................: 0.21%
DBCC SHOWCONTIG scanning 'WorkArea' table...
Table: 'WorkArea' (2089058478); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7970.0
- Avg. Page Density (full)................: 1.53%
DBCC SHOWCONTIG scanning 'ImportMap' table...
Table: 'ImportMap' (2112726579); index ID: 0, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7742.0
- Avg. Page Density (full)................: 4.35%
DBCC SHOWCONTIG scanning 'QCCurrentActivity' table...
Table: 'QCCurrentActivity' (2116254644); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 100.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 7940.0
- Avg. Page Density (full)................: 1.90%
DBCC SHOWCONTIG scanning 'Module' table...
Table: 'Module' (2137058649); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6848.0
- Avg. Page Density (full)................: 15.39%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Tracy McKibben wrote:
> Jayme wrote:
> > We have been running SQL Server 2000 on a production machine for years.
> > Within the last 6 months, we have noticed a performance problem. The
> > system will suddenly slow to a crawl. We run DBCC Reindex for all of
> > the tables in the our database, and things go back to normal - running
> > very quickly. We are currently having to do this multiple times a day.
> > We have verified the indexes are getting fragmented by using the DBCC
> > showcontig. What will cause the indexes to get fragmented so quickly?
> > How do we prevent this from happening?
> >
> > Any help would greatly be appreciated.
> > Thank you!
> > Jayme
> >
> Are you sure you're seeing index fragmentation, and not disk
> fragmentation? Post the output of your DBCC command..|||Jayme wrote:
> This is the dbcc output before the reindex is done:
>
Ahh yes, I see lots of high "Extent Scan Fragmentation" values,
indicating external fragmentation, i.e. DISK fragmentation:
http://www.sql-server-performance.com/rd_index_fragmentation.asp
Some things you can do:
1. Never shrink the database. It's going to grow again, and repeated
shrink/growth operations will cause disk fragmentation.
2. Size the database properly to accommodate your needs to several
months, to avoid auto-growth
3. After sizing the DB properly, schedule an outage where you can shut
down SQL Server and run a full disk defrag. Copy the database files
(mdf and ldf) to another volume, use the Windows defragger to defrag the
drive, then copy the data files back, one at a time.
4. Now that the external fragmentation has been resolved, rebuild the
indexes.|||Jayme wrote:
> OK, I defrag'd the disk. Rebuilt the indexes. It still looks like
> there is some extent fragmentation for some of the tables. The
> database is set up in 3 files, one for data, one for index, one for
> log. Could that cause it to show more extent fragmentation?
>
Having the indexes in a seperate file should not cause more
fragmentation, assuming the file is sized properly and is not
auto-growing or shrinking.
I have to ask, did you stop SQL Server before running your defrag? You
can't defrag the database files while SQL is running.|||Yes I stopped SQL Server.
Tracy McKibben wrote:
> Jayme wrote:
> > OK, I defrag'd the disk. Rebuilt the indexes. It still looks like
> > there is some extent fragmentation for some of the tables. The
> > database is set up in 3 files, one for data, one for index, one for
> > log. Could that cause it to show more extent fragmentation?
> >
> Having the indexes in a seperate file should not cause more
> fragmentation, assuming the file is sized properly and is not
> auto-growing or shrinking.
> I have to ask, did you stop SQL Server before running your defrag? You
> can't defrag the database files while SQL is running.|||Jayme wrote:
> Yes I stopped SQL Server.
>
You said you have seperate files for data, indexes, and logs. Is it
possible that these few indexes span multiple files, i.e. they share
space in your data file and the index file? Certain values reported by
SHOWCONTIG are unreliable in such cases.|||It is possible. We try to keep all the indexes in one file, but it
could happen.
Tracy McKibben wrote:
> Jayme wrote:
> > Yes I stopped SQL Server.
> >
> >
> You said you have seperate files for data, indexes, and logs. Is it
> possible that these few indexes span multiple files, i.e. they share
> space in your data file and the index file? Certain values reported by
> SHOWCONTIG are unreliable in such cases.|||Don't even bother about fragmentation unless the index has at least 1000
pages otherwise you're hardly going to notice a difference. form your
earlier output, none of your indexes have more than about 50 pages. The only
larger table you have is 'LineItem' and that's a heap so fragmentation is
irrelevant.
Why are you concerned about fragmentation?
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jayme" <jayloub@.comcast.net> wrote in message
news:1151071190.962959.17680@.i40g2000cwc.googlegroups.com...
> It is possible. We try to keep all the indexes in one file, but it
> could happen.
> Tracy McKibben wrote:
>> Jayme wrote:
>> > Yes I stopped SQL Server.
>> >
>> >
>> You said you have seperate files for data, indexes, and logs. Is it
>> possible that these few indexes span multiple files, i.e. they share
>> space in your data file and the index file? Certain values reported by
>> SHOWCONTIG are unreliable in such cases.
>|||It seems to have something to do with fragmentation of the indexes.
When we reindex the tables - the slowness immediately goes away.
What else could be happening that a reindex would fix?
Paul S Randal [MS] wrote:
> Don't even bother about fragmentation unless the index has at least 1000
> pages otherwise you're hardly going to notice a difference. form your
> earlier output, none of your indexes have more than about 50 pages. The only
> larger table you have is 'LineItem' and that's a heap so fragmentation is
> irrelevant.
> Why are you concerned about fragmentation?
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstorageengine/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1151071190.962959.17680@.i40g2000cwc.googlegroups.com...
> > It is possible. We try to keep all the indexes in one file, but it
> > could happen.
> >
> > Tracy McKibben wrote:
> >> Jayme wrote:
> >> > Yes I stopped SQL Server.
> >> >
> >> >
> >>
> >> You said you have seperate files for data, indexes, and logs. Is it
> >> possible that these few indexes span multiple files, i.e. they share
> >> space in your data file and the index file? Certain values reported by
> >> SHOWCONTIG are unreliable in such cases.
> >