Two ways to tune your SQL Server queries

Once you have identified the queries that are running slowly on your server, you can often improve the top 3 to 5 most slowly running ones to see a significant performance improvements. An easy way to identify your slowest running SQL Server queries is to use our baseline reports tool. Here are two ways to tune your SQL Server queries.

Use “SET STATISTICS IO ON”:

After you execute this command in your Management Studio query window, it will report the amount of reads done for every query that you execute in the same session. Please note the example below and the before/after difference in logical and physical reads for the query.

 

SELECT top 1 a.SessionId,a.TicketId,a.CompanyName,a.CreatedBy,a.CreatedDate ,a.UpdatedBy
,a.UpdatedDate,a.Data,s.Code as Status
FROM APISession a inner join Status s
on s.StatusId = a.StatusId
where a.TicketId= 'EXeEo93MSamVy2SEoka3uFbujbZalMYKCY'

–before

--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
--Table 'APISession'. Scan count 4, logical reads 238070, physical reads 0, read-ahead reads 0
--Table 'Status'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0

create nonclustered index IX_APISession_ TicketID on APISession(TicketID)

–after

--Table 'Status'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0
--Table 'APISession'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0

 

The reason that you should look at reads instead of execution time when tuning your queries is because reads are not impacted by other factors such as other users on the system or blocking locks. What it comes down to for SQL Server is to read as few pages of data from memory (logical reads) or from disk (physical reads) in order to return the data requested by the query as quickly as possible.

Use “Include Actual Execution Plan”

This can be turned on from the menu bar or by going to Query > Include Actual Execution Plan. If you are not able to actually run the query, either because it takes a long time to complete or because it changes the data, you can also use “Display Estimated Execution Plan”.

Since SQL Server 2008, when you turn on one of these options, SQL Server Management Studio will recommend an index for you if it can improve your query performance. You will see this in green on the “Execution plan” tab. Below is an example:

Once you click on “Missing Index Details…” the create statement for the index will open in a new query window.