Using Database Engine Tuning Advisor...

Objective : Database Engine Tuning Advisor is used to increase the performance of the SQL Queries. Using SQL Server 2008 Database Engine Tuning Advisor is quite different from SQL Server 2012 - Denali Version which has Plan Cache. Kindly refer the Microsoft Site.

Step 1: Create a SQL Query by connecting the SQL Server management Studio and Selecting the Adventure Works OLAP Data Warehouse Database. 


Step 2: Create a SQL Query as given below.

Select

            C.FirstName,

            C.LastName,

            C.EnglishEducation,

            C.EnglishOccupation,

            C.EmailAddress,

            C.MaritalStatus,

            C.YearlyIncome,

            I.SalesAmount,

            I.TaxAmt,

            I.UnitPrice

From DimCustomer C

Inner Join FactInternetSales I on C.CustomerKey = I.CustomerKey

Step 3: Right click the Query, a Menu will appear as shown. Select the "Analyze Query in Database Engine Tuning Advisor"

Step 4: Database Engine Tuning Advisor opens in Separate window. Query Radio button under the Work Load Group gets automatically Selected. Rename you Session Name as Required.

Step 5: From the Menu, Select Action and Click on Start Analysis to run the Process.

Step 6: Process ran successfully. There are few other tabs such as Recommendations and Report Tabs created newly after the Process completion.

Step 7: Process ran successfully. There are few other tabs such as Recommendations and Report Tabs created newly after the Process completion.

Step 8: There was a recommendation for the SQL Query which was executed earlier using Management Studio. it can be applied to the Table by Selecting the Action Menu and Click on Apply Recommendation which will Improve the Query Performance by 58% as shown.

Regards,

Daniel J.S BE CSE, MBA Entrepreneur,
Data Warehouse and Data Mining Architect,
+91 8939574948...