Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
 Posted - 2006-11-04 : 09:18:47
|
| I have a job set up that does the dbcc reindex i am usingSet to Database maintenance plan optimizations set to regorganize data and index pages reorganize pages with the orginal amount of free space .....After applying SP4 ... I checked the logs Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SQL1' Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006 2:00:03 AM [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)... That means ever time record insert its going to split Most of the tables have fill factor of 80 Why would it report leaving 100% ......that means all tables have no space and i get page split everytime add new records.Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-04 : 10:21:49
|
| What's the index that you refer to set to (as the FILLFACTOR)?The default used to be to "leave 10% free" which caused Maintenance Plan to change the FILLFACTOR to 90% ...... the other option was to use the original settings for the index - i.e. as the developer intended - which strikes me as being a better option!Not sure this helps any with what you are seeing though, sorry.Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-04 : 12:35:06
|
Wouldn't "leaving 100% free space" imply that the table would be empty?  Actually, that is just SQL Servers odd way of telling you that it is rebuilding the indexes with a fill factor = 0, meaning it is using the original amount of free space specified when the index was created.That is what you said you wanted, so you are OK.CODO ERGO SUM |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-11-04 : 16:58:18
|
| Leaving 100% free space = means table empty ? None of my tables are empty ..Actually, that is just SQL Servers odd way of telling you that it is rebuilding the indexes with a fill factor = 0, meaning it is using the original amount of free space specified when the index was created.----So its doing the reindex with what is original left.....just odd way saying 100 free...What do you guys use...do you use Optimizations in backup jobs ..do you choose the regorganize data and index I get confused on the change free space per page percentage is that 10% free........so if i run it for 10% its like runningdbcc reindex databasname ,90Thanks |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-04 : 17:59:40
|
quote:
Originally posted by TRACEYSQL
i just found thishttp://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.aspSo from what im reading its basically leaving no space at the end ...so any new inserts will cause it to page splitso i best use change free space per page to 20% .... as most of my tables are fill factor 80Is that right ?
No, you're wrong.It is using the original amount of free space specified when the index was created, just like I told you in my first post.From SQL Server Books Online:"When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created."That is what you said you wanted, so you are OK.CODO ERGO SUM |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2006-11-04 : 18:27:52
|
| Thanks very much for clarifying .....I leave it as it is then what im running .I just never noticed the 100% in the logs before....only after sp4Great if index is set at 80 im doing it right leaving 20% free for more inserts updates i was getting concerned thinking 100% there was no room i get page splits all the timeIf i run the dbcc showcontig i be able to see the free space ? on index |
|
|
|
|
|
|
|
|
DBCC REINDEX