The Prepare Query Suggestions timer job in SharePoint Server 2013 takes longer than expected to run, and it consumes lots of tempdb resources.
The Prepare Query Suggestions timer job runs the proc_MSS_GetPopularQueries stored procedure internally. This contains joins and aggregations that don't scale well when the number of rows in the underlying table increases too much.
By default, SharePoint keeps up to 365 days of data in the underlying tables. This value should work for the vast majority of SharePoint users. However, if you are experiencing the issue that's described in the "Symptoms" section, we recommend that you lower the retention period to a reasonable value. To do this, follow these steps:
- Change the clean-up interval. To do this, launch a SharePoint management shell, and then enter the following PowerShell commands:
$ssa = Get-SPEnterpriseSearchServiceApplication -Identity "Search Service Application"
$ssa.QueryLogSettings.CleanupDays = <Days>
Note The <Days> placeholder represents the number of days that you want to set as the retention value. There is no "correct" value here; and you should start with a large number and then decrease it until you find the best retention period for your environment. For example, start with 270 days, then 180 days, and then 90 days. Make sure that you set a reasonable retention value for your production environment, as reducing it too much may affect other search features, including ranking, relevance, and query suggestions.
- Wait for the Query Logging timer job to run.
The Query Logging timer job checks which tables need cleanup, and it will run an internal stored procedure to purge the records from the underlying tables in a 24-hour period.
The behavior that's described in the "Symptoms" section complies with the product specifications. The resolution steps are recommended only if you are actually experiencing performance issues with the timer job, or if your database server is running out of tempdb space because of the proc_MSS_GetPopularQueries stored procedure.
Article ID: 3174023 - Last Review: Sep 20, 2016 - Revision: 1