CODEWITHSUNDEEP
sql-serverperformance
adrianm
adrianm

Reputation: 14736

Sql server tuning advisor report

I created a trace file of a days work (~10 hours) at a client and ran it in the SQL Server tuning advisor.

The tuning advisor ran over the weekend and the report says:

Time taken for tuning: 2 days, 13 hours, 58 minutes
Number of events in workload: 1762519
Number of events tuned: 67525
Number of statements tuned: 37258

I also summed the "frequency" column in the tuning log to 141236 skipped events

Does this mean that the advisor only looked at 12%, (141236+67525)/1762519, of the workload file in 62 hours?

I.e. is the recommendation only based on the first hour of the workload?

Is there a way to speed up the analysis or do I need to run it for a month?

Upvotes: 3

Views: 452

Answers (1)

John Sansom
John Sansom

Reputation: 41879

No, the DTA has processed your entire workload file.

You need to identify why the DTA skipped 141236 events. This could be for a number of reasons, such as the queries reference very small tables i.e. smaller than 10 data pages or because the queries reference tables that have not been selected for tuning.

I suggest you review your tuning log and cross reference with the following microsoft documentation in order to identify why you events have been skipped.

Troubleshooting the Database Engine Tuning Advisor

In particular you will want to read:

  • Why Events are not Tuned
  • Errors and Messages

Upvotes: 1

Related Questions