Parameter sniffing

Question

Parameter sniffing

Viewing 12 posts - 46 through 56 (of 56 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Very interesting question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Umer Akram

SSC-Insane

Points: 22209

October 18, 2013 at 5:28 am

#1659655

very nice question

SqlMel SSCrazy Points: 2901 More actions · Answer 3

Great question, learned something today.

Got it right but didn't know that by recompiling the table only the statement referencing that table inside the SP would be recompiled!!

I recently watched one of SQLSentry's videos on query tuning in which they stated that if a table was recompiled all "objects" that depend on a table would be recompiled so I figured the whole SP would recompile. My thought was that the entire execution plan would be thrown away and recreated.

Thanks!

---------------
Mel. 😎

Thomas Abraham SSChampion Points: 10761 More actions · Answer 4

Posting a bit late to the party, as Thursday was a hectic day. Thanks for this very interesting question. I figured there was a command option to force the defaults to be applied to existing records. Thanks to your question, I know what it is. Questions like this help me learn stuff ahead of when I need them, and that's what keeps me coming back to QOTD.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Two-pointer... Got it right only because I ran into this about three weeks ago.

Thanks, Mikael!

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

Christian Buettner-167247

SSChampion

Points: 13729

October 21, 2013 at 5:28 am

#1660092

Thank you!

Best Regards,

Chris Büttner

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

Indeed a great one!!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Good Question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

manik_anu SSCrazy Points: 2377 More actions · Answer 9

I cannt get that result...

Manik
You cannot get to the top by sitting on your bottom.

jfgoude SSCrazy Points: 2586 More actions · Answer 10

end of sequence for good answers !

really nice question thanks

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 11

Getting this error:

Msg 208, Level 16, State 1, Procedure GetIt, Line 7

Invalid object name 'T'.

Msg 15165, Level 16, State 1, Procedure sp_recompile, Line 19

Could not find object 'T' or you do not have permission.

Msg 208, Level 16, State 1, Procedure GetIt, Line 7

Invalid object name 'T'.

Thanks.

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 12

SQL-DBA-01 (12/26/2014)
Getting this error:
Msg 208, Level 16, State 1, Procedure GetIt, Line 7
Invalid object name 'T'.
Msg 15165, Level 16, State 1, Procedure sp_recompile, Line 19
Could not find object 'T' or you do not have permission.
Msg 208, Level 16, State 1, Procedure GetIt, Line 7
Invalid object name 'T'.

If you blindly copy, paste, and run the code - then yes, you will get that error. The code references a table named "T" that you probably didn't create.

But for answering this QotD, there is no need to run the code. Just reading the question and the code should suffice.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/