Basic How To – Using SQL Server Database Engine Tuning Advisor

The function of SQL Server Database Engine Tuning Advisor in MS Web is “examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning”
Refer To: https://msdn.microsoft.com/en-us/library/ms166575.aspx
Query needs to be tuned in order to increase performance.

This post will showing how to basic use Database Engine Tuning Advisor. I use SQL Server 2008 R2 AdventureWorks sample Database.

  1. First create your prefered query. For example
    select row_number() over(order by Sales.Store.Name) as rowno, Sales.Store.Name as StoreName, Sales.SalesTerritory.Name as TerritoryName, sum(Sales.SalesOrderHeader.TotalDue) as SalesValue 
    from Sales.Customer
    inner join Sales.Store on Sales.Customer.StoreID = Sales.Store.BusinessEntityID
    inner join Sales.SalesTerritory on Sales.SalesTerritory.TerritoryID = Sales.Customer.TerritoryID
    inner join Sales.SalesOrderHeader on Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
     
    where Sales.Customer.StoreID is not null
    group by Sales.Store.Name,Sales.SalesTerritory.Name order by Sales.Store.Name
    

    Save To your folder and name it StoreSales.sql

  2. Open Database Engine Tuning Advisor from MS SQL Server 2008 R2 Start Menu, Performance Tools, Database Engine Tuning Advisor.
  3. You can Edit Session Name to preferred one. Choose previous Query file at Workload section also choose Database for workload analysis
  4. Select tables on Datbase GridView by clicking its listbox and choose apropriate table(s)
  5. You can go to Tuning Options Tab to see tuning options but for start just leave as default
  6. Click Start Analysis button and let tuning advisor is working for a while
  7. The result is recommendations for creating indexes and statistics. You can go to Action menu, and Apply Recommendations to create all indexes & stats
  8. Try again your query. It should be faster than before. Finish

Regards,
Agung Gugiaji

Advertisements

One response to “Basic How To – Using SQL Server Database Engine Tuning Advisor

  1. Pingback: Get Top Queries By Average CPU Time SQL Server 2008 R2 | Enlighten Application Developer Journals

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s