Query time expired for CME search

Question

Advanced search option in CME is timing out if the result expected is more than 250 items. On closed observation, found that if query takes more than 50 seconds then it times out and retrieving just 250 items taking more than 50 seconds is very strange.

Please note, this scenario is only happening in production where we have better infrastructure than development. In development same query is completed for approx 900 items in 20 seconds.

The query timeout error is as follows:

Error: Query timeout expired

Component: Tridion.ContentManager.CoreService
Errorcode: 3633
User: NT AUTHORITY\NETWORK SERVICE

StackTrace Information Details:
SQLUtilities.OpenRecordsetByStoredProcedure
SystemDAL.GetBulkStatusInfo
UtilitiesBL.GetBulkStatusInfo
SearchBLST.PostProcessListSearchResults
SearchBLST.GetListData
SearchBLST.GetSearchResults
   at Tridion.ContentManager.ContentManagement.SearchQuery.GetListResults(Int32 startRowIndex, Int32 maxRows)
   at Tridion.ContentManager.Query.GetListResults()
   at Tridion.ContentManager.CoreService.CoreServiceBase.GetSearchResultsXml(SearchQueryData filter)
   at SyncInvokeGetSearchResultsXml(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at Tridion.ContentManager.CoreService.TransactionSupportInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

We have same search infrastructure setup in development and production i.e. One master search server and other slave search servers. In development we have total 2 servers and in production we have 6 redundant servers.

Also, we have already tried following options:

1. Reindexing the CM database
2. Running the solr link for index optimization
3. Ran re-index tool.

Please suggest what can be done to improve the performance of the query in production?

Answer 1

If you have ensured that your DB is indexed, I.e. Indexes and stats are up to date, then check that you have sufficient paging/swap memory allocated (talk to your DBA).

The issue could also lie with the network between the DB and app servers. Perhaps the routers/switches are not properly routing the packets and they are making unnecessary loops. Do a packet trace to see what's going on there.

Also check your solr/jetty logs (see Tridion home folder) and compare them for abnormalities against your fast working environment.

Answer 2

In the meantime, we upgraded to 2013 sp1 however that also did not resolve the issue.

SDL support has shared a hotfix which addresses this timeout issue: CM_2013.1.0.88283

After applying the hotfix the query timeout issues are resolved.