SQLTeam.com | Weblogs | Forums

Why when I use variables on SQL the Database Engine avoid the indexes?

I'm improving our SSIS process passing the variables through a "declare" and I found this:

Here you have the plan execution using variables
brentozar com/pastetheplan/?id=rkSxs7VPv

And here without variables
brentozar com/pastetheplan/?id=SJ8soXEww

I cant put links, so i deleted the "." from ".com". Just put it on the url

As you can see, when I use the "declare" SQL Database Engine don't use the Index. The index used is an nonclustered for the column used on the "where"

Both executions were on the same server, with the same set options and at the same time executed.


did you try updating the statistics? The first one shows reading 81m records and the second shows reading 20K