index fragmentation for Share point Content database

Question

Post reply

index fragmentation for Share point Content database

klnsuddu

SSCertifiable

Points: 5836
More actions
August 19, 2009 at 11:38 am

#225151

Hi,
I'm trying to find the index fragmentation in a share point content database in SQL Server 2005.
SELECT avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physicalstats (12, NULL, NULL, NULL, NULL)
REsults:
avg_fragmentation_in_percentPage _count
38.88888889 18
38.88888889 18
38.88888889 18
75 4
66.66666667 3
50 2
50 2
83.33333333 6
75 4
75 4
66.66666667 3
As the page count is less than 1000, can we ignore this index fragmentation?
thank you
Kln

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

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

klnsuddu SSCertifiable Points: 5836 More actions · Answer 1

klnsuddu

SSCertifiable

Points: 5836

August 19, 2009 at 4:14 pm

#1041890

Could you please advice me..

thanks

Jeffrey Williams SSC Guru Points: 90253 More actions · Answer 2

Yes, you can more or less ignore the index fragmentation for those tables. What I normally do is schedule a weekly index rebuild for smaller systems and just select all indexes to be rebuilt.

It doesn't hurt - as long as you have the maintenance window, and usually the weekends don't have a lot of users on Sharepoint at 3am.

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

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 3

We run index maintenance(rebuild/reorganise) every night on our sharepoint databases as our databases are highly used.

I don't see any harm in rebuilding/reorganising sharepoint indexes.

Vivek

Vivek Shukla - MCTS SQL Server 2008

klnsuddu SSCertifiable Points: 5836 More actions · Answer 4

I'm getting High Disk Quelength (for the Data drive, having .mdf files) alarms from Spot light whenever the Index rebuild/reorganize job runs.

Could you please post the Script you are using for Index rebuild/ reorganise for Share point databases...

Currently, I'm using Maintenance plan job for Index rebuild/ reorganis

thank you

klnsuddu SSCertifiable Points: 5836 More actions · Answer 5

Hi Vivek Shukla,

Could you please post the Script you are using for Index rebuild/ reorganise for Share point databases...

As you are maintaining Share Point databases in SQL Server, I have couple of questions to make sure:

1. While installing SQL Server, what collation settings you have selected?
2. What is the collation settings for your system databases?
3. What is the collations settings for your share point databases?

I appreciate your help

thank you

kln

Vivek Shukla SSCrazy Points: 2146 More actions · Answer 6

Could you please post the Script you are using for Index rebuild/ reorganise for Share point databases...

http://msdn.microsoft.com/en-us/library/ms175008.aspx

I have used script listed on this page but i have modified it to replace cursor and run it for all databases in production server rather than just one database.

As you are maintaining Share Point databases in SQL Server, I have couple of questions to make sure:

1. While installing SQL Server, what collation settings you have selected?

Latin1_General_CI_AS

2. What is the collation settings for your system databases?

Latin1_General_CI_AS

3. What is the collations settings for your share point databases?

Latin1_General_CI_AS_KS_WS

Hope this helps!

Vivek

Vivek Shukla - MCTS SQL Server 2008

klnsuddu SSCertifiable Points: 5836 More actions · Answer 7

Thank you,

http://msdn.microsoft.com/en-us/library/ms175008.aspx
I have used script listed on this page but i have modified it to replace cursor and run it for all databases in production server rather than just one database.

I'm also using the same script, but I'm running manually in every database. I tried to make it work for all database and create a SQL Agent job BUT I did not make it work. I appreciate if you share the script you are using.

1. While installing SQL Server, what collation settings you have selected?
Latin1_General_CI_AS
I have selected the default Collation settings SQL_Latin1_General_CP1_CI_AI
2. What is the collation settings for your system databases?
Latin1_General_CI_AS
Our system database have the collation as SQL_Latin1_General_CP1_CI_AI
3. What is the collations settings for your share point databases?
Latin1_General_CI_AS_KS_WS
Share Point databases Latin1_General_CI_AS_KS_WS
So system databases have the collation settings as SQL_Latin1_General_CP1_CI_AI
user databases have the collation settings as Latin1_General_CI_AS_KS_WS

while installing SQL Server I selected default collations SQL_Latin1_General_CP1_CI_AI? Do I need to change the collations to Latin1_General_CI_AS_KS_WS?

thank you

gary1 SSC-Insane Points: 21152 More actions · Answer 8

http://msdn.microsoft.com/en-us/library/ms175008.aspx
I have used script listed on this page but i have modified it to replace cursor and run it for all databases in production server rather than just one database.
I'm also using the same script, but I'm running manually in every database. I tried to make it work for all database and create a SQL Agent job BUT I did not make it work. I appreciate if you share the script you are using.

I too need that script to work for all databases in an instance so that I can create a SQL Agent job.

Anybody can post the full script which work for all databases in an instance.

thank you very much