Slow Query Duration leading to slow crawls

Hi all,

I have an issue that seems to have no answer on Google so I am hopeful someone can help here.

I have 4 application servers with two front ends my 2013 environment. The topology is:

Name

Admin

Crawler

Content Processing

Analytics Processing

Query Processing

Index Partition 0

Index Partition 1

WEB1

X

WEB2

X

APP1

X

X

APP2

X

X

APP3

X

X

X

APP4

X

X

X

I have two content sources. My site collection source and a file share with over 100000 files.

I am able to successfully crawl both sources with the dps hitting 60 which is good performance.

However, if I run a crawl the next day it slows down to 2-3 dps and the content processing servers log the ULS errors below.

Any insight would be greatly appreciated!

Slow Query Duration: 9294.96116126491
-------------------------------------------------------------------------
Slow Query StackTrace-Managed:  
 at Microsoft.Office.Server.Data.SqlSession.OnPostExecuteCommand(SqlCommand command, SqlQueryData monitoringData)   
 at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)   
 at Microsoft.Office.Server.Search.Analytics.SearchAnalyticsApi.UpsertSearchAnalyticsDocument(String fromURL, Guid tenantId, UInt16 pid, Int64 id, Byte[] payload)   
 at Microsoft.Ceres.SearchAnalytics.Operators.LinkDBAddProducer.Write(IRecord record)   
 at Microsoft.Ceres.ContentEngine.Processing.Writer.AbstractWriterProducer`1.ProcessRecord(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.Profiling.RecordCountingRecordSetSink.PutAndCountRecord(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.Profiling.RecordCountingRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithTryCatch(IRecord record)   
 at Microsoft.Ceres.ContentEngine.Processing.BuiltIn.RouterProducer.ProcessRecord(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.ContentEngine.Processing.Events.EventRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithoutTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.PushToOutput()   
 at Microsoft.Ceres.Evaluation.Processing.Producers.ProducerRecordSetSink.Put(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.ErrorHandling.HandleExceptionRecordSetSink.DoWithTryCatch(IRecord record)   
 at Microsoft.Ceres.Evaluation.Engine.PushPull.PushPullThread.DoRunPushPull()   
 at Microsoft.Ceres.Common.Utils.Concurrent.PoolTaskWrapper.Run()   
 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)   
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)   
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)   
 at Microsoft.Ceres.Common.Utils.Concurrent.PoolWorker.DoWorkIteration()   
 at Microsoft.Ceres.Common.Utils.Concurrent.AbstractWorker.Run()   
 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)   
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)   
 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)   
 at System.Threading.ThreadHelper.ThreadStart()
 -------------------------------------------------------------------------
 ConnectionString: 'Data Source=[DBSERVER];Initial Catalog=Search_Service_Prod_LinksStore;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: '[SOME SP URL]'     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: '370'     Parameter: '@Payload' Type: VarBinary Size: -1 Direction: Input     Parameter: '@PartitionBucket' Type: SmallInt Size: 0 Direction: Input Value: '370'


August 25th, 2015 10:04am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics