Defragmentation of Indexes on SQL 2005 Sharepoint Database

Question

Defragmentation of Indexes on SQL 2005 Sharepoint Database

j mangual

Ten Centuries

Points: 1197
More actions
September 3, 2008 at 12:19 pm

#123179

Server: MS 2005 Enterprise Edition SP2, OS MS 2003 Server Standard SP2
Ran script to rebuild indexes of a sharepoint database. Results shown on panel were:
Executed ALTER INDEX IX_MSSCrawledProperties_CID ON dbo.MSSCrawledProperties REBUILD
Executed ALTER INDEX IX_MSSCrawledProperties_PropName ON dbo.MSSCrawledProperties REBUILD
Executed ALTER INDEX IX_UserSites ON dbo.UserSites REBUILD
Executed ALTER INDEX IX_UserSites_SiteID_WssId ON dbo.UserSites REBUILD
Executed ALTER INDEX PK_MemberGroup_Id ON dbo.MemberGroup REBUILD
However when selecting record from the sys.dm_index_physical_stats I got the following results for the following fields:
TableName TableIndexName AVGFragPercent avg_page_space_used_in_percent
UserSites IX_UserSites 80 85.9550284161107
UserSites IX_UserSites_SiteID_WssId 80 80.4002965159377
MSSCrawledProperties IX_MSSCrawledProperties_CID 66.6666666666667 73.3135656041512
MSSCrawledProperties IX_MSSCrawledProperties_PropName 66.6666666666667 69.0305658512478
MemberGroup PK_MemberGroup_Id 50 59.5997034840623
PropertyList NULL 50 63.2196688905362
Seems that the rebuild of the indexes can't lower the AVGFragPercent less than 80% for some of the indexes and 50% for others? Nor can it lower the avg_page_space_used_in_percent which we should be aiming to 15%? Any advise :hehe::hehe:!!!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Warren Peace SSCertifiable Points: 5881 More actions · Answer 1

You might have some type of scheduled job running at the same time. Backup? Table Inserts? Maintenance of tables? SSIS package updating records? MAke sure all jobs that access the DB are not running. If that doesn't work try the below SQL script...

DECLARE @TableNameToReIndex VarChar(200)

DECLARE ListOfTables CURSOR FOR

SELECT table_name

FROM information_schema.tables

WHERE table_type = 'base table'

Order By table_name ASC

OPEN ListOfTables

FETCH NEXT FROM ListOfTables INTO @TableNameToReIndex

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Reindexing ' + @TableNameToReIndex + ' table' + ' ' + 'DBCC DBREINDEX (' + @TableNameToReIndex + ', ' + Char(39) + ' ' + Char(39) + ', 80)'

DBCC DBREINDEX (@TableNameToReIndex, ' ', 80)

FETCH NEXT FROM ListOfTables INTO @TableNameToReIndex

END

CLOSE ListOfTables

DEALLOCATE ListOfTables

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

j mangual (9/3/2008)
Seems that the rebuild of the indexes can't lower the AVGFragPercent less than 80% for some of the indexes and 50% for others? Nor can it lower the avg_page_space_used_in_percent which we should be aiming to 15%? Any advise :hehe::hehe:!!!!

Yep... find out what the FILL FACTOR is for each of those indexes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Warren Peace SSCertifiable Points: 5881 More actions · Answer 3

In the script above, change the fill factor of 80 to whatever you desire...

DBCC DBREINDEX (@TableNameToReIndex, ' ', 80)

Maksymilian Mulawa Ten Centuries Points: 1336 More actions · Answer 4

Increasing FILLFACTOR may degrade performance of Inserts/Updates on these tables. It's better to test it before it will go to PROD enviroment.

In official white paper from Microsoft commited by Bill Baer’s , Database Maintenance for SharePoint (

http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409) it's stated that:

For many situations the default server-wide fill factor level of 0 is optimal; however, for Microsoft Office SharePoint Server 2007, a server-wide setting of 70 is optimal to support growth and minimize fragmentation.
Although it is possible, we do not recommend that you set the fill factor for individual tables or indexes.

Cheers, Max

Check-out free open-source utility Sql Server Replication Explorer[/url]

madhu-686862 SSChampion Points: 10707 More actions · Answer 5

Did you find any solution for this rebuilding indexes?

did you change the fill factor?

can we do rebuid index task for sharepoint databases in sql server

http://support.microsoft.com/kb/932744/ this link says we can not use rebuild index task?

So how you are defragmenting the indexes of sharepoint databases?

and where to check the fill factor of an index?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Please include the number of pages for each index\table. If these are small tables they will never be defragmented fully.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Make sure there's no shrink operation running, autoshrink or a scheduled shrink.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

madhu-686862 SSChampion Points: 10707 More actions · Answer 8

hi,

In official white paper from Microsoft commited by Bill Baer’s , Database Maintenance for SharePoint (
http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409) it's stated that:
For many situations the default server-wide fill factor level of 0 is optimal; however, for Microsoft Office SharePoint Server 2007, a server-wide setting of 70 is optimal to support growth and minimize fragmentation.
Although it is possible, we do not recommend that you set the fill factor for individual tables or indexes.

We are maintaing sharepoint databases and from above statement, Iam confused that whether can we change the fillfactor to 70 or not? Because sharepoint databases are getting fragemented rapidly.

plz advice me on this

lamphear Valued Member Points: 61 More actions · Answer 9

(My first post!)

I just wanted to reiterate how much getting a response on this means to me. Microsoft says to set sharepoint databases to fill factor 70, so should we or not? At my (small) company we don't have a development SQL server or devel sharepoint server, so we tend to just follow Microsoft's guidelines for everything. I was going to adjust our fillfactor to 70 based on "For many situations the default server-wide fill factor level of 0 is optimal; however, for Microsoft Office SharePoint Server 2007, a server-wide setting of 70 is optimal to support growth and minimize fragmentation." from http://technet.microsoft.com/en-us/library/cc973100.aspx

Advice?

Thanks