SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

MSSQLFUN

I, Rohit Garg, am working as Consultant in IT Company. I am having an around 5 years of experience in MSSQL server & other Microsoft technologies. I am working as DBA in Microsoft SQL Server from last 5 years in e-Commerce, Telecom, Finance domain. In this tenure, I got a chance of working as Database administrator, Developer and trainer on SQL server 2000 to SQL Server 2012. I am holding Master’s degree in Computer Science along with certification in SQL Server & .Net. I like to learn new things by hand-on experience on regular basis. This journey is so far is delightful & valuable with the addition of wonderful friends.

How to use DTA (Database Tuning Advisor) with Plan Cache option?

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

Comments

Leave a comment on the original post [mssqlfun.com, opens in a new window]

Loading comments...