Extent Fragmentation High Just After Reindexing

Jul 18, 2007

Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.



I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers?? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s??



If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce

View 7 Replies


ADVERTISEMENT

High Extent Scan Fragmentation

Oct 25, 2002

Can anyone let me now why the extent scan fragmentation is very high.
I do have a clustered index on this table .
The fill factor is 0 and this table has high inserts as it is used to maintain history.
Rebuilding the Indexes did not help.

DBCC SHOWCONTIG scanning 'ACCOUNTS' table...
Table: 'ACCOUNTS'(1061578820); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 728157
- Extents Scanned..............................: 91759
- Extent Switches..............................: 93305
- Avg. Pages per Extent........................: 7.9
- Scan Density .......: 97.55% [91020:93306]
- Logical Scan Fragmentation ..................: 0.33%
- Extent Scan Fragmentation ...................: 99.99%
- Avg. Bytes Free per Page.....................: 76.6
- Avg. Page Density (full).....................: 99.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator

Thanks in advance,
Shades[B]Extent Scan Fragmentation

View 2 Replies View Related

Extent Fragmentation Still High After Index Drop/create

Jul 20, 2005

hello everyone,we dropped the clustered & nonclustered indeces on a table, thenrebuilt them. logical fragmentation is near zero, but extentfragmentation is about 40%. how can this be if the indeces are brandnew?

View 2 Replies View Related

Extent Scan Fragmentation Increases After DBReindex - Why?

Dec 28, 2007

On some tables when I run DBCC ShowContig followed by DBReindex followed by ShowContig I notice Extent Scan Fragmentation actually increases. Why does this happen? Below are the SHOWCONTIG results after running DBReindex three times.

After First DBReindex

- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 47.58%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%

After Second DBReindex

- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 20.16%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%

After Third DBReindex

- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 67.74%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%

Thanks, Dave

View 6 Replies View Related

High Availability To High Protection Without Reconfiguring Mirroring

Apr 23, 2007

Hi,

Is there a way to configure mirroring to go from High Availability to High Protection without having to reconfigure Database Mirroring? Using the interface in Management Studio, I can change the configuration option to High Performance, but not High Protection despite both of them being Synchronous.

If not, what are the recommended steps to configure the mirror once it already has been configured? Is just like initially setting up the mirror or would there be any shortcuts I could take? If I stop the mirroring and remove the witness, will the High Protection option be available?

Thanks,
J.

View 3 Replies View Related

High Safety Changed To High Performance After Fail Over ?

Mar 6, 2008

Hi There

I realise this is a stupid quesiton but i cannot really find any confirmation of this in BOL.


If you are running High Safety with automatic failover, when failover occurs does this automatically change to High Performance mode. SInce for failover to occur something has happen with the primary , it will be impossible to commit transactions on the new primary and mirror asyncronously since 1 of them is no longer available.


So am i correct in assuming that automatic failover also automatically changes the mode to High Performacne for that session?

Thanx

View 4 Replies View Related

Extent Allocation/Free Space - SQL2000??

May 6, 2004

I've faced with this problem in my DB:

There are some tables that suffer inserts and deletes daily. These tables have 3 nonclustered indexes including the pk.

I perceived that the space used for these tables are growing day after day even ocurring daily deletes.

These inserts and deletes follow the keys of the pk in ascent order.

I ran DBCC SHOWCONTIG on the tables and got results like this:

- Avg. Bytes Free per Page.....................: 7996.3

When I transform the pk to clustered this problem doesn't happen.

You realized the consequence of this: the users complains because the DB is without space, but it's not true!

Anybody could help me to understand why the extents are not being deallocated?

Thank's for help!

View 8 Replies View Related

To What Extent Can We Believe The Pattens Discovered By Mining Models?

Jul 16, 2007

Hi, all,

Again I am confused about the extent of being convinced by the mining models. We can validate the models with accuracy chart, but then to what extent can we trust that? (you never get 100% correct miming models) If we dont trust the results of the models, then the patterns the models discover are meaningless.

Just need some advices from you experts here to help me convince people on what I got from my mining models.

