Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
2

There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of canonical answer as to what the symptoms of parameter sniffing are. So that's the question:

What are the typical symptoms of parameter sniffing that would lead to its diagnosis as a potential culprit in performance?

share|improve this question
1  
See if the articl at SQL Server Pro answers your question. Page 4 is where you'll find content related to parameter sniffing. – Bruce Feb 7 at 20:06
1  
One tell tale sign can be that a query runs terribly from the application but on copying and pasting the exact same query into SSMS and executing it performance is fine. – Martin Smith Feb 8 at 11:28

1 Answer

up vote 1 down vote

It's commonly a potential cause when a stored procedure takes wildly different times to complete with different parameters.

Or when a stored procedure is seen taking a less than brilliant approach to getting data with certain parameters.

These would indicate to me that SQL Server had based it's query plan on the first parameters it had seen, but that these parameters weren't really indicative for all the possible parameter values.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.