Need help on Effective Reindexing Stragtegy.

microsoft.public.sqlserver.server

25 Mar 2005 6:21 PM

MZeeshan

Hello-

We have a database supporting a vertical solution. Following are some of the
important details:

OS: Windows 2003 Server (sp?? I am not sure but can get that info if needed)
DB: SQL Server 2000 / sp3A

Data Device Size = 125 GB
Log Device Size = 25 GB (most of the time it remains free with hourly
transaction log backups)

Size of Fragmented data device = 86.09 GB
Size after defragmentation = 76.78

Total Number of tables: ~ 1,900 (some temporary but majority of them are
empty!)

This uneven data distribution is also evident from Index defragmentation
(DBCC DBREINDEX/offline method)processing times:

Largest Table: 2 hours 29 minutes (27% of the total processing time)
Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)

The timing trend is similar when I used the online/INDEXDEFRAG method as
well. The server also processes web requests from an e-commerce site and will
be actively on 24 x 7 schedule in coming 6 months.

We have already discarded offline method as it results in transaction
failures. The online method is quite long (takes more than twice as much time
~ 18-20 hours compared to 8-10 hours for offline method... understandably!).

Another worry is the excessive log generation for both offline and online
methods (its contrary to our thinking for at least online method). We take
hourly transaction log backups and nearly 200 GB of logs are genarted while
the index defrag process ran in the test environment. Being test, we deleted
all of them but its another worry in production environment.

Suggestions are welcome.

P.S. Please inform me if you are looking for some information that I have
not provided in the text.

--
Regards,
MZeeshan

25 Mar 2005 6:42 PM

Andrew J. Kelly

> Size of Fragmented data device = 86.09 GB
> Size after defragmentation = 76.78

What exactly do you mean by data device and why is the size important to
you?

> Total Number of tables: ~ 1,900 (some temporary but majority of them are
> empty!)

Why do you keep all those empty tables around and why are you using
temporary tables in the main db?

What does the DDL (including indexes) look like for the 2 largest tables
with fragmentation issues?
It sounds as if you have the clustered index on a column that either changes
or is a random value and causes page splits. I can tell more after looking
at the DDL and can give a better suited answer as well. YOu may be able to
adjust your fill factors to minimize fragmentation as well.

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"MZeeshan" <mzeeshan@community.nospam> wrote in message
news:102CB403-22A1-42B5-8A9C-8AAF23D5BEEA@microsoft.com...
> Hello-
>
> We have a database supporting a vertical solution. Following are some of
> the
> important details:
>
> OS: Windows 2003 Server (sp?? I am not sure but can get that info if
> needed)
> DB: SQL Server 2000 / sp3A
>
> Data Device Size = 125 GB
> Log Device Size = 25 GB (most of the time it remains free with hourly
> transaction log backups)
>
> Size of Fragmented data device = 86.09 GB
> Size after defragmentation = 76.78
>
> Total Number of tables: ~ 1,900 (some temporary but majority of them are
> empty!)
>
> This uneven data distribution is also evident from Index defragmentation
> (DBCC DBREINDEX/offline method)processing times:
>
> Largest Table: 2 hours 29 minutes (27% of the total processing time)
> Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
> Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
>
> The timing trend is similar when I used the online/INDEXDEFRAG method as
> well. The server also processes web requests from an e-commerce site and
> will
> be actively on 24 x 7 schedule in coming 6 months.
>
> We have already discarded offline method as it results in transaction
> failures. The online method is quite long (takes more than twice as much
> time
> ~ 18-20 hours compared to 8-10 hours for offline method...
> understandably!).
>
> Another worry is the excessive log generation for both offline and online
> methods (its contrary to our thinking for at least online method). We take
> hourly transaction log backups and nearly 200 GB of logs are genarted
> while
> the index defrag process ran in the test environment. Being test, we
> deleted
> all of them but its another worry in production environment.
>
> Suggestions are welcome.
>
> P.S. Please inform me if you are looking for some information that I have
> not provided in the text.
>
>
>
> --
> Regards,
> MZeeshan

Are all your drivers up to date? click for free checkup

25 Mar 2005 6:47 PM

Jeff Robinson

MZeeshan-

I would start be trying to figure out why the indexes become so fragemented.
What fill settings are you using?

Are the underlying tables subject to high levels of modifications
(particularly inserts or updates of indexes fields)?

Are you using large keyed clustered indexes and supporting additional
indicies on the same table?

If you are able to reduce the fragmentation the de-fragmentation will take
much less time.

Show quoteHide quote

"MZeeshan" wrote:

> Hello-
>
> We have a database supporting a vertical solution. Following are some of the
> important details:
>
> OS: Windows 2003 Server (sp?? I am not sure but can get that info if needed)
> DB: SQL Server 2000 / sp3A
>
> Data Device Size = 125 GB
> Log Device Size = 25 GB (most of the time it remains free with hourly
> transaction log backups)
>
> Size of Fragmented data device = 86.09 GB
> Size after defragmentation = 76.78
>
> Total Number of tables: ~ 1,900 (some temporary but majority of them are
> empty!)
>
> This uneven data distribution is also evident from Index defragmentation
> (DBCC DBREINDEX/offline method)processing times:
>
> Largest Table: 2 hours 29 minutes (27% of the total processing time)
> Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
> Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
>
> The timing trend is similar when I used the online/INDEXDEFRAG method as
> well. The server also processes web requests from an e-commerce site and will
> be actively on 24 x 7 schedule in coming 6 months.
>
> We have already discarded offline method as it results in transaction
> failures. The online method is quite long (takes more than twice as much time
> ~ 18-20 hours compared to 8-10 hours for offline method... understandably!).
>
> Another worry is the excessive log generation for both offline and online
> methods (its contrary to our thinking for at least online method). We take
> hourly transaction log backups and nearly 200 GB of logs are genarted while
> the index defrag process ran in the test environment. Being test, we deleted
> all of them but its another worry in production environment.
>
> Suggestions are welcome.
>
> P.S. Please inform me if you are looking for some information that I have
> not provided in the text.
>
>
>
> --
> Regards,
> MZeeshan

