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

DBCC FREEPROCCACHE

GO

–Execute fresh statement

SELECT * FROM [HUMANRESOURCES].[EMPLOYEE] WHERE BUSINESSENTITYID > 10

GO

–Check Plan Cache has plan for ADVENTUREWORKS2012 database

SELECT * FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE DBID=DB_ID(‘ADVENTUREWORKS2012’)

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 :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx