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