I am looking forward to hearing from you shortly and thank you very much.

With best regards,

Yours sincerely,

View 3 Replies View Related

Reindexing

Apr 15, 2002

Attempt to fetch logical page (1:166354) in database 'pm_pmc_prod' belongs to object '837120', not to object 'ng_visit'.

I got his error while importing data into the error, when i ran dbcc checkdb it gave me Msg 8928,8942,8976 etct witl serverity level 16...

is it possible to fix this curropt tables?

View 2 Replies View Related

REINDEXING IN VER 7

Jun 22, 1999

We load tables from text files for inquiries.
My procedure is to truncate the table
Use DTS to move the text file into the table
do the command DBCC DBREINDEX (TABLE,'')

Am I wasting my time?
Does SQL 7 rebuild the indicis as it loads the data from the text file?

View 1 Replies View Related

How And When Reindexing A Db?

Aug 16, 2006

Hello guys,

Two things:

1) Could somebody explain me how to reindex all tables in my db?

2) How do I know when I should reindex my db?

Thank you very much for any help!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 4 Replies View Related

Reindexing Tables

Sep 17, 2001

I've just recently tried to perform a scheduled reindexing job with the following command:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

Unfortunately, this command has only finished once of the five or six times I've tried to run it. The times it has failed, it deadlocks behind what seems to be a succession of other transactions. How can I make sure this command finishes?

View 1 Replies View Related

Reindexing Tables

Dec 27, 2002

Hello Everyone,

I need to rebuild all indexes in one table, is there any command that will do automatically instead of deleting and recreating them all?

Any help will be appreciated.

Thanks

View 3 Replies View Related

Table Reindexing

Nov 15, 2007

Hi All,
is it really improve performance that making table reindexing?

what i mean to say is i've one script, which will automatically drops all the indexes in a database, and reconstruct them with the same name.


is it really worth doing that?.....


thankyou very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 10 Replies View Related

Reindexing A Database...

Oct 18, 2007


All,

I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:

1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror

6) Restore the backup
7) Re-establish the mirror


Does anyone see any issues with the process itself?

Regards,

Ian

View 4 Replies View Related

Refragmentation And Reindexing

Dec 27, 2007

Hi experts,
For defragmenation and reindexing they are using the below cursor, and now they have asked me to remove the cursor and schedule the job accordingly to do the same functionality, so what will be the other way we can do without cursor? can you plase let me know the solution?


DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM dbadmin.dbo.tdbstatus WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN




SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor


Your help will be appreciated.

View 1 Replies View Related

Maintenance Plan, Reindexing

Mar 24, 2005

In the Enterprise Manager of SQL Server 2000 I have set up a maintenance plan which rebuilds my indexes. I've stuided the documentation, and from what I've learned what happens behind the curtain is that several DBCC REINDEX commands are being issued.
Question:
If I have 20 tables and 40 indexes: Will SQL Server do the maintance plan in 1 single transaction, or will it divide the it up to eg. 20 or 40 transactions?

-h

View 1 Replies View Related

Reindexing Vs Update Statistics

Nov 22, 2004

Hi

We are upgrading from sql 7 to 2000.During the upgrade process do we have to do a reindexing of all tables or will update statistics take care of that.

Or do we have to do both?
What is the difference between reindexing and update statistics.

Thanks

Madhukar Gole

View 5 Replies View Related

Reindexing And Transaction Logs

Dec 14, 2004

Hi All,

Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.

Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?

Is there any way that the reindexing can be excluded from the log files or is this a necessity?

Thanks in advance for your help.

Cheers
Troy

View 4 Replies View Related

Reindexing Of Database Tables

May 27, 2008

hi
i have reindex of the tables
is it really improve performance that making table reindexing ?

View 1 Replies View Related

Third Party Reindexing Utilities

Jul 23, 2005

Folks,I work on a system which is growing rapidly, with the number oftransactions we process growing on a daily basis. While this is goodnews or the business, maintenance is starting to become an issue as thedatabase is the backend for a website which cannot be down for alengthy period of time.While I do defrag the indexes, periodically the indexes do need to berebuilt. When this happens, the process locks pages and transactionsstart getting bounced out.Are their any third party utilities which will rebuild an indexwithout this locking occuring? Any help in pointing me in the rightdirection would be appreciated.

