|
|
You're in the
sql server programming
group:DBCC DBREINDEX is long running ..... Please help
sql server programming:
we have 2 tables that will have huge data updations during the bussiness hours I have ran DBCC SHOWCONTIG on 2 tables and the data is shown as below Table1 ------ DBCC SHOWCONTIG ('table1') Table: 'table1' (251147940); index ID: 1, database ID: 7 [SQLSTATE 01000] TABLE level scan performed. [SQLSTATE 01000] - Pages Scanned................................: 200880 [SQLSTATE 01000] - Extents Scanned..............................: 25243 [SQLSTATE 01000] - Extent Switches..............................: 25242 [SQLSTATE 01000] - Avg. Pages per Extent........................: 8.0 [SQLSTATE 01000] - Scan Density [Best Count:Actual Count].......: 99.47% [25110:25243] [SQLSTATE 01000] - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] - Extent Scan Fragmentation ...................: 5.56% [SQLSTATE 01000] - Avg. Bytes Free per Page.....................: 287.5 [SQLSTATE 01000] - Avg. Page Density (full).....................: 96.45% [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] After that I ran DBCC DBREINDEX('table1') This command took nearly 45 Min to complete after DBREINDEX I again ran DBCC SHOWCONTIG ('table1') the result is DBCC SHOWCONTIG scanning 'table1' table... [SQLSTATE 01000] Table: 'DATA_SERVICE' (251147940); index ID: 1, database ID: 7 [SQLSTATE 01000] TABLE level scan performed. [SQLSTATE 01000] - Pages Scanned................................: 200880 [SQLSTATE 01000] - Extents Scanned..............................: 25243 [SQLSTATE 01000] - Extent Switches..............................: 25242 [SQLSTATE 01000] - Avg. Pages per Extent........................: 8.0 [SQLSTATE 01000] - Scan Density [Best Count:Actual Count].......: 99.47% [25110:25243] [SQLSTATE 01000] - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] - Extent Scan Fragmentation ...................: 5.56% [SQLSTATE 01000] - Avg. Bytes Free per Page.....................: 287.5 [SQLSTATE 01000] - Avg. Page Density (full).....................: 96.45% [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] Table 2 --------- DBCC SHOWCONTIG ('table2') DBCC SHOWCONTIG scanning 'DATA_SERVICE_OPS' table... [SQLSTATE 01000] Table: 'DATA_SERVICE_OPS' (395148453); index ID: 1, database ID: 7 [SQLSTATE 01000] TABLE level scan performed. [SQLSTATE 01000] - Pages Scanned................................: 438736 [SQLSTATE 01000] - Extents Scanned..............................: 55309 [SQLSTATE 01000] - Extent Switches..............................: 62098 [SQLSTATE 01000] - Avg. Pages per Extent........................: 7.9 [SQLSTATE 01000] - Scan Density [Best Count:Actual Count].......: 88.31% [54842:62099] [SQLSTATE 01000] - Logical Scan Fragmentation ..................: 23.27% [SQLSTATE 01000] - Extent Scan Fragmentation ...................: 2.41% [SQLSTATE 01000] - Avg. Bytes Free per Page.....................: 173.1 [SQLSTATE 01000] - Avg. Page Density (full).....................: 97.86% [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] After DBCC DBREINDEX('table2') the result was as follows This command took 1 Hr to complete DBCC SHOWCONTIG scanning 'table2' table... [SQLSTATE 01000] Table: '' (395148453); index ID: 1, database ID: 7 [SQLSTATE 01000] TABLE level scan performed. [SQLSTATE 01000] - Pages Scanned................................: 435140 [SQLSTATE 01000] - Extents Scanned..............................: 54736 [SQLSTATE 01000] - Extent Switches..............................: 54735 [SQLSTATE 01000] - Avg. Pages per Extent........................: 7.9 [SQLSTATE 01000] - Scan Density [Best Count:Actual Count].......: 99.37% [54393:54736] [SQLSTATE 01000] - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] - Extent Scan Fragmentation ...................: 3.41% [SQLSTATE 01000] - Avg. Bytes Free per Page.....................: 107.7 [SQLSTATE 01000] - Avg. Page Density (full).....................: 98.67% [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] DBCC DBREINDEX on these 2 tables are taking nearly 2 Hrs to complete which is killing most of the bussiness hours Is there any solution for this such that the DBREINDEX time can be reduced Please help Thanks
Hi Gowtham,
You have something seriously wrong with your IO subsystem in that case; take table1 its only 1.5GBytes; roughly say that its goingt to read 1.5GBytes and write 1.5GBytes and let's assume that you write another 2GBytes of data to the log, in tempdb etc... so that adds up to 5GBytes needing to be read/written; if you do the maths 5000MBytes / 2700 seconds you will get the throughput of your disk - it's 1.8MBytes per second. I've not come across anything that does less than 12MBytes per second in the past 5 years. Please give us details of the hardware and disk configuration you are running with. Also, notice your extent fragmentation is still high even after the reindex; do you have autoshrink option turned on for this database? How much free space is left in the database? Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:E3C9D093-D97A-4634-AA2A-6920FDF4BA2A@microsoft.com... > Hi all, > > we have 2 tables that will have huge data updations during the bussiness > hours > > > I have ran DBCC SHOWCONTIG on 2 tables and the data is shown as below > > Table1 > ------ > > DBCC SHOWCONTIG ('table1') > > Table: 'table1' (251147940); index ID: 1, database ID: 7 [SQLSTATE 01000] > TABLE level scan performed. [SQLSTATE 01000] > - Pages Scanned................................: 200880 [SQLSTATE 01000] > - Extents Scanned..............................: 25243 [SQLSTATE 01000] > - Extent Switches..............................: 25242 [SQLSTATE 01000] > - Avg. Pages per Extent........................: 8.0 [SQLSTATE 01000] > - Scan Density [Best Count:Actual Count].......: 99.47% [25110:25243] > [SQLSTATE 01000] > - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] > - Extent Scan Fragmentation ...................: 5.56% [SQLSTATE 01000] > - Avg. Bytes Free per Page.....................: 287.5 [SQLSTATE 01000] > - Avg. Page Density (full).....................: 96.45% [SQLSTATE 01000] > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. [SQLSTATE 01000] > > After that I ran DBCC DBREINDEX('table1') > > This command took nearly 45 Min to complete > > after DBREINDEX I again ran DBCC SHOWCONTIG ('table1') the result is > > DBCC SHOWCONTIG scanning 'table1' table... [SQLSTATE 01000] > Table: 'DATA_SERVICE' (251147940); index ID: 1, database ID: 7 [SQLSTATE > 01000] > TABLE level scan performed. [SQLSTATE 01000] > - Pages Scanned................................: 200880 [SQLSTATE 01000] > - Extents Scanned..............................: 25243 [SQLSTATE 01000] > - Extent Switches..............................: 25242 [SQLSTATE 01000] > - Avg. Pages per Extent........................: 8.0 [SQLSTATE 01000] > - Scan Density [Best Count:Actual Count].......: 99.47% [25110:25243] > [SQLSTATE 01000] > - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] > - Extent Scan Fragmentation ...................: 5.56% [SQLSTATE 01000] > - Avg. Bytes Free per Page.....................: 287.5 [SQLSTATE 01000] > - Avg. Page Density (full).....................: 96.45% [SQLSTATE 01000] > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. [SQLSTATE 01000] > > > Table 2 > --------- > > DBCC SHOWCONTIG ('table2') > > DBCC SHOWCONTIG scanning 'DATA_SERVICE_OPS' table... [SQLSTATE 01000] > Table: 'DATA_SERVICE_OPS' (395148453); index ID: 1, database ID: 7 > [SQLSTATE > 01000] > TABLE level scan performed. [SQLSTATE 01000] > - Pages Scanned................................: 438736 [SQLSTATE 01000] > - Extents Scanned..............................: 55309 [SQLSTATE 01000] > - Extent Switches..............................: 62098 [SQLSTATE 01000] > - Avg. Pages per Extent........................: 7.9 [SQLSTATE 01000] > - Scan Density [Best Count:Actual Count].......: 88.31% [54842:62099] > [SQLSTATE 01000] > - Logical Scan Fragmentation ..................: 23.27% [SQLSTATE 01000] > - Extent Scan Fragmentation ...................: 2.41% [SQLSTATE 01000] > - Avg. Bytes Free per Page.....................: 173.1 [SQLSTATE 01000] > - Avg. Page Density (full).....................: 97.86% [SQLSTATE 01000] > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. [SQLSTATE 01000] > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. [SQLSTATE 01000] > > After DBCC DBREINDEX('table2') the result was as follows > > This command took 1 Hr to complete > > DBCC SHOWCONTIG scanning 'table2' table... [SQLSTATE 01000] > Table: '' (395148453); index ID: 1, database ID: 7 [SQLSTATE 01000] > TABLE level scan performed. [SQLSTATE 01000] > - Pages Scanned................................: 435140 [SQLSTATE 01000] > - Extents Scanned..............................: 54736 [SQLSTATE 01000] > - Extent Switches..............................: 54735 [SQLSTATE 01000] > - Avg. Pages per Extent........................: 7.9 [SQLSTATE 01000] > - Scan Density [Best Count:Actual Count].......: 99.37% [54393:54736] > [SQLSTATE 01000] > - Logical Scan Fragmentation ..................: 0.00% [SQLSTATE 01000] > - Extent Scan Fragmentation ...................: 3.41% [SQLSTATE 01000] > - Avg. Bytes Free per Page.....................: 107.7 [SQLSTATE 01000] > - Avg. Page Density (full).....................: 98.67% [SQLSTATE 01000] > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. [SQLSTATE 01000] > > > DBCC DBREINDEX on these 2 tables are taking nearly 2 Hrs to complete which > is killing most of the bussiness hours > > Is there any solution for this such that the DBREINDEX time can be reduced > > Please help > > Thanks > > > > > >
Hi Gowtham,
What's the hard drive? Is this a really old machine? SATA gives 40 - 70MBytes per second. Write cache is probably turned off on the disk which would explain the performance, or some other process is using the disk. Run up PERFMON and check the physical disk write and read bytes per second both before REDINDEX and during so as to deterime just how 'busy' your disk is. You a) must really use RAID in production; if the disk fails you are probably going to be in a lot of hours of down time; also b) any write cache must be battery backed up and c) use scsi or if using sata disable the on disk write cache (the battery backup does not protect that). You could in the interim look at DBCC INDEXDEFRAG instead; this will help with your fragmentation but won't make the tables unavailable - if you are using SQL 2000 and SP4 make sure you have the hotfix that fixes the concurrency bug with this command. My real advice is to get your hardware sorted out; it's not appropriate for what you are doing. Tony -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:4C14375B-6ABB-494F-A928-8FF3C839168B@microsoft.com... > Hi Tony, > > Below are the details > > Intel Xeon processor > > 3GB RAM > > 102GB Hard Drive [ NOT A RAID CONFIGURATION] > > Free Space in Hard Dirve 28.9 GB > > O.S. Windows 2000 Server > > > Auo Shrink option is turned off for this DataBase > > 6396 MB free space available in the Database > > >
No problems Gowtham,
The quick way to getting new hardware is to ask your boss how much money and face would your company lose if that server was turned off for 6 hours which conseratively might be the amount of time to get it rebuilt assuming you had a) the backups and b) a new replacement disk onsite. Another disk may cost your £200 - £300; a day without the server may cost £xK's.... Tony. -- Tony Rogerson, SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson [Ramblings from the field from a SQL consultant] http://sqlserverfaq.com [UK SQL User Community] [quoted text, click to view] "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message
news:6428D87E-D5E8-42C2-933A-EE66F77A7E66@microsoft.com... > Thank You for your valuable reply..... > > will try to do as suggested > > "Tony Rogerson" wrote: > >> Hi Gowtham, >> >> What's the hard drive? Is this a really old machine? >> >> SATA gives 40 - 70MBytes per second. >> >> Write cache is probably turned off on the disk which would explain the >> performance, or some other process is using the disk. >> >> Run up PERFMON and check the physical disk write and read bytes per >> second >> both before REDINDEX and during so as to deterime just how 'busy' your >> disk >> is. >> >> You a) must really use RAID in production; if the disk fails you are >> probably going to be in a lot of hours of down time; also b) any write >> cache >> must be battery backed up and c) use scsi or if using sata disable the on >> disk write cache (the battery backup does not protect that). >> >> You could in the interim look at DBCC INDEXDEFRAG instead; this will help >> with your fragmentation but won't make the tables unavailable - if you >> are >> using SQL 2000 and SP4 make sure you have the hotfix that fixes the >> concurrency bug with this command. >> >> My real advice is to get your hardware sorted out; it's not appropriate >> for >> what you are doing. >> >> Tony >> >> -- >> Tony Rogerson, SQL Server MVP >> http://sqlblogcasts.com/blogs/tonyrogerson >> [Ramblings from the field from a SQL consultant] >> http://sqlserverfaq.com >> [UK SQL User Community] >> >> >> "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message >> news:4C14375B-6ABB-494F-A928-8FF3C839168B@microsoft.com... >> > Hi Tony, >> > >> > Below are the details >> > >> > Intel Xeon processor >> > >> > 3GB RAM >> > >> > 102GB Hard Drive [ NOT A RAID CONFIGURATION] >> > >> > Free Space in Hard Dirve 28.9 GB >> > >> > O.S. Windows 2000 Server >> > >> > >> > Auo Shrink option is turned off for this DataBase >> > >> > 6396 MB free space available in the Database >> > >> > >> > >> >>
Hi Tony,
Below are the details Intel Xeon processor 3GB RAM 102GB Hard Drive [ NOT A RAID CONFIGURATION] Free Space in Hard Dirve 28.9 GB O.S. Windows 2000 Server Auo Shrink option is turned off for this DataBase 6396 MB free space available in the Database
Coulnd't you start reindexing after business hours...? Maybe put it on
the agent as a job to run at night. It's a big table but not exeptional by the looks of it.. What kind of disks are you using? striped RAID? Also do you really need al your indexes? How many indexs are there on the tables? you can reindex one by one if you also specify the index name. Another option that would probably make your life easier is to partition the table into a bunch of smaller ones, then you can reindex them one by one (and even maybe index by index as well). I can't tell from what you've said what type of dataaccess you have on the table. But often with clever partitioning you can create a 'hot' partition where all the writes go, keep that small, not too many indexes on columns that change a lot and on very fast disks (striped 15k with lots of cache etc, split indexes and tables across filegroups on different disks) . The 'stale' parts of the table you kan keep big and very indexed and put those on the slower disks. Regards, Gert-Jan
Thank You for your valuable reply.....
will try to do as suggested [quoted text, click to view] "Tony Rogerson" wrote:
> Hi Gowtham, > > What's the hard drive? Is this a really old machine? > > SATA gives 40 - 70MBytes per second. > > Write cache is probably turned off on the disk which would explain the > performance, or some other process is using the disk. > > Run up PERFMON and check the physical disk write and read bytes per second > both before REDINDEX and during so as to deterime just how 'busy' your disk > is. > > You a) must really use RAID in production; if the disk fails you are > probably going to be in a lot of hours of down time; also b) any write cache > must be battery backed up and c) use scsi or if using sata disable the on > disk write cache (the battery backup does not protect that). > > You could in the interim look at DBCC INDEXDEFRAG instead; this will help > with your fragmentation but won't make the tables unavailable - if you are > using SQL 2000 and SP4 make sure you have the hotfix that fixes the > concurrency bug with this command. > > My real advice is to get your hardware sorted out; it's not appropriate for > what you are doing. > > Tony > > -- > Tony Rogerson, SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson > [Ramblings from the field from a SQL consultant] > http://sqlserverfaq.com > [UK SQL User Community] > > > "Gowtham" <Gowtham@discussions.microsoft.com> wrote in message > news:4C14375B-6ABB-494F-A928-8FF3C839168B@microsoft.com... > > Hi Tony, > > > > Below are the details > > > > Intel Xeon processor > > > > 3GB RAM > > > > 102GB Hard Drive [ NOT A RAID CONFIGURATION] > > > > Free Space in Hard Dirve 28.9 GB > > > > O.S. Windows 2000 Server > > > > > > Auo Shrink option is turned off for this DataBase > > > > 6396 MB free space available in the Database > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |