SQL Server 2012 comes up with new workload option in DTA (Database Tuning Advisor). In addition, on existing options File or Table, Microsoft gives option to fetch workload from plan cache of SQL Server. In this case, the DTA will select the top 1,000 events from the plan cache based on total elapsed time of the query (that is, based on the total_elapsed_time column of the sys.dm_exec_query_stats DMV). This can be increased using –n option of Database Tuning Advisor command line utility.
dta -E -D DatabaseName -ip –n 2000-s SessionName1
Let’s try it: –
Step 1: Execute query and ensure Cache has plan for required database
–Clear Plan Cache
–Execute fresh statement
SELECT * FROM [HUMANRESOURCES].[EMPLOYEE] WHERE BUSINESSENTITYID > 10
–Check Plan Cache has plan for ADVENTUREWORKS2012 database
SELECT * FROM SYS.DM_EXEC_CACHED_PLANS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
Step 2: Open up DTA and under the “General” tab select
– Workload: Plan Cache
– Database for work load analysis: AdventureWorks2012
– Database and tables to tune: relevant database and table you want to tune. In my case I selected all tables under AdventureWorks2012 database.
Step 3 – Leave other options as default. You can change them as per your requirements. Click on “Start Analysis” and it gives you recommendations as shown below.
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
Other Linked Profiles :-