View 1 Replies View Related

Effect On Snapshots While Reindexing

Apr 30, 2007

Does someone know if doing a reindex on a clustered or non-clustered index cause the snapshot file to grow? In other words, is the data that makes up the snapshot copied from the source to the snapshot database? If a normal reindex is done on the underlying database, will it block users from acessing the snapshot? Any help would be appreciated.

View 1 Replies View Related

ReIndexing All Tables In A Database

Sep 5, 2006



I need to reindex all tables in my database and would like to do this without using a Cursor. What is the simplest way to achieve this.

Cheers

Nat

View 2 Replies View Related

Reindexing On A Mirror Environment

Oct 18, 2007

All,

I've got a medium sized database in a mirror configuration with witness. The database size is about 300gb and I would to reindex all of the tables in the database. My process would go something like this:

1) Backup principal
2) Break the mirror
3) Set the principal database to simple recovery mode
4) Perform the reindexing
5) Backup the principal and transfer that backup to the mirror

6) Restore the backup
7) Re-establish the mirror


Does anyone see any issues with the process itself?

Regards,

Ian

View 1 Replies View Related

Database Reindexing And Mirroring

Apr 25, 2008

Hi,

We have scheduled a job for DB Reindexing (Maitinance Plan) for a OLTP database on sunday.

We have used mirroring for automatic failover with a witness server now the DR Reindexing job fails after 30 mins without any error.

Please let me know why Database reindexing gets failed.

Regards
Sufian

View 16 Replies View Related

Reindexing Tables On A Databse That Is 36GB

Nov 3, 2005

Hello,

I'm looking for the query command that will go out to all the user Tables and will tell me what Indexes need to be reindexed.

We are having a problem with some of the tables and we don't know when our tables need to be reindexed other than when operations are stopped for our company.

Thanks,

Ron

View 2 Replies View Related

Fragmentation

May 13, 2008

 i want to do fragmentation using SQL 2005 Express. Can their be any helping material for making fragmentation.  Any Example

View 2 Replies View Related

Fragmentation

Mar 29, 2001

What DBCC or otherwise one would be able to find table fragmentation in SQL 7 and how to take some proactive actions ?

Thanks very much for your time.


-Tim

View 2 Replies View Related

Fragmentation

Jun 1, 2004

Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
dbcc indexdefrag
or
dbcc reindex
or
dropping and recreating indexes through SQL?

Thanks
Vinnie

View 5 Replies View Related

Fragmentation?

Jul 27, 2007

Sql Server 2000.

For simplicity, I am avoiding minor details.

Here is a scenario.

Create Table T (T_ID int PK, €¦..) and this table is referenced by 20 other places on T_ID.

And these 20 tables have T_ID as PK or part of PK.

We have large volume of deletes in T and in the other 20 tables based on T_ID.

Once the deletes are done, will dropping and re-creating the FK€™s that reference the T_ID
help reduce the fragmentation caused by the deletes?

View 4 Replies View Related

Table Fragmentation

May 8, 2001

hi all,

i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output

TABLE level scan performed.
- Pages Scanned................................: 104934
- Extents Scanned..............................: 13124
- Extent Switches..............................: 13123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.95% [13117:13124]
- Extent Scan Fragmentation ...................: 5.84%
- Avg. Bytes Free per Page.....................: 7757.4
- Avg. Page Density (full).....................: 4.16%

this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows!
select on the table sucks!

since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?

thanx

:-)

hemant

View 1 Replies View Related

Fragmentation -Urgent

Oct 13, 2000

Hi!

I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.

Thanks,
Saran

View 2 Replies View Related

Disk Fragmentation && SQL

Apr 14, 2004

One of my production servers has been determined to be 92% fragmented.

What's the proper procedure for defraging a database server?

I couldn't find anything very helpful in BOL, nor Knowledge Base.

Sidney Ives
Database Administrator
Sentara Healthcare

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved