Sql-server – change Fill Factor option for our database server

fill-factorsql serversql-server-2008-r2

I have a production database for ERP which has clustered and non clustered indexes. Some other tables have index type as heap and i don't understand what does heap means. Every month i have a job run this command sp_msforeachtable "dbcc dbreindex('?')" to rebuild all indexes in our database after that i checked the fragmentation and i found it to be still 98%. I searched on web to discover that our Fill factor option for our server is 0, so can i change it to suitable percentage and what factors I need to consider?

Best Answer

As stated in the documentation "Fill factor 0 and 100 are actually interchangeable and fill factor does not apply to heaps, which are tables without a clustered index.

Brent Ozar has pretty good writeup on fillfactor settings which I recommend that you look at but it boils down to this:

Don't spend too much time on the exact fill factor unless you can pinpoint specific performance problems to page splits and then change those from the defaults, for instance indexes on GUID columns which are modified frequently

Use Ola Hallengrens maintenance solution which rebuilds those indexes which are fragmented and skips the others making maintenance run faster and with less load on the server.

If your application uses quite a lot of heaps Ola's solution does not help rebuilding those. You can run alter table <tablename> rebuild to rebuild the heaps ( Alter Table rebuild command applies to SQL Server 2008 and above) but Tibor Karaszi has more intelligent solution which you can run periodically.

Related Question