In this post I would like to describe step-by-step process how a query can be optimized. This is taken from real practice I faced not so long ago.
So, I’ve got a query that joined about 20 tables, had ORDER BY statement and rather complex WHERE clause that looked like:
where
(JR_Charge.DocNumber like @DocNumber)
and (JR_Charge.IsDmark = @ShowDeleted or @ShowDeleted = 2)
and (@OpenLot=2 or (@OpenLot=0 and isnull(JR_Charge.Quantity,0)>0) or (@OpenLot=1 and isnull(JR_Charge.Quantity,0)<=0))
and (@TurnaroundWaste=2 or (@TurnaroundWaste=0 and isnull(JR_Charge.TurnaroundWaste,0)=0) or (@TurnaroundWaste=1 and isnull(JR_Charge.TurnaroundWaste,0)=1))
and (@HideDeleted != ''1'' OR JR_Charge.IsDMark = 0)
Some tables used in the query contain several millions of records.
It was working rather well but we’ve got a notification that with some parameters it was tremendously slow. We’ve caught the needed set of parameters so I can start optimization process.
To monitor the results of query process first it’s needed to start MS profiler with following parameters.
What I’ve set:
- SQL:BatchCompleted – to catch that the Transact-SQL batch has completed (RPC:Completed – for stored procedures).
- All other parameters will generate only additional records in profiler trace and cannot help optimizing the query.
- Also, I’ve set a filter on the part of the text of the query, to filter only my results.
Before executing the query it’s also needed to turn on option “Include Actual Execution Plan” in SQL Management Studio.
So, at last, all preparations are finished and I can start optimization process. The first results were quite confusing – the query has returned only four records but it has taken 599 sec and 2.2 millions of reads. The development server is not so powerful as production, but this values are for sure unacceptable. So the main task was set: on development server this query must be optimized to work about 10 seconds; also it’s important to reduce amount of reads at least to four times.
The Execution plan did not provide me with any index recommendations, so I’ve paid my attention to key lookups. Key Lookup operator is a bookmark lookup on a table with a clustered index. The use of a Key Lookup operator in a query plan indicates that the query might benefit from performance tuning.
To understand what fields are not covered by the index, you must view the properties of the operation:
In my case – two columns from Output list are not included into covering index. The name of the index to be modified (or as in my case create new index) can be found near the Key Lookup operation:
So the required index must be the following:
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ Sys_StorageChargeRest]') AND name = N'IX_Sys_StorageChargeRest_ChargeID_ProducerID_WorkshopID_WarehouseEquipmentID_BookcaseNumberID_INCLUDE_WeightNet_WeightGross')
create index IX_Sys_StorageChargeRest_ChargeID_ProducerID_WorkshopID_WarehouseEquipmentID_BookcaseNumberID_INCLUDE_WeightNet_WeightGross
on dbo.Sys_StorageChargeRest
(ChargeID, ProducerID, WorkshopID, WarehouseEquipmentID, BookcaseNumberID)
INCLUDE (WeightNet, WeightGross) with fillfactor = 90
I have executed query one more time, and then it was much better: duration 49 sec, Reads 600 thousands. CPU also was down from 18 000 to 7 000. The new Execution plan looked like:
By creating only one index I have improved query duration twelve times but it was still not enough – to provide comfortable conditions for users I have some more optimization work to do. So, I have had to look for other expensive operations. And I’ve found them:
It did not have key lookup, but used Name_ru field, that was not included in the index. Let’s create covering index:
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Ref_AlloyInternalCH]') AND name = N'IX_Ref_AlloyInternalCH_ID_INCLUDE_Name_ru') BEGIN
create index IX_Ref_AlloyInternalCH_ID_INCLUDE_Name_ru
on dbo.Ref_AlloyInternalCH (ID) INCLUDE (Name_ru) with fillfactor = 90
create index IX_Ref_AlloyInternalCH_ID_INCLUDE_Name_ru on
dbo.Ref_AlloyInternalCH (ID) INCLUDE (Name_ru) with fillfactor = 90
END
The result was improved a bit: duration became 39 seconds (-10 seconds).
Next expensive operation – just do the same:
Add new index:
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Ref_ChargeMaterialName]') AND name = N'IX_Ref_ChargeMaterialName_ID_INCLUDE_Name_ru') BEGIN
create index IX_Ref_ChargeMaterialName_ID_INCLUDE_Name_ru on
dbo.Ref_ChargeMaterialName (ID) INCLUDE (Name_ru) with fillfactor = 90
END
And at last I’ve reached the required results – the query has worked for 11 seconds, reads about 387 thousands. The execution plan now was looking pretty good for me:
To sum up I would like to post the profiler trace of all my experiments:
