1

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?

4

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.

  • I agree with Spörri's comments. Just a couple of other notes: Those that are 98% fragmented: are you looking at an index or a heap? As noted, rebuilding an index does nothing for a heap, since a heap isn't an index. And if it is an index, then you likely have very few pages, where fragmentation is unavoidable and irrelevant. And DBCC DBREINDEX is an ancient command. I agree that you should use a good maint solution (like Ola's). And, it might even be questionable where rebuilding index will buy you anything in the and, assuming you have some pretty modern storage. – Tibor Karaszi Nov 27 '19 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.