25 Mar 2005 8:19 PM

MZeeshan

Thanks for the reply.

For us, this vertical solution is like a black box (a can't do much here).
Similarly, the reason why there is this fragmentation is due to the code was
ported from a mainframe application... and is not very efficient. Now, its
another whole story why it cannot be fixed. Let's assume that option is not
open.

One approach I am also pursuing is the archiving/purging of some historical
data, that is expected to help.

That leaves problems that I am facing with index defragmentation.

Does this answer your question?

Show quoteHide quote

"Jeff Robinson" wrote:

> MZeeshan-
>
> I would start be trying to figure out why the indexes become so fragemented.
> What fill settings are you using?
>
> Are the underlying tables subject to high levels of modifications
> (particularly inserts or updates of indexes fields)?
>
> Are you using large keyed clustered indexes and supporting additional
> indicies on the same table?
>
> If you are able to reduce the fragmentation the de-fragmentation will take
> much less time.
>
>
> "MZeeshan" wrote:
>
> > Hello-
> >
> > We have a database supporting a vertical solution. Following are some of the
> > important details:
> >
> > OS: Windows 2003 Server (sp?? I am not sure but can get that info if needed)
> > DB: SQL Server 2000 / sp3A
> >
> > Data Device Size = 125 GB
> > Log Device Size = 25 GB (most of the time it remains free with hourly
> > transaction log backups)
> >
> > Size of Fragmented data device = 86.09 GB
> > Size after defragmentation = 76.78
> >
> > Total Number of tables: ~ 1,900 (some temporary but majority of them are
> > empty!)
> >
> > This uneven data distribution is also evident from Index defragmentation
> > (DBCC DBREINDEX/offline method)processing times:
> >
> > Largest Table: 2 hours 29 minutes (27% of the total processing time)
> > Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> > Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
> > Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
> >
> > The timing trend is similar when I used the online/INDEXDEFRAG method as
> > well. The server also processes web requests from an e-commerce site and will
> > be actively on 24 x 7 schedule in coming 6 months.
> >
> > We have already discarded offline method as it results in transaction
> > failures. The online method is quite long (takes more than twice as much time
> > ~ 18-20 hours compared to 8-10 hours for offline method... understandably!).
> >
> > Another worry is the excessive log generation for both offline and online
> > methods (its contrary to our thinking for at least online method). We take
> > hourly transaction log backups and nearly 200 GB of logs are genarted while
> > the index defrag process ran in the test environment. Being test, we deleted
> > all of them but its another worry in production environment.
> >
> > Suggestions are welcome.
> >
> > P.S. Please inform me if you are looking for some information that I have
> > not provided in the text.
> >
> >
> >
> > --
> > Regards,
> > MZeeshan

25 Mar 2005 9:23 PM

Jeff Robinson

I would suggest decreasing the fill factor for the indicies since they seem
to be fragmenting quickly. This will also improve the performance of the
INDEXDEFRAG it runs better when logical fragmentation is low.
Also INDEXDEFRAG is single-threaded ( won't make use of multiprocessors ),
if you have a server with multiple processors consider running one
INDEXDEFRAG for each processor simultaneously to reduce overall execution
time.

Show quoteHide quote

"MZeeshan" wrote:

> Thanks for the reply.
>
> For us, this vertical solution is like a black box (a can't do much here).
> Similarly, the reason why there is this fragmentation is due to the code was
> ported from a mainframe application... and is not very efficient. Now, its
> another whole story why it cannot be fixed. Let's assume that option is not
> open.
>
> One approach I am also pursuing is the archiving/purging of some historical
> data, that is expected to help.
>
> That leaves problems that I am facing with index defragmentation.
>
> Does this answer your question?
>
> "Jeff Robinson" wrote:
>
> > MZeeshan-
> >
> > I would start be trying to figure out why the indexes become so fragemented.
> > What fill settings are you using?
> >
> > Are the underlying tables subject to high levels of modifications
> > (particularly inserts or updates of indexes fields)?
> >
> > Are you using large keyed clustered indexes and supporting additional
> > indicies on the same table?
> >
> > If you are able to reduce the fragmentation the de-fragmentation will take
> > much less time.
> >
> >
> > "MZeeshan" wrote:
> >
> > > Hello-
> > >
> > > We have a database supporting a vertical solution. Following are some of the
> > > important details:
> > >
> > > OS: Windows 2003 Server (sp?? I am not sure but can get that info if needed)
> > > DB: SQL Server 2000 / sp3A
> > >
> > > Data Device Size = 125 GB
> > > Log Device Size = 25 GB (most of the time it remains free with hourly
> > > transaction log backups)
> > >
> > > Size of Fragmented data device = 86.09 GB
> > > Size after defragmentation = 76.78
> > >
> > > Total Number of tables: ~ 1,900 (some temporary but majority of them are
> > > empty!)
> > >
> > > This uneven data distribution is also evident from Index defragmentation
> > > (DBCC DBREINDEX/offline method)processing times:
> > >
> > > Largest Table: 2 hours 29 minutes (27% of the total processing time)
> > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
> > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
> > >
> > > The timing trend is similar when I used the online/INDEXDEFRAG method as
> > > well. The server also processes web requests from an e-commerce site and will
> > > be actively on 24 x 7 schedule in coming 6 months.
> > >
> > > We have already discarded offline method as it results in transaction
> > > failures. The online method is quite long (takes more than twice as much time
> > > ~ 18-20 hours compared to 8-10 hours for offline method... understandably!).
> > >
> > > Another worry is the excessive log generation for both offline and online
> > > methods (its contrary to our thinking for at least online method). We take
> > > hourly transaction log backups and nearly 200 GB of logs are genarted while
> > > the index defrag process ran in the test environment. Being test, we deleted
> > > all of them but its another worry in production environment.
> > >
> > > Suggestions are welcome.
> > >
> > > P.S. Please inform me if you are looking for some information that I have
> > > not provided in the text.
> > >
> > >
> > >
> > > --
> > > Regards,
> > > MZeeshan

25 Mar 2005 11:27 PM

MZeeshan

We are running on a quad processor machine and the processor utilization
always remain very low; only one processor is utilized.

This is something I am looking to find for some time. How come I tell the
index defrag process which processor to use?

Show quoteHide quote

"Jeff Robinson" wrote:

> I would suggest decreasing the fill factor for the indicies since they seem
> to be fragmenting quickly. This will also improve the performance of the
> INDEXDEFRAG it runs better when logical fragmentation is low.
> Also INDEXDEFRAG is single-threaded ( won't make use of multiprocessors ),
> if you have a server with multiple processors consider running one
> INDEXDEFRAG for each processor simultaneously to reduce overall execution
> time.
>
>
> "MZeeshan" wrote:
>
> > Thanks for the reply.
> >
> > For us, this vertical solution is like a black box (a can't do much here).
> > Similarly, the reason why there is this fragmentation is due to the code was
> > ported from a mainframe application... and is not very efficient. Now, its
> > another whole story why it cannot be fixed. Let's assume that option is not
> > open.
> >
> > One approach I am also pursuing is the archiving/purging of some historical
> > data, that is expected to help.
> >
> > That leaves problems that I am facing with index defragmentation.
> >
> > Does this answer your question?
> >
> > "Jeff Robinson" wrote:
> >
> > > MZeeshan-
> > >
> > > I would start be trying to figure out why the indexes become so fragemented.
> > > What fill settings are you using?
> > >
> > > Are the underlying tables subject to high levels of modifications
> > > (particularly inserts or updates of indexes fields)?
> > >
> > > Are you using large keyed clustered indexes and supporting additional
> > > indicies on the same table?
> > >
> > > If you are able to reduce the fragmentation the de-fragmentation will take
> > > much less time.
> > >
> > >
> > > "MZeeshan" wrote:
> > >
> > > > Hello-
> > > >
> > > > We have a database supporting a vertical solution. Following are some of the
> > > > important details:
> > > >
> > > > OS: Windows 2003 Server (sp?? I am not sure but can get that info if needed)
> > > > DB: SQL Server 2000 / sp3A
> > > >
> > > > Data Device Size = 125 GB
> > > > Log Device Size = 25 GB (most of the time it remains free with hourly
> > > > transaction log backups)
> > > >
> > > > Size of Fragmented data device = 86.09 GB
> > > > Size after defragmentation = 76.78
> > > >
> > > > Total Number of tables: ~ 1,900 (some temporary but majority of them are
> > > > empty!)
> > > >
> > > > This uneven data distribution is also evident from Index defragmentation
> > > > (DBCC DBREINDEX/offline method)processing times:
> > > >
> > > > Largest Table: 2 hours 29 minutes (27% of the total processing time)
> > > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> > > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
> > > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
> > > >
> > > > The timing trend is similar when I used the online/INDEXDEFRAG method as
> > > > well. The server also processes web requests from an e-commerce site and will
> > > > be actively on 24 x 7 schedule in coming 6 months.
> > > >
> > > > We have already discarded offline method as it results in transaction
> > > > failures. The online method is quite long (takes more than twice as much time
> > > > ~ 18-20 hours compared to 8-10 hours for offline method... understandably!).
> > > >
> > > > Another worry is the excessive log generation for both offline and online
> > > > methods (its contrary to our thinking for at least online method). We take
> > > > hourly transaction log backups and nearly 200 GB of logs are genarted while
> > > > the index defrag process ran in the test environment. Being test, we deleted
> > > > all of them but its another worry in production environment.
> > > >
> > > > Suggestions are welcome.
> > > >
> > > > P.S. Please inform me if you are looking for some information that I have
> > > > not provided in the text.
> > > >
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > MZeeshan

26 Mar 2005 1:01 AM

Andrew J. Kelly

Indexdefrag always uses a single process since it is a single threaded
operation and operates mainly on the page level. DBreindex can use all
available processors but you need to have Enterprise edition to take full
advantage of it. I suspect you are using Std Edition.

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"MZeeshan" <mzeeshan@community.nospam> wrote in message
news:0028ED78-A2B4-49D5-9AC8-D89A6D3C6741@microsoft.com...
> We are running on a quad processor machine and the processor utilization
> always remain very low; only one processor is utilized.
>
> This is something I am looking to find for some time. How come I tell the
> index defrag process which processor to use?
>
>
>
> "Jeff Robinson" wrote:
>
>> I would suggest decreasing the fill factor for the indicies since they
>> seem
>> to be fragmenting quickly. This will also improve the performance of the
>> INDEXDEFRAG it runs better when logical fragmentation is low.
>> Also INDEXDEFRAG is single-threaded ( won't make use of
>> multiprocessors ),
>> if you have a server with multiple processors consider running one
>> INDEXDEFRAG for each processor simultaneously to reduce overall execution
>> time.
>>
>>
>> "MZeeshan" wrote:
>>
>> > Thanks for the reply.
>> >
>> > For us, this vertical solution is like a black box (a can't do much
>> > here).
>> > Similarly, the reason why there is this fragmentation is due to the
>> > code was
>> > ported from a mainframe application... and is not very efficient. Now,
>> > its
>> > another whole story why it cannot be fixed. Let's assume that option is
>> > not
>> > open.
>> >
>> > One approach I am also pursuing is the archiving/purging of some
>> > historical
>> > data, that is expected to help.
>> >
>> > That leaves problems that I am facing with index defragmentation.
>> >
>> > Does this answer your question?
>> >
>> > "Jeff Robinson" wrote:
>> >
>> > > MZeeshan-
>> > >
>> > > I would start be trying to figure out why the indexes become so
>> > > fragemented.
>> > > What fill settings are you using?
>> > >
>> > > Are the underlying tables subject to high levels of modifications
>> > > (particularly inserts or updates of indexes fields)?
>> > >
>> > > Are you using large keyed clustered indexes and supporting additional
>> > > indicies on the same table?
>> > >
>> > > If you are able to reduce the fragmentation the de-fragmentation will
>> > > take
>> > > much less time.
>> > >
>> > >
>> > > "MZeeshan" wrote:
>> > >
>> > > > Hello-
>> > > >
>> > > > We have a database supporting a vertical solution. Following are
>> > > > some of the
>> > > > important details:
>> > > >
>> > > > OS: Windows 2003 Server (sp?? I am not sure but can get that info
>> > > > if needed)
>> > > > DB: SQL Server 2000 / sp3A
>> > > >
>> > > > Data Device Size = 125 GB
>> > > > Log Device Size = 25 GB (most of the time it remains free with
>> > > > hourly
>> > > > transaction log backups)
>> > > >
>> > > > Size of Fragmented data device = 86.09 GB
>> > > > Size after defragmentation = 76.78
>> > > >
>> > > > Total Number of tables: ~ 1,900 (some temporary but majority of
>> > > > them are
>> > > > empty!)
>> > > >
>> > > > This uneven data distribution is also evident from Index
>> > > > defragmentation
>> > > > (DBCC DBREINDEX/offline method)processing times:
>> > > >
>> > > > Largest Table: 2 hours 29 minutes (27% of the total processing
>> > > > time)
>> > > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
>> > > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing
>> > > > time)
>> > > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
>> > > >
>> > > > The timing trend is similar when I used the online/INDEXDEFRAG
>> > > > method as
>> > > > well. The server also processes web requests from an e-commerce
>> > > > site and will
>> > > > be actively on 24 x 7 schedule in coming 6 months.
>> > > >
>> > > > We have already discarded offline method as it results in
>> > > > transaction
>> > > > failures. The online method is quite long (takes more than twice as
>> > > > much time
>> > > > ~ 18-20 hours compared to 8-10 hours for offline method...
>> > > > understandably!).
>> > > >
>> > > > Another worry is the excessive log generation for both offline and
>> > > > online
>> > > > methods (its contrary to our thinking for at least online method).
>> > > > We take
>> > > > hourly transaction log backups and nearly 200 GB of logs are
>> > > > genarted while
>> > > > the index defrag process ran in the test environment. Being test,
>> > > > we deleted
>> > > > all of them but its another worry in production environment.
>> > > >
>> > > > Suggestions are welcome.
>> > > >
>> > > > P.S. Please inform me if you are looking for some information that
>> > > > I have
>> > > > not provided in the text.
>> > > >
>> > > >
>> > > >
>> > > > --
>> > > > Regards,
>> > > > MZeeshan

26 Mar 2005 10:35 AM

MZeeshan

You are correct... we are on standard edition. I am not aware of the
intricacies of the MSDN Universal Subscription but we just got this... at
least I think we can use for testing purposes (I'll check with my procurement
department but if you have any information please do share).

But... what I am really interested is finding out the performance
improvement that comes with it (for a quad processor machine)?

Show quoteHide quote

"Andrew J. Kelly" wrote:

> Indexdefrag always uses a single process since it is a single threaded
> operation and operates mainly on the page level. DBreindex can use all
> available processors but you need to have Enterprise edition to take full
> advantage of it. I suspect you are using Std Edition.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "MZeeshan" <mzeeshan@community.nospam> wrote in message
> news:0028ED78-A2B4-49D5-9AC8-D89A6D3C6741@microsoft.com...
> > We are running on a quad processor machine and the processor utilization
> > always remain very low; only one processor is utilized.
> >
> > This is something I am looking to find for some time. How come I tell the
> > index defrag process which processor to use?
> >
> >
> >
> > "Jeff Robinson" wrote:
> >
> >> I would suggest decreasing the fill factor for the indicies since they
> >> seem
> >> to be fragmenting quickly. This will also improve the performance of the
> >> INDEXDEFRAG it runs better when logical fragmentation is low.
> >> Also INDEXDEFRAG is single-threaded ( won't make use of
> >> multiprocessors ),
> >> if you have a server with multiple processors consider running one
> >> INDEXDEFRAG for each processor simultaneously to reduce overall execution
> >> time.
> >>
> >>
> >> "MZeeshan" wrote:
> >>
> >> > Thanks for the reply.
> >> >
> >> > For us, this vertical solution is like a black box (a can't do much
> >> > here).
> >> > Similarly, the reason why there is this fragmentation is due to the
> >> > code was
> >> > ported from a mainframe application... and is not very efficient. Now,
> >> > its
> >> > another whole story why it cannot be fixed. Let's assume that option is
> >> > not
> >> > open.
> >> >
> >> > One approach I am also pursuing is the archiving/purging of some
> >> > historical
> >> > data, that is expected to help.
> >> >
> >> > That leaves problems that I am facing with index defragmentation.
> >> >
> >> > Does this answer your question?
> >> >
> >> > "Jeff Robinson" wrote:
> >> >
> >> > > MZeeshan-
> >> > >
> >> > > I would start be trying to figure out why the indexes become so
> >> > > fragemented.
> >> > > What fill settings are you using?
> >> > >
> >> > > Are the underlying tables subject to high levels of modifications
> >> > > (particularly inserts or updates of indexes fields)?
> >> > >
> >> > > Are you using large keyed clustered indexes and supporting additional
> >> > > indicies on the same table?
> >> > >
> >> > > If you are able to reduce the fragmentation the de-fragmentation will
> >> > > take
> >> > > much less time.
> >> > >
> >> > >
> >> > > "MZeeshan" wrote:
> >> > >
> >> > > > Hello-
> >> > > >
> >> > > > We have a database supporting a vertical solution. Following are
> >> > > > some of the
> >> > > > important details:
> >> > > >
> >> > > > OS: Windows 2003 Server (sp?? I am not sure but can get that info
> >> > > > if needed)
> >> > > > DB: SQL Server 2000 / sp3A
> >> > > >
> >> > > > Data Device Size = 125 GB
> >> > > > Log Device Size = 25 GB (most of the time it remains free with
> >> > > > hourly
> >> > > > transaction log backups)
> >> > > >
> >> > > > Size of Fragmented data device = 86.09 GB
> >> > > > Size after defragmentation = 76.78
> >> > > >
> >> > > > Total Number of tables: ~ 1,900 (some temporary but majority of
> >> > > > them are
> >> > > > empty!)
> >> > > >
> >> > > > This uneven data distribution is also evident from Index
> >> > > > defragmentation
> >> > > > (DBCC DBREINDEX/offline method)processing times:
> >> > > >
> >> > > > Largest Table: 2 hours 29 minutes (27% of the total processing
> >> > > > time)
> >> > > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
> >> > > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing
> >> > > > time)
> >> > > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
> >> > > >
> >> > > > The timing trend is similar when I used the online/INDEXDEFRAG
> >> > > > method as
> >> > > > well. The server also processes web requests from an e-commerce
> >> > > > site and will
> >> > > > be actively on 24 x 7 schedule in coming 6 months.
> >> > > >
> >> > > > We have already discarded offline method as it results in
> >> > > > transaction
> >> > > > failures. The online method is quite long (takes more than twice as
> >> > > > much time
> >> > > > ~ 18-20 hours compared to 8-10 hours for offline method...
> >> > > > understandably!).
> >> > > >
> >> > > > Another worry is the excessive log generation for both offline and
> >> > > > online
> >> > > > methods (its contrary to our thinking for at least online method).
> >> > > > We take
> >> > > > hourly transaction log backups and nearly 200 GB of logs are
> >> > > > genarted while
> >> > > > the index defrag process ran in the test environment. Being test,
> >> > > > we deleted
> >> > > > all of them but its another worry in production environment.
> >> > > >
> >> > > > Suggestions are welcome.
> >> > > >
> >> > > > P.S. Please inform me if you are looking for some information that
> >> > > > I have
> >> > > > not provided in the text.
> >> > > >
> >> > > >
> >> > > >
> >> > > > --
> >> > > > Regards,
> >> > > > MZeeshan
>
>
>

26 Mar 2005 1:32 PM

Andrew J. Kelly

You should always test with an equivalent version to what you will use in
production. Std edition is most likely not what you want. First off you can
only use a mx of 2GB of memory for sql server and you don't have any of the
enhancements that EE gives you regarding larger db's. This document will
help explain some.

http://www.microsoft.com/sql/techinfo/planning/SQLReskChooseEd.asp

Also look at the section in BooksOnLine listed as "editions of SQL Server"
to see what you are missing. Your DBCC's and Index rebuilding will be much
faster with EE than Std.

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"MZeeshan" <mzeeshan@community.nospam> wrote in message
news:39159BE1-4DB0-4D29-A65C-169DFDCC6C3D@microsoft.com...
> You are correct... we are on standard edition. I am not aware of the
> intricacies of the MSDN Universal Subscription but we just got this... at
> least I think we can use for testing purposes (I'll check with my
> procurement
> department but if you have any information please do share).
>
> But... what I am really interested is finding out the performance
> improvement that comes with it (for a quad processor machine)?
>
> "Andrew J. Kelly" wrote:
>
>> Indexdefrag always uses a single process since it is a single threaded
>> operation and operates mainly on the page level. DBreindex can use all
>> available processors but you need to have Enterprise edition to take full
>> advantage of it. I suspect you are using Std Edition.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "MZeeshan" <mzeeshan@community.nospam> wrote in message
>> news:0028ED78-A2B4-49D5-9AC8-D89A6D3C6741@microsoft.com...
>> > We are running on a quad processor machine and the processor
>> > utilization
>> > always remain very low; only one processor is utilized.
>> >
>> > This is something I am looking to find for some time. How come I tell
>> > the
>> > index defrag process which processor to use?
>> >
>> >
>> >
>> > "Jeff Robinson" wrote:
>> >
>> >> I would suggest decreasing the fill factor for the indicies since they
>> >> seem
>> >> to be fragmenting quickly. This will also improve the performance of
>> >> the
>> >> INDEXDEFRAG it runs better when logical fragmentation is low.
>> >> Also INDEXDEFRAG is single-threaded ( won't make use of
>> >> multiprocessors ),
>> >> if you have a server with multiple processors consider running one
>> >> INDEXDEFRAG for each processor simultaneously to reduce overall
>> >> execution
>> >> time.
>> >>
>> >>
>> >> "MZeeshan" wrote:
>> >>
>> >> > Thanks for the reply.
>> >> >
>> >> > For us, this vertical solution is like a black box (a can't do much
>> >> > here).
>> >> > Similarly, the reason why there is this fragmentation is due to the
>> >> > code was
>> >> > ported from a mainframe application... and is not very efficient.
>> >> > Now,
>> >> > its
>> >> > another whole story why it cannot be fixed. Let's assume that option
>> >> > is
>> >> > not
>> >> > open.
>> >> >
>> >> > One approach I am also pursuing is the archiving/purging of some
>> >> > historical
>> >> > data, that is expected to help.
>> >> >
>> >> > That leaves problems that I am facing with index defragmentation.
>> >> >
>> >> > Does this answer your question?
>> >> >
>> >> > "Jeff Robinson" wrote:
>> >> >
>> >> > > MZeeshan-
>> >> > >
>> >> > > I would start be trying to figure out why the indexes become so
>> >> > > fragemented.
>> >> > > What fill settings are you using?
>> >> > >
>> >> > > Are the underlying tables subject to high levels of modifications
>> >> > > (particularly inserts or updates of indexes fields)?
>> >> > >
>> >> > > Are you using large keyed clustered indexes and supporting
>> >> > > additional
>> >> > > indicies on the same table?
>> >> > >
>> >> > > If you are able to reduce the fragmentation the de-fragmentation
>> >> > > will
>> >> > > take
>> >> > > much less time.
>> >> > >
>> >> > >
>> >> > > "MZeeshan" wrote:
>> >> > >
>> >> > > > Hello-
>> >> > > >
>> >> > > > We have a database supporting a vertical solution. Following are
>> >> > > > some of the
>> >> > > > important details:
>> >> > > >
>> >> > > > OS: Windows 2003 Server (sp?? I am not sure but can get that
>> >> > > > info
>> >> > > > if needed)
>> >> > > > DB: SQL Server 2000 / sp3A
>> >> > > >
>> >> > > > Data Device Size = 125 GB
>> >> > > > Log Device Size = 25 GB (most of the time it remains free with
>> >> > > > hourly
>> >> > > > transaction log backups)
>> >> > > >
>> >> > > > Size of Fragmented data device = 86.09 GB
>> >> > > > Size after defragmentation = 76.78
>> >> > > >
>> >> > > > Total Number of tables: ~ 1,900 (some temporary but majority of
>> >> > > > them are
>> >> > > > empty!)
>> >> > > >
>> >> > > > This uneven data distribution is also evident from Index
>> >> > > > defragmentation
>> >> > > > (DBCC DBREINDEX/offline method)processing times:
>> >> > > >
>> >> > > > Largest Table: 2 hours 29 minutes (27% of the total processing
>> >> > > > time)
>> >> > > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing
>> >> > > > time)
>> >> > > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing
>> >> > > > time)
>> >> > > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing
>> >> > > > time)
>> >> > > >
>> >> > > > The timing trend is similar when I used the online/INDEXDEFRAG
>> >> > > > method as
>> >> > > > well. The server also processes web requests from an e-commerce
>> >> > > > site and will
>> >> > > > be actively on 24 x 7 schedule in coming 6 months.
>> >> > > >
>> >> > > > We have already discarded offline method as it results in
>> >> > > > transaction
>> >> > > > failures. The online method is quite long (takes more than twice
>> >> > > > as
>> >> > > > much time
>> >> > > > ~ 18-20 hours compared to 8-10 hours for offline method...
>> >> > > > understandably!).
>> >> > > >
>> >> > > > Another worry is the excessive log generation for both offline
>> >> > > > and
>> >> > > > online
>> >> > > > methods (its contrary to our thinking for at least online
>> >> > > > method).
>> >> > > > We take
>> >> > > > hourly transaction log backups and nearly 200 GB of logs are
>> >> > > > genarted while
>> >> > > > the index defrag process ran in the test environment. Being
>> >> > > > test,
>> >> > > > we deleted
>> >> > > > all of them but its another worry in production environment.
>> >> > > >
>> >> > > > Suggestions are welcome.
>> >> > > >
>> >> > > > P.S. Please inform me if you are looking for some information
>> >> > > > that
>> >> > > > I have
>> >> > > > not provided in the text.
>> >> > > >
>> >> > > >
>> >> > > >
>> >> > > > --
>> >> > > > Regards,
>> >> > > > MZeeshan
>>
>>
>>

