Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BC
AutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
Showing posts with label point. Show all posts
Showing posts with label point. Show all posts
Thursday, March 29, 2012
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
dbcc shrinkfile & recovery mode
Occasionally, we have gotten messages that the logfile is too big on one
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
of our servers. At that point, we will run a backup of the database and
then dbcc shrinkfile on it.
From what I've read, I thought that some of the properties we have set
would make it unnecessary to have to do that. On the Options tab on the
database properties, we have the Recovery model set to Simple, and have
the following settings checked: Auto Update Statistics, Auto Shrink,
and Auto Create Statistics.
Is there another setting I'm missing? Or, should I just set up a job to
back up the database and run the dbcc shrinkfile on it at set intervals?
BCAutoShrink is taking your log file down to a very minimal size when it runs,
and then your Autogrow has to kick in when the db has a lot of
activity...if it is very busy, it could grow quite large. I've never seen
a SQL Server message "too big"...what are you really getting, and what is
the size at that point.?
In theory, if you set the db to Simple and DBCC Shrinkfile it down to a size
that is big enough to handle your busiest time frames you should be good
from there.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23xn12hncFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Occasionally, we have gotten messages that the logfile is too big on one
> of our servers. At that point, we will run a backup of the database and
> then dbcc shrinkfile on it.
> From what I've read, I thought that some of the properties we have set
> would make it unnecessary to have to do that. On the Options tab on the
> database properties, we have the Recovery model set to Simple, and have
> the following settings checked: Auto Update Statistics, Auto Shrink, and
> Auto Create Statistics.
> Is there another setting I'm missing? Or, should I just set up a job to
> back up the database and run the dbcc shrinkfile on it at set intervals?
> BC
Sunday, March 25, 2012
DBCC SHOWCONTIG question
Hi Freinds,
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>
SQL 2000
I have performance issues on my database and got to this point that DBCC
SHOWCONTIG result for my tables are:
DBCC SHOWCONTIG scanning 'dup_source_title' table...
Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 19296
- Extents Scanned.......................: 2429
- Extent Switches.......................: 2428
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
- Logical Scan Fragmentation ..............: 0.06%
- Extent Scan Fragmentation ...............: 1.03%
- Avg. Bytes Free per Page................: 7265.6
- Avg. Page Density (full)................: 10.24%
DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 180459
- Extents Scanned.......................: 22605
- Extent Switches.......................: 23105
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
- Logical Scan Fragmentation ..............: 3.78%
- Extent Scan Fragmentation ...............: 12.67%
- Avg. Bytes Free per Page................: 656.4
- Avg. Page Density (full)................: 91.89%
is this good result? bad result? which statistic is not good and need
tuning? and how , what should I do to tune it?
Thanks in advance,
PatThey are both fine in regards to fragmentation but the first one is only 10%
full where as the second is 90% full. There is no right or wrong number for
fullness since it depends a lot on how the index is used. But 10% is
probably way too low of a fill factor. Have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have performance issues on my database and got to this point that DBCC
> SHOWCONTIG result for my tables are:
> DBCC SHOWCONTIG scanning 'dup_source_title' table...
> Table: 'dup_source_title' (779149821); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 19296
> - Extents Scanned.......................: 2429
> - Extent Switches.......................: 2428
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.30% [2412:2429]
> - Logical Scan Fragmentation ..............: 0.06%
> - Extent Scan Fragmentation ...............: 1.03%
> - Avg. Bytes Free per Page................: 7265.6
> - Avg. Page Density (full)................: 10.24%
> DBCC SHOWCONTIG scanning 'jm_wo_transaction' table...
> Table: 'jm_wo_transaction' (667305587); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 180459
> - Extents Scanned.......................: 22605
> - Extent Switches.......................: 23105
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 97.63% [22558:23106]
> - Logical Scan Fragmentation ..............: 3.78%
> - Extent Scan Fragmentation ...............: 12.67%
> - Avg. Bytes Free per Page................: 656.4
> - Avg. Page Density (full)................: 91.89%
> is this good result? bad result? which statistic is not good and need
> tuning? and how , what should I do to tune it?
> Thanks in advance,
> Pat
>|||Thank you very much for the answer,
I am struggling with the performance now and need to do smt to get the db
running.
What does Scan Density [Best Count:Actual Count] show? Should it be low ?
High?
Where are the main places that Ihave to look to tune the database?
Thanks again ina advance ,
Pat
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
> They are both fine in regards to fragmentation but the first one is only
> 10% full where as the second is 90% full. There is no right or wrong
> number for fullness since it depends a lot on how the index is used. But
> 10% is probably way too low of a fill factor. Have a look at these:
>
> http://www.microsoft.com/technet/pr..._showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/col...fillfactors.asp
> Fill Factors
> http://www.sql-server-performance.c...red_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OroJcabLGHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||The 2nd link I gave you is all about DBCC SHOWCONTIG and should answer most
if not all of your questions on that topic. The other links are extremely
beneficial as well. But fragmentation is probably not the first place you
should look if you are having performance issues. Have a look at these to
help you narrow down the culprits:
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance Checklist
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Patrick" <patriarck@.gmail.com> wrote in message
news:ej0DZfcLGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Thank you very much for the answer,
> I am struggling with the performance now and need to do smt to get the db
> running.
> What does Scan Density [Best Count:Actual Count] show? Should it be low ?
> High?
> Where are the main places that Ihave to look to tune the database?
> Thanks again ina advance ,
> Pat
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGkHq8bLGHA.344@.TK2MSFTNGP11.phx.gbl...
>
DBCC SHOWCONTIG Performances
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 ?
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 ?
Wednesday, March 7, 2012
DBCC DBREINDEX fails for table
Hi,
I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.
---
Error Number : 3628
Message :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.
[Microsoft][ODBC SQL Server
----
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..
Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.
Thanks in advance,
Ranjit.That's one I have not come accross. Have a look at this thread, and see if you can follow the same steps:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=70&threadid=45658&enterthread=y
Are the columns in the index float datatypes?|||What about an INDEXDEFRAG instead of a rebuild?
Regards,
hmscott|||My bet is that it is a corrupt value for a floating point number... Not all possible values are valid floating point values. Some of them are non-sensical, so any attempt to even read them produces a runtime error.
A simple test would be to do a SELECT * to see if you can successfully retrieve all of the rows.
If that is the case, you'll need to fix the row before you can successfully build the index.
-PatP|||Thanks MCrowley/Scott/Pat for all your help.
But I am not done yet and would be back after some more research.. maybe to ask you guys again or to let you what I did to overcome my issue :)
Thanks once again,
Ranjit.|||Just an update on this one ..
Well this was due to corrupt float data in the tables ..to track the errant row ..we tried to export it into a file from where we could see it .. so we tried various methods of export ..
we could not export it into an xls/textfile, As the no of rows were large, ..
export thru bcp was possible but we could not correct the data as it was in native format .. import through BCP failed surprisingly long before it encountered the corrupt data rows .. ( maybe some mismatch in the data during bcp export )
But the select statement was the best solution in identifying the corrupt rows .. SQL failed to select on the corrupt data rows .. and we knew the error lay in those rows ..
Once identified, an update command was successfully executed on the corrupt rows and rectified ..
PS: all this was done on a test database .. we still have to sit down with the business team and make the changes ..
Just wanted to know what could be the best way out
1) change the data in corrupt rows and make sure their application inputs correct data
2) alter the column of the tables to accomodate the data ..
Please let me know.
And Thanks once again for the wonderful help I recieved during this issue.
Warm Regards,
Ranjit.|||One option is to simply "plug" the offending values. If you know that column plugh of the row associated with PK 'xyzzy' is bad, you can simply:UPDATE myTable
SET plugh = 0e0
WHERE 'xyzzy' = PK
Another approach is to make a copy, the basic idea is simple, but implementing it can be a bit difficult to explain. The short answer is to copy all of the completely readable rows in the original table to a scratch table, then copy the columns you can get from the probem row or rows substituting some acceptable value for the problem columns.
The exact mechanics of this process get complicated, due to space/time/other constraints. Feel free to ask for more help if you run into problems, because there are often easy fixes for otherwise unsolvable problems if you're willing to "think outside the box" a bit!
-PatP
I am facing a rather peculiar issue where I am getting a floating point exception error while rebuilding index for a particular table.
---
Error Number : 3628
Message :
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3628: [Microsoft][ODBC SQL Server Driver][SQL Server]A floating point exception occurred in the user process. Current transaction is canceled.
[Microsoft][ODBC SQL Server
----
This seems to be a rare issue ( as acknowledged by microsoft ) and they seem to suggest that it happens with SQL Server 2000 SP3 . I migrated my database into an SQL Server 2000 SP4 and started the rebuild again .. .. But it still failed with the same error ..
Am just hoping the microsoft guys are wrong and many of you have actually faced this stuff before.. Please let me know.
Thanks in advance,
Ranjit.That's one I have not come accross. Have a look at this thread, and see if you can follow the same steps:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=70&threadid=45658&enterthread=y
Are the columns in the index float datatypes?|||What about an INDEXDEFRAG instead of a rebuild?
Regards,
hmscott|||My bet is that it is a corrupt value for a floating point number... Not all possible values are valid floating point values. Some of them are non-sensical, so any attempt to even read them produces a runtime error.
A simple test would be to do a SELECT * to see if you can successfully retrieve all of the rows.
If that is the case, you'll need to fix the row before you can successfully build the index.
-PatP|||Thanks MCrowley/Scott/Pat for all your help.
But I am not done yet and would be back after some more research.. maybe to ask you guys again or to let you what I did to overcome my issue :)
Thanks once again,
Ranjit.|||Just an update on this one ..
Well this was due to corrupt float data in the tables ..to track the errant row ..we tried to export it into a file from where we could see it .. so we tried various methods of export ..
we could not export it into an xls/textfile, As the no of rows were large, ..
export thru bcp was possible but we could not correct the data as it was in native format .. import through BCP failed surprisingly long before it encountered the corrupt data rows .. ( maybe some mismatch in the data during bcp export )
But the select statement was the best solution in identifying the corrupt rows .. SQL failed to select on the corrupt data rows .. and we knew the error lay in those rows ..
Once identified, an update command was successfully executed on the corrupt rows and rectified ..
PS: all this was done on a test database .. we still have to sit down with the business team and make the changes ..
Just wanted to know what could be the best way out
1) change the data in corrupt rows and make sure their application inputs correct data
2) alter the column of the tables to accomodate the data ..
Please let me know.
And Thanks once again for the wonderful help I recieved during this issue.
Warm Regards,
Ranjit.|||One option is to simply "plug" the offending values. If you know that column plugh of the row associated with PK 'xyzzy' is bad, you can simply:UPDATE myTable
SET plugh = 0e0
WHERE 'xyzzy' = PK
Another approach is to make a copy, the basic idea is simple, but implementing it can be a bit difficult to explain. The short answer is to copy all of the completely readable rows in the original table to a scratch table, then copy the columns you can get from the probem row or rows substituting some acceptable value for the problem columns.
The exact mechanics of this process get complicated, due to space/time/other constraints. Feel free to ask for more help if you run into problems, because there are often easy fixes for otherwise unsolvable problems if you're willing to "think outside the box" a bit!
-PatP
Subscribe to:
Posts (Atom)