Page Splits, Fill Factors and Naked Women

Okay, the "Naked Women" portion was just to get you to look at my post. Pathetic but it worked now didn't it?

E1 ships with a default fill factor for SQL Server of '0', which basically means that each leaf level page is filled to capacity and leaves no room for additional rows without the need for page splitting. While a fill factor of 0 is an efficient use of page space I think it sucks as a default value.

Questions:

Does anyone know how to change the default fill factor in E1 so that, if I change the fill factor in SQL, the old fill factor is not re-introduced if the index is regenerated within E1? I would like to change the fill factor to something I think is more reasonable but would like to not have that value overridden by E1 if an index is regenned.

I suppose I could set up a maintenance plan in SQL to rebuild the indexes weekly and use the fill factor I desire so even if someone does regen the index, at the end of the week it will be rebuilt with my fill factor and not the E1 default.

Thoughts?

Yep, it sure did ;-)

Unlike how it does it with Oracle, in case of SQL Server JDE does not really care, nor allow you to change it - it assumes the default seting, as defined in SQL Server.

Run:

sp_configure "fill factor"

to find out what it is now. Your DBA must have have changed it, because the default out-of-the-box is 80%.

Actually, the "efficient use of page space" it would become, if changed to 100% ;-)

0% may have a special meaning (as when used in "DBCC DBREINDEX", as an example), or be exactly opposite of "efficient use of page space" ;-)

[ QUOTE ]
Actually, the "efficient use of page space" it would become, if changed to 100% ;-)

0% may have a special meaning (as when used in "DBCC DBREINDEX", as an example), or be exactly opposite of "efficient use of page space" ;-)

[/ QUOTE ]

I am pretty sure that the default fill factor is 0, which Microsoft says is the functional equivalent of 100(%). I think what I am going to do is change the fill factor to somewhere around 90% and then do a DBCC DBREINDEX which will have the effect of regenerating all indexes with the new default fill factor.

I suppose if E1 does indeed take the server default then I should be okay.

Thanks Alex.

I'm not a DBA, but I'm gaining more and more responsibility at my company in making JDE run efficiently. Plus I'm always interested in learning more, so I have a few questions.

From what I've read, the fill factor is implemented only when the index is created and not maintained as data is added, deleted, or updated. I assume that is the reason that you are regenerating your indexes weekly. So how much of a performance increase do you really see by reducing your page splits and is it worth the hit that you'll take to your read performance? Are you assuming that your data will on average not increase beyond 10% per week and so that is why you've chosen 90%? I understand that besides decreased read performance, that increased storage is a side effect. I would assume that if you set the fill factor to 90% then your increase in storage is 11.11%? Are there any other hidden "costs" that you take by changing this setting? Lastly, does anyone know of a difference between SQL 2000 and 2005 on this topic. We're looking to upgrade in the next few months but I'm less familiar with what 2005 has to offer (although I heard that index generation is more dynamic)

I understand that tuning is different for every site and that all of the usual factors play (hardware, throughput, ect.,) but I was looking for some philisophical responses.... especially from the experts that are working on Christmas day.

P.S. - I admit that I was intially only interested in finding out how the naked women factored in...

[ QUOTE ]
I'm not a DBA, but I'm gaining more and more responsibility at my company in making JDE run efficiently. Plus I'm always interested in learning more, so I have a few questions.

From what I've read, the fill factor is implemented only when the index is created and not maintained as data is added, deleted, or updated. I assume that is the reason that you are regenerating your indexes weekly. So how much of a performance increase do you really see by reducing your page splits and is it worth the hit that you'll take to your read performance? Are you assuming that your data will on average not increase beyond 10% per week and so that is why you've chosen 90%? I understand that besides decreased read performance, that increased storage is a side effect. I would assume that if you set the fill factor to 90% then your increase in storage is 11.11%? Are there any other hidden "costs" that you take by changing this setting? Lastly, does anyone know of a difference between SQL 2000 and 2005 on this topic. We're looking to upgrade in the next few months but I'm less familiar with what 2005 has to offer (although I heard that index generation is more dynamic)

I understand that tuning is different for every site and that all of the usual factors play (hardware, throughput, ect.,) but I was looking for some philisophical responses.... especially from the experts that are working on Christmas day.

P.S. - I admit that I was intially only interested in finding out how the naked women factored in...

[/ QUOTE ]

So now that I have brought JDEList to a new cultural low I suppose we should get back to business huh?


My purpose for re-indexing weekly is to limit index fragmentation and to limit page splits. I am tossing around the merits of changing the fill factor for all indexes or trying to identify the particular tables/indexes that are causing the increased page splits. The plus side of changing the server-wide fill factor is that it is easy to do. You have already mentioned the downsides of changing fill factor server-wide (increased space and decreased read performance).

There are also costs to running DBCC DBREINDEX weekly as well. It is a very costly function performance-wise and has an impact on anything running at the time as it generates exclusive locks while running. That, plus the fact that the re-index is a logged operation (generating large transaction logs) means that it is not free of costs.