27 Mar 2005 5:25 AM

MZeeshan

I found this article on BOL as well as MSFT website
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

I'll now setup a test environment and, once armed with some statistics, I
should be in a better position for some recommendation.

Thanks All!!!

Show quoteHide quote

"Andrew J. Kelly" wrote:

> You should always test with an equivalent version to what you will use in
> production. Std edition is most likely not what you want. First off you can
> only use a mx of 2GB of memory for sql server and you don't have any of the
> enhancements that EE gives you regarding larger db's. This document will
> help explain some.
>
> http://www.microsoft.com/sql/techinfo/planning/SQLReskChooseEd.asp
>
> Also look at the section in BooksOnLine listed as "editions of SQL Server"
> to see what you are missing. Your DBCC's and Index rebuilding will be much
> faster with EE than Std.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "MZeeshan" <mzeeshan@community.nospam> wrote in message
> news:39159BE1-4DB0-4D29-A65C-169DFDCC6C3D@microsoft.com...
> > You are correct... we are on standard edition. I am not aware of the
> > intricacies of the MSDN Universal Subscription but we just got this... at
> > least I think we can use for testing purposes (I'll check with my
> > procurement
> > department but if you have any information please do share).
> >
> > But... what I am really interested is finding out the performance
> > improvement that comes with it (for a quad processor machine)?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Indexdefrag always uses a single process since it is a single threaded
> >> operation and operates mainly on the page level. DBreindex can use all
> >> available processors but you need to have Enterprise edition to take full
> >> advantage of it. I suspect you are using Std Edition.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "MZeeshan" <mzeeshan@community.nospam> wrote in message
> >> news:0028ED78-A2B4-49D5-9AC8-D89A6D3C6741@microsoft.com...
> >> > We are running on a quad processor machine and the processor
> >> > utilization
> >> > always remain very low; only one processor is utilized.
> >> >
> >> > This is something I am looking to find for some time. How come I tell
> >> > the
> >> > index defrag process which processor to use?
> >> >
> >> >
> >> >
> >> > "Jeff Robinson" wrote:
> >> >
> >> >> I would suggest decreasing the fill factor for the indicies since they
> >> >> seem
> >> >> to be fragmenting quickly. This will also improve the performance of
> >> >> the
> >> >> INDEXDEFRAG it runs better when logical fragmentation is low.
> >> >> Also INDEXDEFRAG is single-threaded ( won't make use of
> >> >> multiprocessors ),
> >> >> if you have a server with multiple processors consider running one
> >> >> INDEXDEFRAG for each processor simultaneously to reduce overall
> >> >> execution
> >> >> time.
> >> >>
> >> >>
> >> >> "MZeeshan" wrote:
> >> >>
> >> >> > Thanks for the reply.
> >> >> >
> >> >> > For us, this vertical solution is like a black box (a can't do much
> >> >> > here).
> >> >> > Similarly, the reason why there is this fragmentation is due to the
> >> >> > code was
> >> >> > ported from a mainframe application... and is not very efficient.
> >> >> > Now,
> >> >> > its
> >> >> > another whole story why it cannot be fixed. Let's assume that option
> >> >> > is
> >> >> > not
> >> >> > open.
> >> >> >
> >> >> > One approach I am also pursuing is the archiving/purging of some
> >> >> > historical
> >> >> > data, that is expected to help.
> >> >> >
> >> >> > That leaves problems that I am facing with index defragmentation.
> >> >> >
> >> >> > Does this answer your question?
> >> >> >
> >> >> > "Jeff Robinson" wrote:
> >> >> >
> >> >> > > MZeeshan-
> >> >> > >
> >> >> > > I would start be trying to figure out why the indexes become so
> >> >> > > fragemented.
> >> >> > > What fill settings are you using?
> >> >> > >
> >> >> > > Are the underlying tables subject to high levels of modifications
> >> >> > > (particularly inserts or updates of indexes fields)?
> >> >> > >
> >> >> > > Are you using large keyed clustered indexes and supporting
> >> >> > > additional
> >> >> > > indicies on the same table?
> >> >> > >
> >> >> > > If you are able to reduce the fragmentation the de-fragmentation
> >> >> > > will
> >> >> > > take
> >> >> > > much less time.
> >> >> > >
> >> >> > >
> >> >> > > "MZeeshan" wrote:
> >> >> > >
> >> >> > > > Hello-
> >> >> > > >
> >> >> > > > We have a database supporting a vertical solution. Following are
> >> >> > > > some of the
> >> >> > > > important details:
> >> >> > > >
> >> >> > > > OS: Windows 2003 Server (sp?? I am not sure but can get that
> >> >> > > > info
> >> >> > > > if needed)
> >> >> > > > DB: SQL Server 2000 / sp3A
> >> >> > > >
> >> >> > > > Data Device Size = 125 GB
> >> >> > > > Log Device Size = 25 GB (most of the time it remains free with
> >> >> > > > hourly
> >> >> > > > transaction log backups)
> >> >> > > >
> >> >> > > > Size of Fragmented data device = 86.09 GB
> >> >> > > > Size after defragmentation = 76.78
> >> >> > > >
> >> >> > > > Total Number of tables: ~ 1,900 (some temporary but majority of
> >> >> > > > them are
> >> >> > > > empty!)
> >> >> > > >
> >> >> > > > This uneven data distribution is also evident from Index
> >> >> > > > defragmentation
> >> >> > > > (DBCC DBREINDEX/offline method)processing times:
> >> >> > > >
> >> >> > > > Largest Table: 2 hours 29 minutes (27% of the total processing
> >> >> > > > time)
> >> >> > > > Top 5 Tables: 6 hours 23 minutes (77% of the total processing
> >> >> > > > time)
> >> >> > > > Top 10 Tables: 7 hours 45 minutes (93% of the total processing
> >> >> > > > time)
> >> >> > > > Top 20 Tables: 8 hours 5 minutes (97% of the total processing
> >> >> > > > time)
> >> >> > > >
> >> >> > > > The timing trend is similar when I used the online/INDEXDEFRAG
> >> >> > > > method as
> >> >> > > > well. The server also processes web requests from an e-commerce
> >> >> > > > site and will
> >> >> > > > be actively on 24 x 7 schedule in coming 6 months.
> >> >> > > >
> >> >> > > > We have already discarded offline method as it results in
> >> >> > > > transaction
> >> >> > > > failures. The online method is quite long (takes more than twice
> >> >> > > > as
> >> >> > > > much time
> >> >> > > > ~ 18-20 hours compared to 8-10 hours for offline method...
> >> >> > > > understandably!).
> >> >> > > >
> >> >> > > > Another worry is the excessive log generation for both offline
> >> >> > > > and
> >> >> > > > online
> >> >> > > > methods (its contrary to our thinking for at least online
> >> >> > > > method).
> >> >> > > > We take
> >> >> > > > hourly transaction log backups and nearly 200 GB of logs are
> >> >> > > > genarted while
> >> >> > > > the index defrag process ran in the test environment. Being
> >> >> > > > test,
> >> >> > > > we deleted
> >> >> > > > all of them but its another worry in production environment.
> >> >> > > >
> >> >> > > > Suggestions are welcome.
> >> >> > > >
> >> >> > > > P.S. Please inform me if you are looking for some information
> >> >> > > > that
> >> >> > > > I have
> >> >> > > > not provided in the text.
> >> >> > > >
> >> >> > > >
> >> >> > > >
> >> >> > > > --
> >> >> > > > Regards,
> >> >> > > > MZeeshan
> >>
> >>
> >>
>
>
>

