In my previous article, An Introduction to Database Engine Tuning Advisor (DETA) in SQL Server 2012, I’ve shared a detailed description of DETA. Here we’ll see DETA in action with an example.
P.S. This article will be applicable to SQL Server 2014 as well. DETA is not available in any of the Express editions of SQL Server.
Before we proceed let us get ready with the required tables and data.
1. Create a database named AnalysisTest.
2. Run the following script. It will create two tables Product and ProductReview. Apart from creating the table the script will populate Product table with 1 million dummy records. This script may take 7 to 10 minutes for complete execution!
Use AnalysisTest
-- Drop Product table if it already exists
If (OBJECT_ID(N'Product', N'U') <> 0)
Drop Table [Product]
— Creating the Product table
Create Table Product
([ID] int NOT NULL IDENTITY(1,1)
,[ShortCode] varchar(10) NOT NULL
,[Description] varchar(50) NOT NULL
,[Price] money NOT NULL DEFAULT(0)
,[Quantity] decimal(18,2) NOT NULL DEFAULT(0)
,[DateCreated] datetime NOT NULL DEFAULT(GETDATE())
,[IsActive] bit NOT NULL DEFAULT(1)
,[LastUpdated] datetime NULL
)
— Dropping ProductReview table if it already exists
If(OBJECT_ID(N’ProductReview’, N’U’) <> 0)
Drop Table ProductReview
— Creating the ProductReview table
Create Table ProductReview
([ID] int NOT NULL IDENTITY(1,1)
,[ProductID] int NOT NULL
,[ReviewerName] varchar(100) NOT NULL DEFAULT(”)
,[ReviewerEmail] varchar(100) NULL
,[ReviewDate] datetime NOT NULL DEFAULT(GETDATE()))
— Inserting dummy data (1 million records) into Product and ProductReview Table
Declare @ProductCount bigint
Declare @Loop bigint
Declare @ShortCode varchar(10)
Declare @Description varchar(50)
Declare @Price money
Declare @Quantity decimal(18,2)
Declare @DateCreated datetime
Declare @IsActive bit
Declare @ReviewCount int
Declare @ReviewNumber int
Declare @ProductID int
Set @ProductCount = 1000000
Set @Loop = 1
Set @Price = 100
Set @Quantity = 10
While(@Loop <= @ProductCount) Begin Set @ReviewCount = 0 Set @ShortCode = ‘P’ + REPLICATE(‘0’,9 – LEN(@Loop)) + CAST(@Loop As varchar) Set @Description = ‘Description for Product ‘ + CAST(@Loop As varchar) If(@Price > 1000) — We are limiting the price range between 10 and 1000
Set @Price = 0
Set @Price = @Price + 10
If(@Quantity <= 10) Set @Quantity += 10 Else If(@Quantity > 100)
Set @Quantity -= 10
If(@Loop % 10 = 0)
Set @IsActive = 0
Else
Set @IsActive = 1
Set @ReviewNumber = @Price % 10
Insert Into Product
([ShortCode]
,[Description]
,[Price]
,[Quantity]
,[IsActive])
Values(@ShortCode
,@Description
,@Price
,@Quantity
,@IsActive)
Set @ProductID = IDENT_CURRENT(‘Product’)
While(@ReviewCount < @ReviewNumber)
Begin
Insert Into ProductReview
([ProductID]
,[ReviewerName]
,[ReviewerEmail])
Select @ProductID
,’Reviewer ‘ + CAST(@ReviewCount As varchar)
,’Reviewer’ + CAST(@ReviewCount As varchar) + ‘@mail.com’
Set @ReviewCount += 1
End
Set @Loop += 1
End
3. Open SSMS (SQL Server Management Studio). Copy paste the following query.
-- Clearing the SQL Server cache so that previously created execution plans does not affect our analysis query
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
— Selecting data from Product Table
Declare @FirstDate datetime
Declare @StartDate datetime
Declare @EndDate datetime
— We are taking a time value that is between 10 seconds and 300 seconds while creating the dummy table data
Select @FirstDate = MIN(DateCreated) From Product
Set @StartDate = DATEADD(second, 10, @FirstDate)
Set @EndDate = DATEADD(second, 300, @FirstDate)
Select
Product.[ID] As ProductID
,[ShortCode]
,[Description]
,[Price]
,[Quantity]
,ProductReview.ReviewDate
From Product
LEFT JOIN ProductReview
ON Product.ID = ProductReview.ProductID
Where Price Between 200 And 900
And Product.Description NOT LIKE ‘%0003%’
And DateCreated Between @StartDate and @EndDate
4. Select all the contents in the query window and right-click on it. From the context menu open a DETA session as explained in the following image.
Open option from SSMS Query context menu
5. Once it is loaded in a new session; start the analysis session by clicking on “Start Analysis” toolbar button as shown in the following image.
6. Now let us see the execution and result of the analysis. Check the following image for reference. The recommendations are shown in “Recommendations” tab. In our example; we can see DETA predicts 93% performance improvement if its recommendations are met and “Partition Recommendations” pane is blank since we chose “No Partitioning” in partitioning strategy (refer Pic 6); but it has recommended creation of a few index(s) and statistics in “Index recommendations” pane.
7. Various reports are displayed in “Reports” tab.
In the above image; we can see the “Tuning summary” pane contains the summary information for the analysis session containing the database tuned, workload is shown as “Inline” (because we started the session directly from the query in SSMS), percentage of benefit that we can get if the recommendations are met, time taken for tuning, number of events tuned, number of statements tuned etc. Whereas the “Tuning Reports” section contain different useful reports generated from the analysis session. In the screen-shot we can see that the “Index usage report (recommended)” displays the usage of the proposed index(s) during analysis.
As a next course of action; we are going to create the index as recommended by DETA in our database and again analyze whether the performance of the query has increased afterwards.
As shown in the above image; we can get the index creation script by clicking on the hyperlinked “Definition” column in recommendation pane and click on “Copy to Clipboard” button so that it can be pasted in the query window.
After creation of the index; perform the same same analysis again. Here DETA will show no further scope of improvement.
Please share this article if you liked the detailed explanation.
Basant Singh
Latest posts by Basant Singh (see all)
- Go Maps Introduction for Beginners - May 20, 2015
- Go Programming – Arrays and Slices - May 11, 2015
- Golang Switch Case with Sample Code - May 1, 2015
