Tag Archives: Database Tuning Advisor

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. Continue reading