I'm having problems with slow performance in a sql select statement with some parameters, for the same query, executing this select using <code>sp_executesql</code> way it takes double time that the inline-way. The problem is that in sp_execute-way sql server is not using optimal execution plan. Although plans are different, it seems that in both cases indexes of tables are being used correctly. I really don't understand why performance are so different. My original query is more complex but to try to figure out what's happening I have simplify the original query to a select with 3 tables and 2 joins. The main difference is the use of Hash Match in optimal , I really don't know the meaning of this but is the only difference I can see. Optimal plan (hash match, over 3 seconds) <img src="https://i.stack.imgur.com/8Ty0C.png" alt="enter image description here"> Wrong plan (no hash match, same indexes than above, over 12 seconds) <img src="https://i.stack.imgur.com/pOUMb.png" alt="enter image description here"> <ol> <li>I think my problem is not "parameter sniffing", in my case the query is always slow for all distinct parameter values because the execution plan is always incorrect.</li> <li><code>OPTION (RECOMPILE)</code> doesn't help, <code>sp_executesql</code> keeps going slow and inline-way take more time (because the query always compile the execution plan)</li> <li>Statistics for tables are updated</li> <li>I have to use <code>sp_executesql</code> way because it seems that reporting services encapsulates the select in <code>sp_executesql</code> calls</li> </ol> Does anybody know why <code>sp_executesql</code> generates a different (wrong) execution plan than the inline query? <hr> EDIT: Queries wasn't using same indexes I guess that because the execution tree is not the same and sqlserver takes indexes as it pleases, attached you can find new execution plans to force to use the same indexes, performance is now even worst, from 12 seconds to more than 15 minutes (I have cancelled) in slow query. I'm really not interested in run this specific query more speed, as I say this is not the real query I'm dealing with, what I'm trying to figure out is why execution plans are so different between inline-query and <code>sp_executesql</code>-query. Is there any magic option in <code>sp_executesql</code> that do this works properly? :) Optimal <img src="https://i.stack.imgur.com/EOPjt.png" alt="enter image description here"> Slow <img src="https://i.stack.imgur.com/PBDKA.png" alt="enter image description here">

My understanding is that sp_executesql keeps a cached plan after the first execution. Subsequent queries maybe using a bad cached plan. You can use the following command to clear out the ENTIRE SQL Server procedure cache. <pre class="prettyprint"><code> DBCC FREEPROCCACHE </code></pre> http://msdn.microsoft.com/en-us/library/ms174283.aspx

Non-optimal execution plan using sp

I'm having problems with slow performance in a sql select statement with some parameters, for the same query, executing this select using sp_executesql way it takes double time that the inline-way.

The problem is that in sp_execute-way sql server is not using optimal execution plan. Although plans are different, it seems that in both cases indexes of tables are being used correctly. I really don't understand why performance are so different.

My original query is more complex but to try to figure out what's happening I have simplify the original query to a select with 3 tables and 2 joins. The main difference is the use of Hash Match in optimal , I really don't know the meaning of this but is the only difference I can see.

Optimal plan (hash match, over 3 seconds)

enter image description here

Wrong plan (no hash match, same indexes than above, over 12 seconds)

enter image description here

I think my problem is not "parameter sniffing", in my case the query is always slow for all distinct parameter values because the execution plan is always incorrect.
OPTION (RECOMPILE) doesn't help, sp_executesql keeps going slow and inline-way take more time (because the query always compile the execution plan)
Statistics for tables are updated
I have to use sp_executesql way because it seems that reporting services encapsulates the select in sp_executesql calls

Does anybody know why sp_executesql generates a different (wrong) execution plan than the inline query?

EDIT: Queries wasn't using same indexes I guess that because the execution tree is not the same and sqlserver takes indexes as it pleases, attached you can find new execution plans to force to use the same indexes, performance is now even worst, from 12 seconds to more than 15 minutes (I have cancelled) in slow query. I'm really not interested in run this specific query more speed, as I say this is not the real query I'm dealing with, what I'm trying to figure out is why execution plans are so different between inline-query and sp_executesql-query.

Is there any magic option in sp_executesql that do this works properly? :)

Optimal enter image description here

Slow enter image description here

What is exec sp_ executesql in SQL?

January 9, 2020 by Esat Erkec. The sp_executesql is a built-in stored procedure in SQL Server that enables to execute of the dynamically constructed SQL statements or batches. Executing the dynamically constructed SQL batches is a technique used to overcome different issues in SQL programming sometimes.

What is the difference between execute and Sp_executesql?

sp_executesql supports parameterisation, whereas EXEC only accepts a string. Only performance differences that may arise are due to the parameterisation i.e. a parameterised sp_executesql call is more likely to have a reusable cached plan.

Can we pass dynamic parameters in SQL query?

Dynamic SQL queries are those built at runtime based on one or more variable values. To execute those queries, we must concatenate them into one SQL statement and pass them as a parameter to the sp_executesql stored procedure.

Is Sp_executesql faster?

This requires an immediate caveat. You should absolutely be using sp_executesql over any type of non-parameterized execution of T-SQL.

My understanding is that sp_executesql keeps a cached plan after the first execution. Subsequent queries maybe using a bad cached plan. You can use the following command to clear out the ENTIRE SQL Server procedure cache.

    DBCC FREEPROCCACHE

http://msdn.microsoft.com/en-us/library/ms174283.aspx

Non-optimal execution plan using sp_executesql

Tags:

sql-server

reporting-services

sp-executesql

Miguel A. Luna

People also ask

1 Answers

Ahz

Recent Activity

Donate For Us