28 Mar 2005 5:07 PM

MZeeshan

Before we can close this thread... I have a final question:

For both offline and online defrag methods, excessive log is being
generated. Its understandable for DBREINDEX but why for INDEXDEFRAG. The
cumulative size of hourly transaction log backups was over 200 GB... since
test I was deleting them as and when needed but in production this won't be
possible. On the other hand, if I start restoring from full backup and start
applying those transaction log backups, it will be too long (not sure even
that would be useful)!

Can something be done? I would like to keep the revenue transactions but
would like to discard those generated by defrag process. Kinda wish list but
that's the best case scenario.

P.S. I read somewhere that INDEXDEFRAG can only run on full recovery
model... so that's how I executed it.

Show quoteHide quote

28 Mar 2005 6:09 PM

Andrew J. Kelly

Both methods will be fully logged in Full recovery mode. DBreindex can be
minimally logged in Bulk Insert or Simple modes but this does not affect
Indexdefrag. The reason is that DBreindex is creating the indexes from
scratch but Indexdefrag basically works at the page level. It can do a lot
of swapping of pages and in some cases (highly fragmented ones) it can swap
a single page multiple times. That means you can actually log much more
data to the log file than the size of the index. If you don't have much
fragmentation it usually logs less. If this is a problem then you should
issue Log backups periodically during the IndexDefrag session. Or if you
have enough log space for all of it simply do a FULL backup when done and
you won't have to worry about applying all those log files, just the recent
full backup.

