Sql-server – way to make SQL Server’s Database Tuning Advisor use more than one core

database-tuningperformancesql server

I'm using SQL Server's Database Engine Tuning Advisor to generate recommendations for performance improvements on my database server.

For the "consuming workload" step, it appears to use all available cores. However, for the much more time consuming "performing analysis" step, only one core appears to be utilized.

Is there a way to get it to use multiple cores for this step?

Best Answer

I expect the analysis is carried out serially, one query at a time, as doing so in parallel would be unreliable.

DTA produces recommendations by creating hypothetical indexes and evaluating the impact on a query's execution plan. If analysis were carried out on multiple queries at a time, the index created for one query could influence the analysis of another.

A hypothetical index is created using the undocumented command CREATE INDEX WITH STATISTICS_ONLY and as the name implies this creates just the statistics for the index, without building the physical structure.

Related Question