sql server - Index seek used in query but index scan in stored procedure -
sql server - Index seek used in query but index scan in stored procedure -
i'm experiencing unusual behaviour in sql server 2008 r2.
i have next query:
update tabletoupdate set columntoupdate = @columnvalue columna in ( select columna subq1table columntoupdatereference = @columnvalue ) , columnb in ( select columnb subq2table columntoupdatereference = @columnvalue )
when run query manually , declare variable @columnvalue follows:
declare @columnvalue int = 123;
it runs in seconds , index seek on index covering columns columna, columnb , columntoupdate on tabletoupdate table.
when create stored procedure using exact query except parameter passed in.. eg:
exec sp_query 123
an index scan used on same index , takes around 30 seconds complete.
i've looked @ both query plans different. reading left right first difference stored procedure seems nested loops (inner join) whereas direct query stream aggregate (aggregate).
why calling sql through stored-procedure create difference? if need me provide more info allow me know.
thanks in advance,
tom.
like garethd suggested, sound may parameter sniffing, in case don't know is, allow me (try to) explain.
sql server compiles stored procedures "sniffing" parameters sent first time procedure executed , plan execution set plan cache future reference.
every subsequent time procedure executed, sql server retrieves execution plan cache , uses (unless there reason recompilation).
a problem can arise if first time stored procedure executed set of parameters used generates acceptable plan set of parameters bad other more mutual sets of parameters.
there few ways around can think of:
there workarounds overcome problem.
option (recompile) alternative (optimize (@variable=value)) alternative (optimize (@variable unknown)) utilize local variables
it depends on setup best you, have read around.
sql server - parameter sniffing
parameter sniffing (or spoofing) in sql server
https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/
also on side note watch out utilize of 'sp_' when naming stored procedures - it's big no no. check out this article, details thorough exam of using sp_.
sql-server tsql sql-server-2008-r2 query-plans
Comments
Post a Comment