I am thinking more now about identifying the particular table/index that is filling so much during the week that it is causing splits, changing the fill factor for that and leaving the rest alone to be handled by the weekly re-index. However, this brings me back to my original issue: if someone then generates that index within E1, the fill factor goes back to the SQL Server default.

Thanks for taking the time to add to the discussion.

Well, when you identify which E1 table(s) you are going to target for this optimization, I would be interested..... if you don't mind sharing.

I just had a healthy conversation with one of our DBAs (the one that didn't take the week off) and now I'm interested in compiling a list of tables to monitor for fragmentation. Apparently we don't regularly rebuild indexes in our JDE database and infrequently defrag so I'm sensing an opportunity (one of many) for improvement.

By the way, what version of E1 and SQL are you running?

[ QUOTE ]
Well, when you identify which E1 table(s) you are going to target for this optimization, I would be interested..... if you don't mind sharing.

I just had a healthy conversation with one of our DBAs (the one that didn't take the week off) and now I'm interested in compiling a list of tables to monitor for fragmentation. Apparently we don't regularly rebuild indexes in our JDE database and infrequently defrag so I'm sensing an opportunity (one of many) for improvement.

By the way, what version of E1 and SQL are you running?

[/ QUOTE ]


I generally defragment nightly (on systems that are not heavily used at night) and reindex weekly.


A good defrag script. Create the procedure below and use Agent to run dbo.sp_dba_DefragIndexes nightly.
====================

/******
Object: Stored Procedure dbo.sp_dba_DefragIndexes version 2
Script Author: Robert Davis, [email protected]
Purpose: Defrag all indexes in a given table or database.
******/

Use database_name
Go

Create Procedure dbo.sp_dba_DefragIndexes
@Table sysname = Null, -- Table in which to defrag the indexes
@ShowDetail bit = 0
As

If (Not Exists (Select 1 From sysobjects with(nolock) where id = object_id(@Table) And xtype = 'U') And @Table Is Not Null) Or @Table = '?'
Begin
Print '''' + @Table + ''' is not a valid table object in this database.' + char(10)
Print 'Proper syntax:'
Print 'Exec dbo.sp_dba_DefragIndexes ' + char(10) + space(5) + '@Table = { [table_name] | ''?'' }' + char(10)
Print 'table_name'
Print space(5) + 'Is the table for which to defragment an index. Table names must conform to the rules for identifiers.'
Print space(5) + 'Table_name is optional, procedure will run for all tables in database if parameter not specified.'
Print '''?'''
Print space(5) + 'This help message will be displayed.'
End
Else
Begin
Declare @IndexID int,
@TableID int,
@SQL varchar(1000)

Declare @IndexList Table (TableID int not null, IndexID int not null)

Insert Into @IndexList (TableID, IndexID)
Select si.id, si.indid
From sysindexes si with(nolock)
Inner Join sysobjects so with(nolock) on so.id = si.id
Where so.xtype = 'U' -- User Table
And so.id = Case When @Table Is Not Null Then object_id(@Table) -- if null runs for all tables
Else so.id End
And si.indid Not In (0, 255)
And si.rows > 0 -- If 0 rows, then nothing to defrag
Order By si.id, si.indid

Declare crsIndex Cursor fast_forward read_only
For Select TableID, IndexID
From @IndexList

Open crsIndex

Fetch Next From crsIndex Into @TableID, @IndexID

While @@Fetch_Status = 0
Begin
If @ShowDetail = 0
Begin
Set @SQL = 'DBCC INDEXDEFRAG (0, ' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ') WITH NO_INFOMSGS'
Exec(@SQL)
End
Else
Begin
Set @SQL = 'DBCC SHOWCONTIG (' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ')'
Exec(@SQL)
Set @SQL = 'DBCC INDEXDEFRAG (0, ' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ') WITH NO_INFOMSGS'
Exec(@SQL)
Set @SQL = 'DBCC SHOWCONTIG (' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ')'
Exec(@SQL)
Print char(10) + char(10)
End


Fetch Next From crsIndex Into @TableID, @IndexID
End

Close crsIndex
Deallocate crsIndex
End
GO

[ QUOTE ]
[ QUOTE ]
Well, when you identify which E1 table(s) you are going to target for this optimization, I would be interested..... if you don't mind sharing.

I just had a healthy conversation with one of our DBAs (the one that didn't take the week off) and now I'm interested in compiling a list of tables to monitor for fragmentation. Apparently we don't regularly rebuild indexes in our JDE database and infrequently defrag so I'm sensing an opportunity (one of many) for improvement.

By the way, what version of E1 and SQL are you running?

[/ QUOTE ]


I generally defragment nightly (on systems that are not heavily used at night) and reindex weekly.

[/ QUOTE ]

I just wanted to clarify that the fragmentation to which I am referring here is SQL Server index fragmentation, different from OS filesystem fragmentation. File defragmentation should also be performed occasionally, or better yet by using Diskeeper to keep the filesystem from getting fragmented.