Search is a critical technical piece in Sharepoint, and a lot of "new" services are based on it in order to consolidate data, such newsfeeds. And it's always a little bit magical in the background.
We have around 7 millions documents in our farm, first full crawls were running at correct speed (~70 document per seconds) but time after time, this crawl rate went down and drop of at 1.7 document per second (dps) but CPU was still acceptable
CPU level of our farm, last peak is when we've restarted the search for all content sources
1.7 could be acceptable, except if you have an incremental search (after a data migration) of 600 000 documents and endless fears of restarting a new full crawl.
A further look in the ULS of one of our search server reveal that some stored procedure were slow
ConnectionString: 'Data Source=XXXXXXXXXXX;Initial Catalog=Search_Service_Application_LinksStoreDB_9bdb1bf60a0e4ea5a20bdb218da7f953;Integrated Security=True;Enlist=False;Pooling=True;Min Pool Size=0;Max Pool Size=100;Connect Timeout=15' Partition: NULL ConnectionState: Open ConnectionTimeout: 15
SqlCommand: 'dbo.proc_MSS_UpsertSearchAnalyticsDocument' CommandType: StoredProcedure CommandTimeout: 0 Parameter: '@FromURL' Type: NVarChar Size: 4000 Direction: Input Value: 'https://somesharepointsite.com...' Parameter: '@TenantID' Type: UniqueIdentifier Size: 0 Direction: Input Value: '0c37852b-34d0-418e-91c6-2ac25af4be5b' Parameter: '@Pid' Type: SmallInt Size: 0 Direction: Input Value: '1' Parameter: '@ID' Type: BigInt Size: 0 Direction: Input Value: '2402973' Parameter: '@Payload' Type: VarBinary Size: -1 Direction: Input Parameter: '@PartitionBucket' Type: SmallInt Size: 0 Direction: Input Value: '2717'
Slow Query Duration: 17499.132711001
around 17 seconds for running an analytic update, maybe not the root cause, but definitively an issue.
a quick look on the SQL server activity monitor with SQL Server Management Studio (SSMS) helped to confirm the impacted database SearchServiceApplicationLinksStoreDB9bdb1bf60a0e4ea5a20bdb218da7f953 and the table itself MSSSearchAnalytics
a quick look on the out of the box & automatically created statistics showed up that they never have been calculated. We forced the recalculation
UPDATE STATISTICS [Search_Service_Application_LinksStoreDB_9bdb1bf60a0e4ea5a20bdb218da7f953].[dbo].[MSSSearchAnalytics]
WITH FULLSCAN
and voilà, >94 documents per second.
there is little few information about updating the statistics on a sharepoint environment, especially because it evolved between sharepoint 2007/2010 & 2013. Statistics are updated for a bunch of databases with a background sharepoint job, especially content databases, but no clue for others.