How can I optimize this query?
Since I am new to SQL server, Kindly help me to optimize this query?
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.