Sharepoint, SQL Server ja päättymätön MSSEARCH.

Sattuipa päivänä muutamana, että asiakkaan Sharepoint-saitti innostui menemään rikki. Valvonnan indikaattorit eivät sanoneet muuta kuin että saittia ei näy - edustakone on a-ok, kantakone pörpöttää hienosti ja noin periaatteessa kaikki on ehjää, mutta mikään ei toimi. IIS on pystyssä, kantapalvelin palvelee. Mikäpä neuvoksi?

Asiaan liittyi lisäksi hassunhauskoja piirteitä - kun edustakoneelta avasi selaimen, se ei suostunut menemään minnekkään. PING kyllä kulki reitittimelle.

Selvittelyn jälkeen valkeni että koneessa oleva Sharepointin indekseri hörppäsi 4 gigatavua (!) muistia, ja sillä oli kantaan auki vaatimattomat 60.000 TCP-yhteyttä. Jokainen portti oli varattu, ei ihme ettei veppi juurikaan selaantunut.

Ilmiö tapahtuu tilanteessa jossa käytössä on SQL Server 2005 SP1, ja SQL-palvelimella ajetaan "rebuild index" -toiminto (vaikkapa jonkin maintenance-jobin yhteydessä). SQL 2005 SP1:ssä tämä johtaa siihen että indeksien "Allow duplicate keys" -asetus menee pois päältä. Sharepoint-serverillä ajettava MSSEARCH.EXE avaa loputtomasti uusia kantayhteyksiä, kuluttaa muistia suuret määrät, ja lopulta Sharepoint-kone ei saa esim. aikaiseksi enää uusia TCP-yhteyksiä lainkaan.

Rikotut indeksit pitää luoda väintään kahdessa kannassa uudelleen. Ensimmäinen on INSTANSSINNIMI_Search_DB, ja toinen WSS-palveluiden vastaava kanta. Kantojen nimet ovat yhden Sharepoint-instanssin osalta ne kaksi kantaa, jotka päättyvät _Search_DB -tekstiin. Jos samassa SQL-serverissä on useita Sharepoint-instanssien kantoja, tulee kukin korjata erikseen.

Referenssin vuoksi alla ne taulut, joiden indeksien asetukset menevät SP1:ssä rikki.

Taulunnimi	Indeksinnimi
MSSAlertDocHistory	IX_AlertDocHistory
MSSAnchorChangeLog	IX_MSSAnchorChangeLog
MSSAnchorPendingChangeLog	IX_MSSAnchorPendingChangeLog
MSSCrawlChangedSourceDocs	IX_MSSCrawlChangedSourceDocs
MSSCrawlChangedTargetDocs	IX_MSSCrawlChangedTargetDocs
MSSCrawledPropSamples	IX_MSSCrawledPropSamplesByDocid
MSSCrawlErrorList	IX_MSSCrawlErrorList_hrResult
MSSCrawlHostList	IX_MSSCrawlHostList_Name
MSSCrawlQueue	IX_MSSCrawlQueue
MSSDocSdids	IX_MSSDocSdids

Alla TSQL-skripti joka yhden kannan osalta ensin tuhoaa väärien asetusten indeksit ja luo ne sitten uudelleen duplikaatit sallivin asetuksin.

USE [SHAREPOINT-INSTANSSI_Search_DB]
GO

DROP INDEX [IX_MSSDocSdids] ON [dbo].[MSSDocSdids] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSCrawlQueue] ON [dbo].[MSSCrawlQueue] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSCrawlHostList_Name] ON [dbo].[MSSCrawlHostList] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSCrawlErrorList_hrResult] ON [dbo].[MSSCrawlErrorList] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSCrawledPropSamplesByDocid] ON [dbo].[MSSCrawledPropSamples] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSCrawlChangedTargetDocs] ON [dbo].[MSSCrawlChangedTargetDocs] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MssCrawlChangedSourceDocs] ON [dbo].[MSSCrawlChangedSourceDocs] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSAnchorPendingChangeLog] ON [dbo].[MSSAnchorPendingChangeLog] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_MSSAnchorChangeLog] ON [dbo].[MSSAnchorChangeLog] WITH ( ONLINE = OFF )
GO

DROP INDEX [IX_AlertDocHistory] ON [dbo].[MSSAlertDocHistory] WITH ( ONLINE = OFF )
GO

CREATE UNIQUE CLUSTERED INDEX [IX_AlertDocHistory] ON [dbo].[MSSAlertDocHistory]

(
[UrlSignature] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MSSAnchorChangeLog] ON [dbo].[MSSAnchorChangeLog]

(
[CrawlId] ASC,
[TargetDocId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MSSAnchorPendingChangeLog] ON [dbo].[MSSAnchorPendingChangeLog]

(
[CrawlId] ASC,
[TargetDocId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MssCrawlChangedSourceDocs] ON [dbo].[MSSCrawlChangedSourceDocs]

(
[CrawlId] ASC,
[DocId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MSSCrawlChangedTargetDocs] ON [dbo].[MSSCrawlChangedTargetDocs]

(
[CrawlId] ASC,
[DocId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MSSCrawledPropSamplesByDocid] ON [dbo].[MSSCrawledPropSamples]

(
[DocId] ASC,
[CrawledPropertyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_MSSCrawlErrorList_hrResult] ON [dbo].[MSSCrawlErrorList]

(
[hrResult] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_MSSCrawlHostList_Name] ON [dbo].[MSSCrawlHostList]

(
[HostName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IX_MSSCrawlQueue] ON [dbo].[MSSCrawlQueue]

(
[CrawlID] ASC,
[DocID] ASC,
[TransactionType] ASC,
[Scope] ASC,
[TransactionFlags] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [IX_MSSDocSdids] ON [dbo].[MSSDocSdids]

(
[DocId] ASC,
[Type] ASC,
[HasPluggableSecurityTrimming] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]