0

Any idea how i debug the fact that direct SQL run fine but when wrapped as a stored procedure, endless runtime. I checked the SSMS activity monitor - does not show blocked or suspended...

FH-Inway
  • 4,432
  • 1
  • 20
  • 37
147
  • 582
  • 8
  • 20
  • 5
    Go look up parameter sniffing, it's almost always this. – DavidG Nov 08 '16 at 11:40
  • 1
    This should be one of the only things you ever need to read on the topic: http://www.sommarskog.se/query-plan-mysteries.html – alroc Nov 08 '16 at 11:42
  • 1
    does the stored proc has a name that starts with sp_ ? I read somewhere that can be a problem https://msdn.microsoft.com/en-us/library/dd172115(v=vs.100).aspx – GuidoG Nov 08 '16 at 11:44
  • DavidG - hmm - lemme check @alroc - awesome reference GuidoG - it does coz i name 'em that way - dunno why that should affect performance – 147 Nov 08 '16 at 11:45
  • Ah yes, *never* name your procedures "sp_...", that prefix implies the procedure is "**sp**ecial", not that it's a "**s**tored **p**rocedure" – DavidG Nov 08 '16 at 11:46
  • 1
    I added a link in my prior comment about this – GuidoG Nov 08 '16 at 11:46
  • @GuidoG - Crikey - such weird behavior - I shall change the name post haste! – 147 Nov 08 '16 at 11:49
  • 2
    It can affect performance, look here https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix – GuidoG Nov 08 '16 at 11:49
  • @DavidG - Parameter sniffing is the problem -- Would you please add this as an answer so I can mark it solved? – 147 Nov 08 '16 at 12:00
  • @GuidoG - good link - I'll change the names of all my procedures - naively had I assumed `sp_` stood for `stored procedure`... – 147 Nov 08 '16 at 12:02
  • You can name them sp without the underscore if you want, that is not a problem – GuidoG Nov 08 '16 at 12:04

0 Answers0