SQL Server Performance Forum – Threads Archive

How to find out?????

Hi,
The database on which I am working, have 220 stored procedures.
I have to find out the procedures those have parameter sniffing problems. How I can find out???
Have any methode to find out this parameter snifffing problem.

Parameter sniffing is an issue that you should look into only after completing a thorough performance audit for the individual procedure that is performing badly. The quick-and-dirty way, if you have a stored procedure that performs badly, is to copy parameters into local variables, then change the procedure to use the local variables instead of the parameters, and see if that improves performance.

BTW, Parameter sniffing is a feature [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

Okay then, it’s a feature that can become an issue.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

Khen’s bloghttp://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx for more information on PS. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

parameter sniffing can be a problem if any of your queries inside the stored procedure has a SARG to a stored proc input parameter
and that column has widely varying data distribution look in a profiler trace a stored proc with erractic cpu times ie, it might be low cpu for a while, then high but if you are on SQL2005 do read Ken’s blog that satya mentioned (not Khen)

[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] shortcut of Ken Henderson used as Khen (as he got his own website too). Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</center>

my goof for not checking
i have not gone to his web site i want get his SQL internals book, but i am a cheapsgate
his books are frequently given away at various MS sponsored events including users group meetings
except i have never drawn, and i could not trade for it
even with something more valuable

When I asked him the PASS 2006, he said sql internals 2005 book is comming…
Any update guys on this book…
MohammedU.
Moderator
SQL-Server-Performance.com

Not yet what I have got (email) from him and I believe they concentrating on enhancing tools such as PSSDiag & SQLNexus (mixture of both), so need to watch the space. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

This dragging but is Ken’s website is working?
I tried many times with no success. MohammedU.
Moderator
SQL-Server-Performance.com

]]>