--
Andrew J. Kelly SQL MVP

Show quoteHide quote

"MZeeshan" <mzeeshan@community.nospam> wrote in message
news:AD1C3044-B41B-42C2-B6D0-6D955D768983@microsoft.com...
> Before we can close this thread... I have a final question:
>
> For both offline and online defrag methods, excessive log is being
> generated. Its understandable for DBREINDEX but why for INDEXDEFRAG. The
> cumulative size of hourly transaction log backups was over 200 GB... since
> test I was deleting them as and when needed but in production this won't
> be
> possible. On the other hand, if I start restoring from full backup and
> start
> applying those transaction log backups, it will be too long (not sure even
> that would be useful)!
>
> Can something be done? I would like to keep the revenue transactions but
> would like to discard those generated by defrag process. Kinda wish list
> but
> that's the best case scenario.
>
> P.S. I read somewhere that INDEXDEFRAG can only run on full recovery
> model... so that's how I executed it.
>
> "MZeeshan" wrote:
>
>> Hello-
>>
>> We have a database supporting a vertical solution. Following are some of
>> the
>> important details:
>>
>> OS: Windows 2003 Server (sp?? I am not sure but can get that info if
>> needed)
>> DB: SQL Server 2000 / sp3A
>>
>> Data Device Size = 125 GB
>> Log Device Size = 25 GB (most of the time it remains free with hourly
>> transaction log backups)
>>
>> Size of Fragmented data device = 86.09 GB
>> Size after defragmentation = 76.78
>>
>> Total Number of tables: ~ 1,900 (some temporary but majority of them are
>> empty!)
>>
>> This uneven data distribution is also evident from Index defragmentation
>> (DBCC DBREINDEX/offline method)processing times:
>>
>> Largest Table: 2 hours 29 minutes (27% of the total processing time)
>> Top 5 Tables: 6 hours 23 minutes (77% of the total processing time)
>> Top 10 Tables: 7 hours 45 minutes (93% of the total processing time)
>> Top 20 Tables: 8 hours 5 minutes (97% of the total processing time)
>>
>> The timing trend is similar when I used the online/INDEXDEFRAG method as
>> well. The server also processes web requests from an e-commerce site and
>> will
>> be actively on 24 x 7 schedule in coming 6 months.
>>
>> We have already discarded offline method as it results in transaction
>> failures. The online method is quite long (takes more than twice as much
>> time
>> ~ 18-20 hours compared to 8-10 hours for offline method...
>> understandably!).
>>
>> Another worry is the excessive log generation for both offline and online
>> methods (its contrary to our thinking for at least online method). We
>> take
>> hourly transaction log backups and nearly 200 GB of logs are genarted
>> while
>> the index defrag process ran in the test environment. Being test, we
>> deleted
>> all of them but its another worry in production environment.
>>
>> Suggestions are welcome.
>>
>> P.S. Please inform me if you are looking for some information that I have
>> not provided in the text.
>>
>>
>>
>> --
>> Regards,
>> MZeeshan