How can I optimize this query?

Nagendran 2014-09-15 09:42:01

Hello,

Since I am new to SQL server, Kindly help me to optimize this query?

SQLkiwi 2014-09-15 15:45:38
From a simple indexing point of view, it is likely worth extending the existing nonclustered index [mistoolv3_sales_Combo_Rights_ecode_IDx] to include type and str_name in the index keys. The following T-SQL command can be used to replace the existing index:

CREATE NONCLUSTERED INDEX
    mistoolv3_sales_Combo_Rights_ecode_IDx
ON dbo.MISToolv3_Sales_Combo_Rights
    (ecode, type, str_name)
WITH (DROP_EXISTING = ON);

This change should eliminate the Key Lookups from the query plan.

You might like to also try adding OPTION (RECOMPILE) to the query. This change will allow the query optimizer to compile a plan that is optimal for the current parameters, avoiding parameter-sensitivity issues, and enabling plan simplifications based on, for example, the known value of @type. Note that this change will only affect post-execution query plans, not an estimated plan.

For an in-depth discussion of the various options for this sort of 'dynamic search condition' query, please take the time to read Erland Sommarskog's article.

You should also consider applying Service Pack 2 for SQL Server 2012.

Nagendran 2014-09-16 07:09:04
Thanks for your solution. However it has omitted keylookup in execution plan. Still has performance degradation.
SQLkiwi 2014-09-16 14:04:07
Did you also try OPTION (RECOMPILE)? What results did you get? Can you provide the new plan? How fast do you need it to be? Note: this site is for questions about Plan Explorer and query plan issues, it is not a free consultancy service 🙂