Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2000 Indexing Expand / Collapse
Author
Message
Posted Monday, August 23, 2004 8:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/sqls






Follow me on Twitter:
@way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #133469
Posted Wednesday, September 15, 2004 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,901, Visits: 1,806

I work in a development environment where data is relatively small, even when we do data migration jobs.

This gives me the luxury of running DBCC DBREINDEX as part of the nightly maintenance plans.

On a client's site we scheduled in a DBCC DBREINDEX and noticed that a 600Mb database shrunk to 250Mb.

Obviously we could have shrunk the physical file as well but as data is input at quite a high rate we decided to leave the file as is to prevent an unscheduled  resize happening. 

This shows that on very large databases there is the potential to save on backup time as well.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #137092
Posted Wednesday, September 15, 2004 6:12 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, August 26, 2014 8:57 AM
Points: 8,369, Visits: 736
However David I would suggest monitor that site regularly. The reason you may have gained so much space back could have been due to the number of page splits that have occurred with regards to the database. If you find the data grows drastically over a short period of time and a large amount of space is regained by running DBCC DBREINDEX then you may need to evaluate the FILL FACTOR for the Clustered Index or even change the clustered index to a non-clustered and pick a different index that suffers fewer data shifts and make the clustered, but there you will need to weigh time for queries with this difference.


Post #137122
Posted Wednesday, September 15, 2004 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 22, 2005 2:30 AM
Points: 4, Visits: 1

This article is one of the best, most informative I have read on SQL 2000 table indexing. While I have always been able to interpret the output from DBCC ShowContig and act accordingly on the results, I have never fully understood what the relationships between the numbers meant.

Well done Steve Jones and thanks you

Steve Walker

DBA - See Tickets 




Post #137134
Posted Wednesday, September 15, 2004 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:40 AM
Points: 15, Visits: 56
As always Par Execellent!!!  


Post #137135
Posted Wednesday, September 15, 2004 7:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, Visits: 76

As usual, nice article Steve. I've read other articles and explanations of showcontig and still came away confused, but this puts it into a more clear light. I also have the luxury of running dbcc dbreindex on our production database, but it does not shrink the data like David experienced (at least not very much). We only run it once per release, or every few months.

-Vic

Post #137158
Posted Wednesday, September 15, 2004 7:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,901, Visits: 1,806

I am still not 100% clear as to how to decide the FILL FACTOR.

My understanding was that if you have a high input rate into a table then the indices need a lower fill factor (whatever lower may be).  If your tables are virtually read only then you can get away with a much higher FILL FACTOR.

If I look at the value for my server using sp_configure this returns zero so I am not sure what this means.

I could also do with an idiots definition of PAD_INDEX.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #137159
Posted Wednesday, September 15, 2004 1:13 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, August 26, 2014 8:57 AM
Points: 8,369, Visits: 736

With SQL if you are adding data to a table and the data will always be inserted as a new row at the end of the data then 100% or 0% (which is 100% btw) is beter with regards to coservation of space.

Many times folks will put a fill factor of 90% on fields such as a identity field. The problem is they have wasted space that will never be reclaimed and page splits will still be as prevalent.

Ex. Say you have and indentity field indexed. Now suppose only 10 recors fit per page and at index rebuild there was 37 records.

Index by page

Page 1

1
2
3
4
5
6
7
8
9
10

Page 2

11
12
13
14
15
16
17
28
19
20

Page 3

21
22
23
24
25
26
27
28
29
30

Page 4

31
32
33
34
35
36
37

Now add 3 records and thus

Page 4

31
32
33
34
35
36
37
38
39
40

Now add two more records

Page 4

31
32
33
34
35

Page 5

36
37
38
39
40
41
42

Now it might be that SQL will create a new page when 41 is actually inserted I haven't looked to see if it is that smart (I think it would be)

But the point is the data in pages 1-3 are never affected again since the data is at the end of the index. If you had used a fill factor of 90 then only 9 records would be in each page to begin with, with 1 extra slot left in the example.

Due to this 1 - 37 records would have taken 5 pages to begin with instead of 4. If say you had had 37000000 records you would have a lot of unused space in the early pages never ging to be reclaimed. So in this case a fill factor of 100% is best.

That is what I am talking about.


Now for PAD_INDEX , this will by default be the same as FILL FACTOR. Basically PAD_INDEX is not the non-leaf pages. It works just like FILL FACTOR, you can however override it with a different value if you choose.

From BOL

"Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum. "

"Note  The number of rows on an intermediate index page is never less than two, regardless of how low the value of FILLFACTOR."

However I will say this.

Basically these will define the range between highest value and lowest on a specific page. It uses these like a Rolladex to decide which page contains the given data from the index. There will always be enough room for 1 more record after an index rebuild no matter what but I feel most folks can get away with nearer 100% no matter what there fill factor but I am still research true effect based on condition.

Kinda Brain-Dead right now, hope that helps.




Post #137237
Posted Thursday, September 16, 2004 1:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:44 PM
Points: 2,901, Visits: 1,806

Great thanks, I think the FILLFACTOR and PAD_INDEX are prime candidates for an article.

So a page split is expensive because it copies half the records from the last page into the new page?

If you have a low fill factor, say 20%, in your example this equates to 2 records per page.  Doesn't this mean that every 2nd insert causes a page split?



LinkedIn Profile
Newbie on www.simple-talk.com
Post #137300
Posted Thursday, September 16, 2004 3:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61

David,

No, though that's the impression I got at first. The key to it is that the FILLFACTOR determines how much of the index is filled ***when it's first built***. After that, the index gets filled until the point where no further entries can be made, at which point a page split occurs, and as you rightly say, half of the records get copied into the new page, and the record pointers adjusted accordingly.  Only when the index is rebuilt does the FILLFACTOR setting get re-applied to the index, and it then becomes 20% full again.  Otherwise, regardless of the its setting, you'd always end up getting page splits the moment a new record was inserted if the FILLFACTOR was constantly maintained, 'on-the-fly'.

With regard to the page splits, it's expensive because a new page has to be allocated, which may also mean a new extent has to be allocated if the current one is full. In addition, the records have to be moved (ie: both copied AND marked as deleted), and the pointers to the records have to be re-adjusted - all lots of performance sapping disk I/O.




Jon
Post #137312
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse