0

I am writing a stored procedure and when I tested the procedure in Execution Plan/Sentry Plan Explorer I have noticed that in one statement the plan reacts strangely.

Here are 2 scripts that from my point of view are the same, but the results are significantly different.

Script 1 (not real code, but a code that demonstrates the problem):

DECLARE @MyVariable INT;
SET @MyVariable = 123456789;

SELECT MT.Name, OT.Name
FROM MyTable MT
INNER JOIN OtherTable OT ON MT.ID = OT.MyTableID
WHERE OT.TypeID = @MyVariable;

Script 2:

SELECT MT.Name, OT.Name
FROM MyTable MT
INNER JOIN OtherTable OT ON MT.ID = OT.MyTableID
WHERE OT.TypeID = 123456789;

It seems to be the same, but the results I get are different:

  1. Estimated rows in Script 1: 15621 rows (wrong for sure, too many rows)
  2. Estimated rows in Script 2: 205 rows (logical result, probably true)

I think that the SQL execution plan behaves differently when a variable is used in the where clause. Why? Or what is going on here? How to improve this?

Thanks

Misha Zaslavsky
  • 8,414
  • 11
  • 70
  • 116
  • I think you have the SP the @MyVariable you are passing as parameter. Is it true? And the second is just same as your script 2. – Hamlet Hakobyan Jun 01 '14 at 19:06
  • What you are saying is weird and shouldn't be the case? Can you show the full code where you are passing this parameter? – Rahul Jun 01 '14 at 19:08
  • @Halmet Hakobyan Yes, to be sure, I made set to the variable, this is not the issue for sure – Misha Zaslavsky Jun 01 '14 at 19:08
  • @Rahul I will post it later if needed, it won't help because the code is almost the same as I wrote, the difference is in the table names... – Misha Zaslavsky Jun 01 '14 at 19:12
  • 3
    Try first query with `OPTION (RECOMPILE)` and you will see difference. – Hamlet Hakobyan Jun 01 '14 at 19:13
  • 2
    Probably also see http://dba.stackexchange.com/q/33698/5203 – GSerg Jun 01 '14 at 19:16
  • 4
    If you use an **explit, literal value**, the SQL Server query optimizer can use **statistics** to find an optimal execution plan for your query. It **cannot** do this if you provide a **variable** since it's value could be anything - it cannot rely on a given, fixed value - it has to optimize the execution plan for any possible value you could have. Also: if your estimated rows and actual rows are significantly different, most likely, your statistics are out of date and should be updated – marc_s Jun 01 '14 at 19:25
  • Also try executing the following after the stored procedure definition has been ALTERed: `exce sp_refreshmodule '';` where stands in for the name of the stored procedure. – Pieter Geerkens Jun 01 '14 at 19:26

0 Answers0