Tag Archives: Database Engine Tuning Advisor

Get Top Queries By Average CPU Time SQL Server 2008 R2

Database performance plays significant effect in large user application. We need to examine and identify causes that makes system slow.

One step we can do is to identify query which takes high CPU time. Use transact SQL sys.dm_exec_sql_text in order to get top CPU expensive queries.

Taken from Microsoft site, https://msdn.microsoft.com/en-us/library/ms181929(v=sql.105).aspx, the code using sys.dm_exec_sql_text is below:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Above T-SQL gets Top five query by Average CPU Time. Open your SQL Server Management Studio, choose your database and run above query.

After getting those Top queries, you can open SQL Server Database Engine Tuning Advisor to analyze them and apply the Tuning Advisor recommendation to get better performance.
You can take a look at my post about How to use Database Engine Tuning Advisor for your reference.

Regards,
Agung Gugiaji

Advertisements

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