I have decided to automate the Index Defragmentation process on all tha
tables of my database
with a store proc.
As a starting point, I used an example from SQL Server 2000 Help (Transact
SQL Reference -
DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG
to defragment
indexes in a database).
I want to only consider indexes with a fragmentation over 20%.
My problem is :
When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
it takes an average
of 65 minutes before I get a result.
Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
I must consider over 60 tables.
My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
Intel Xeon Mp CPU and
7680 MB of RAM.
Would you have any advise helping me increasing thoses performances ?
The perf of SHOWCONTIG (which I wrote) is proportional to the IO
capabilities of your system, and in FAST mode, is also proportional to the
fanout of your table. In FAST mode, it reads all the b-tree pages in the
parent level (one level above the leaf level of the index) - so the larger
the fanout, the smaller the number of pages it has to read.
For the example table you give, you need to do the following for each index:
1) work out index row size, R
2) divide 8000 / R = LF (leaf fanout)
3) divide 43000000 / LF = LN (number of pages required at leaf level)
4) work out index key size, K (this can be different from the row size,
depending on SQL 2005 features used and whether the index is unique or not -
see 2005 BOL for full details)
5) divide 8000 / K = TF (tree fanout)
6) divide LN / TF = P (number of pages SHOWCONTIG has to read for this
index)
Does the table have a clustered index? If not, SHOWCONTIG will have to read
all (i.e. 8000 / data row size) pages.
What's the max IO throughput of your IO subsystem? Are you running
SHOWCONTIG while there's heavy load?
Why do you need to do this for all tables and indexes? Are you able to
correlate increasing logical scan fragmentation with decreasing query
performance, for all these tables and indexes?
Have a read of this whitepaper for more info too - let me know if you have
any further questions.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc G." <Marc G.@.discussions.microsoft.com> wrote in message
news:5F40205F-2835-4AA1-9B9E-D43D32E6CABE@.microsoft.com...
>I have decided to automate the Index Defragmentation process on all tha
> tables of my database
> with a store proc.
> As a starting point, I used an example from SQL Server 2000 Help (Transact
> SQL Reference -
> DBCC ShowContig topic : Example E : Use DBCC SHOWCONTIG and DBCC
> INDEXDEFRAG
> to defragment
> indexes in a database).
> I want to only consider indexes with a fragmentation over 20%.
> My problem is :
> When I run DBCC SHOWCONTIG on a large table (43,000,000 rows, 12 indexes),
> it takes an average
> of 65 minutes before I get a result.
> Code : DBCC SHOWCONTIG (TFt01_Vente_hebdo)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> I must consider over 60 tables.
> My SQL Server runs on a Windows 2000 Server (SP4), with a dual 2.70 Ghz
> Intel Xeon Mp CPU and
> 7680 MB of RAM.
> Would you have any advise helping me increasing thoses performances ?
Showing posts with label process. Show all posts
Showing posts with label process. Show all posts
Sunday, March 25, 2012
Monday, March 19, 2012
DBCC Logical Scan Bytes/Sec
Hi,
I have a process data acquisition system running with SQL 2000
Enterprise Manager. I have a large hard drive Read activity. By using the
performance monitor, I found that this reading activity is clearly associate
with DBCC logical scan bytes/sec counter and is mostly twin with Physical
Hard drive read/sec.
The point is that I didn't find what trigger this scan. I observed that more
my database is growing more this scan take time. Actually the hard drive is
busy at more that 99% of the time to read and data request take more and
more time.
Is those DBCC scans are essential and how can I identify what is triggering
them?
Regards
MarcDo you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
profiler to see what commands are being run at that time.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:I4pFe.52350$mv2.811211@.weber.videotron.net...
> Hi,
> I have a process data acquisition system running with SQL 2000
> Enterprise Manager. I have a large hard drive Read activity. By using the
> performance monitor, I found that this reading activity is clearly
> associate with DBCC logical scan bytes/sec counter and is mostly twin with
> Physical Hard drive read/sec.
> The point is that I didn't find what trigger this scan. I observed that
> more my database is growing more this scan take time. Actually the hard
> drive is busy at more that 99% of the time to read and data request take
> more and more time.
> Is those DBCC scans are essential and how can I identify what is
> triggering them?
>
> Regards
> Marc
>|||Hi Mr. Kelly, thanks for the reply.
That was a thing I was suspecting but I didn't know how to trace it. I used
SQL Profiler, like suggested, and I found many querys who use the DBCC
UPDATEUSAGE command. I think this is another disk intensive command any way
I found some with a duration over 250000 ( milliseconds I guess ).
Is there any reason to use this command frequently?
Thanks again for the help. I'll try to reach to conceptors of the DB to see
why they're using this command intensively.
Regards
Marc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
> Do you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
> profiler to see what commands are being run at that time.
> --
> Andrew J. Kelly SQL MVP
>
> "marc quirion" <mquirion@.videotron.ca> wrote in message
> news:I4pFe.52350$mv2.811211@.weber.videotron.net...
>|||That command certainly would do it. There is no real reason to update it so
often. If they need that kind of accurate data that often they should
rethink what they are doing and why.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:8vnGe.8806$nx3.312875@.wagner.videotron.net...
> Hi Mr. Kelly, thanks for the reply.
> That was a thing I was suspecting but I didn't know how to trace it. I
> used
> SQL Profiler, like suggested, and I found many querys who use the DBCC
> UPDATEUSAGE command. I think this is another disk intensive command any
> way
> I found some with a duration over 250000 ( milliseconds I guess ).
> Is there any reason to use this command frequently?
> Thanks again for the help. I'll try to reach to conceptors of the DB to
> see
> why they're using this command intensively.
> Regards
> Marc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
>
I have a process data acquisition system running with SQL 2000
Enterprise Manager. I have a large hard drive Read activity. By using the
performance monitor, I found that this reading activity is clearly associate
with DBCC logical scan bytes/sec counter and is mostly twin with Physical
Hard drive read/sec.
The point is that I didn't find what trigger this scan. I observed that more
my database is growing more this scan take time. Actually the hard drive is
busy at more that 99% of the time to read and data request take more and
more time.
Is those DBCC scans are essential and how can I identify what is triggering
them?
Regards
MarcDo you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
profiler to see what commands are being run at that time.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:I4pFe.52350$mv2.811211@.weber.videotron.net...
> Hi,
> I have a process data acquisition system running with SQL 2000
> Enterprise Manager. I have a large hard drive Read activity. By using the
> performance monitor, I found that this reading activity is clearly
> associate with DBCC logical scan bytes/sec counter and is mostly twin with
> Physical Hard drive read/sec.
> The point is that I didn't find what trigger this scan. I observed that
> more my database is growing more this scan take time. Actually the hard
> drive is busy at more that 99% of the time to read and data request take
> more and more time.
> Is those DBCC scans are essential and how can I identify what is
> triggering them?
>
> Regards
> Marc
>|||Hi Mr. Kelly, thanks for the reply.
That was a thing I was suspecting but I didn't know how to trace it. I used
SQL Profiler, like suggested, and I found many querys who use the DBCC
UPDATEUSAGE command. I think this is another disk intensive command any way
I found some with a duration over 250000 ( milliseconds I guess ).
Is there any reason to use this command frequently?
Thanks again for the help. I'll try to reach to conceptors of the DB to see
why they're using this command intensively.
Regards
Marc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
> Do you have any maintenance tasks scheduled that run a DBCC CHECKDB? Use
> profiler to see what commands are being run at that time.
> --
> Andrew J. Kelly SQL MVP
>
> "marc quirion" <mquirion@.videotron.ca> wrote in message
> news:I4pFe.52350$mv2.811211@.weber.videotron.net...
>|||That command certainly would do it. There is no real reason to update it so
often. If they need that kind of accurate data that often they should
rethink what they are doing and why.
Andrew J. Kelly SQL MVP
"marc quirion" <mquirion@.videotron.ca> wrote in message
news:8vnGe.8806$nx3.312875@.wagner.videotron.net...
> Hi Mr. Kelly, thanks for the reply.
> That was a thing I was suspecting but I didn't know how to trace it. I
> used
> SQL Profiler, like suggested, and I found many querys who use the DBCC
> UPDATEUSAGE command. I think this is another disk intensive command any
> way
> I found some with a duration over 250000 ( milliseconds I guess ).
> Is there any reason to use this command frequently?
> Thanks again for the help. I'll try to reach to conceptors of the DB to
> see
> why they're using this command intensively.
> Regards
> Marc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: ubKtOedkFHA.2044@.TK2MSFTNGP10.phx.gbl...
>
Sunday, March 11, 2012
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
Travis
DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
Travis
DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrin
k
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effor
t
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce th
e
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrin
k
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effor
t
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce th
e
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Dbcc Indexdefrag ...
Howdy,
Has anyone played around automating DBCC INDEXDEFRAG? I am in the process of setting up a job that will do a dbcc showcontig on all our databases every night, then based on the logicalfrag number, it will pick the top 3 fragmented indexes per database and run the dbcc indexdefrag command. I am having a couple of problems...
First, any ideas on how to capture the output of the dbcc indexdefrag? If i do something like
...
SET @.s_str = 'DBCC INDEXDEFRAG (' + @.dbname + ', ' + @.tablename + ',' + @.indxname + ')'
INSERT INTO #temp
EXEC (@.s_str)
...
it wont run because the defrag cant run in a user transaction which is created due to the insert statement.
Anyway, I need to find out if the defrag completed successfully.
Second, when running the defrag within an exec statement, as above, It only seems to work part of the time. I look at the result window, and for most of the indexes it says it completed it, yet 0 pages were moved and 0 pages removed. However, if I run the dbcc indexdefrag statement on its own, it seems to make changes... Any thoughts? anyone else find problems with the defrag?
TIAOpen the BOL and do a search on the "DBCC SHOWCONTIG" topic. In the section E. There is a script that you can cut & paste, then schedule a job and run till your hear content (with whatever level of fragmentation that you wanted to defrag, by changing the "SELECT @.maxfrag = xx " at the top of the script. I cut &paste it here for anyone who doesn't have the BOL handy:
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
-- Good luck :-)
SVT
Has anyone played around automating DBCC INDEXDEFRAG? I am in the process of setting up a job that will do a dbcc showcontig on all our databases every night, then based on the logicalfrag number, it will pick the top 3 fragmented indexes per database and run the dbcc indexdefrag command. I am having a couple of problems...
First, any ideas on how to capture the output of the dbcc indexdefrag? If i do something like
...
SET @.s_str = 'DBCC INDEXDEFRAG (' + @.dbname + ', ' + @.tablename + ',' + @.indxname + ')'
INSERT INTO #temp
EXEC (@.s_str)
...
it wont run because the defrag cant run in a user transaction which is created due to the insert statement.
Anyway, I need to find out if the defrag completed successfully.
Second, when running the defrag within an exec statement, as above, It only seems to work part of the time. I look at the result window, and for most of the indexes it says it completed it, yet 0 pages were moved and 0 pages removed. However, if I run the dbcc indexdefrag statement on its own, it seems to make changes... Any thoughts? anyone else find problems with the defrag?
TIAOpen the BOL and do a search on the "DBCC SHOWCONTIG" topic. In the section E. There is a script that you can cut & paste, then schedule a job and run till your hear content (with whatever level of fragmentation that you wanted to defrag, by changing the "SELECT @.maxfrag = xx " at the top of the script. I cut &paste it here for anyone who doesn't have the BOL handy:
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
-- Good luck :-)
SVT
Sunday, February 19, 2012
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBC
C
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBC
C
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
Prakash
Hi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
Prakash
Hi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
dbcc checkdb on a large table
We have a 200 GB database with a single table that spans 36 partitions.
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Each time we run a dbcc checkdb on this database, the process takes
close to 40 hours to finish, and in the course of doing it expands
tempdb to almost 200 GB. What are the recommended options to speed up
the dbcc process? What kind of dbcc's can be done? Suggestions are
welcome.
The database runs on SQL Server 2005 - there are 4 CPUs on the box and
the total memory is 8 GB.
Thanks,
PrakashHi,
DBCC CHECKDB on my 320 GB database on 8 CPU X 8 GB machine is taking me only
1 hour. 40 hours is too long on a 4 CPU machine. Just see the Disk trasfer
and Queueu length at that point of time. Check with your hardware person to
do a hardware check.
From you side you could enable the degree of paralleism (DOP) during the DBCC
and see if that helps. Hope you have already enabled the AWE memory since
you have 8 GB RAM.
Thanks
Hari
SQL Server MVP
"pnat331@.gmail.com" wrote:
> We have a 200 GB database with a single table that spans 36 partitions.
> Each time we run a dbcc checkdb on this database, the process takes
> close to 40 hours to finish, and in the course of doing it expands
> tempdb to almost 200 GB. What are the recommended options to speed up
> the dbcc process? What kind of dbcc's can be done? Suggestions are
> welcome.
> The database runs on SQL Server 2005 - there are 4 CPUs on the box and
> the total memory is 8 GB.
> Thanks,
> Prakash
>
Subscribe to:
Posts (Atom)