DEFRAG AND DBCC DBREINDEX NOT UPDATING

Question

DEFRAG AND DBCC DBREINDEX NOT UPDATING

cleber

Valued Member

Points: 73
More actions
October 19, 2005 at 2:12 pm

#152591

Hello,
I did all commands posted by the guys on the forum like
DBCC SHOWCONTIG ('CADCAM')
sp_updatestats
DBCC UPDATEUSAGE ('Power','CADCAM')
DBCC DBREINDEX
I did the maintanance plan and the Avg. Page Density (full) is not changing
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6670.0
- Avg. Page Density (full).....................: 17.59%
Someone knows a trick?

Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

dbcc dbreindex will rebuild your indexes with their original fill factor and this why you will have a low average page density. You find out the original fill factor from the sysnidexes table of the database.

Anyway if you want a higher page density try dbcc dbreindex and specifya fill factor. e.g.

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)

this will do it with a fill factor of 80%

If you need help determining fill factors there a good article on this site.

hth

David

cleber Valued Member Points: 73 More actions · Answer 2

Thanks David!

I know just that if the table is read only, the best choice is to use 100% of fill factor

Is that true?

I dont found the article, do you have the link?

Thanks again

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

If the table was read only yes you would want a very high fill factor as there will be no updates to that could cause page splitting etc.

The fill factor you should use depends on how many updates are being made to the table as you want to minimise page splits but you also don't want such a low fill factor that sql server is having to traverse a huge number of extra index pages.

Anyway here's the link

http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp

To get the original fill factor from sysindexes do something like

select name, origfillfactor

from sysindexes

where name = 'nameofyourindex'

The default fillfactor is 0 which is similar to 100% but it tries to leave room to insert one row

hth

David

cleber Valued Member Points: 73 More actions · Answer 4

Hello David,

I read the article, its very good and very complex....

Just to begin i will show an example

I have a table that every day must be truncated its like a stage table...

And the call center software update and select data every second.

So the table is 0% FILLFACTOR with these settings:

- Pages Scanned................................: 1970

- Extents Scanned..............................: 247

- Extent Switches..............................: 246

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [247:247]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 4.05%

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

- Avg. Page Density (full).....................: 99.41%

Have i to increase my fill factor to have a better Page Density?

Have i to measure the page splits on Sql Server?

Can you help me with my first steps?

Thanks dude!

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 5

Yes to get a better page density you have to increase your fill factor.

You'll be able to tell if you have alot of page splits by looking at

Logical Scan Fragmentation ..................: 0.00%

Extent Scan Fragmentation ...................: 4.05%

these should both be low

hth

David

cleber Valued Member Points: 73 More actions · Answer 6

David

I was reading an article at Microsoft Msdn website and they wrote:

If writes are a substantial fraction of reads, the best approach in a very busy OLTP system is to specify as high a FILLFACTOR as feasible that will leave a minimal amount of free space per 8-KB page but still prevent page splitting and still allow the SQL Server to reach the next available time window for rebuilding the index. This methodology balances I/O performance (keeping the pages as full as possible) and page splitting avoidance (not letting pages overflow). This may take some experimentation with rebuilding the index with varying FILLFACTOR and then simulating load activity on the table to validate an optimal value for FILLFACTOR. Once the optimal FILLFACTOR value has been determined, automate the scheduled rebuilding of the index as a SQL Server Task. For More information on automating tasks, search in SQL Server Books Online for the string "creating a task."

In the situation where there will be no write activity into the SQL Server database, FILLFACTOR should be set at 100 percent so that all index and data pages are filled completely for maximum I/O performance.

So, In my case a have to read a lot of data and sometimes send an update.

Setting a fillfactor with 70% (I read a lot of data and sometimes i update) will i increase my index file?

Do you have any suggestion?

Thanks

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 7

If i'm desigining a new database and very little idea about the usage in terms of updates versus reads I usualy start with a fill factor of 95% and then adjust this as necessary pretty much as per the article.

If however I know that were be a lot of writes I usually start at 90%.

There is no hard and fast rule on this, the correct fill factor depends on how often you rebuild your indexes and the no of writes versus the number of reads. I wouldn't worry to much if your indexes suffer from a little bit of external fragmentation unless you carry out a lot of index scans i.e. the queries you run against this table use an index scan in their execution plan. In my opinion a bit of external fragmentation is much better than internal fragmentation (Internal fragmentatin just means that the index is a lot bigger than it needs to be because the index pages are not full)

So in short its a balancing act between leaving enough space in the index pages so that new inserts don't cause page splits between index rebuilds and making sure that the index pages are as full as possible so that the index is not any bigger than it has to be

hth

David

dmanju Say Hey Kid Points: 685 More actions · Answer 8

If you set the fill factor at 70%, you would have to scan more pages each time you have to read because the page denisity would be less. It will however, reduce the number of page splits. The best way to determine a fill factor value is to start off at a good approximate value (say 80%) and monitor the number of page splits in perf monitor. If the page splits is high, you can reduce the number to maybe 75 or 70 and monitor the page splits value again.

To answer the first question that was posted on this thread, you only have data in 1 page. and only 17% of that page is filled. so there is no problem there. (unless Iam missing something)

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 9

Heh good point shows how much attention I paid to the output of dbcc showcontig didn't even pay attention to the number of pages scanned.

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 10

For the very first time you have to know some basics:

dbreindex is not useful if your table is less then a segment (8 pages)

Also if you do not have clustered index on your table, the table itself wont be defragmented.

Bye
Gabor