I'm hoping someone here can answer a question I seem to be having real problems finding the answer to myself. The issue concerns parameter sniffing. I have been aware of this phenomenon for a few years now and I have read a number of articles on the subject. I understand the signs, why it happens and the various options available to me to 'resolve' it, though i use the word resolve in quotes as I don't believe any of the options are a resolution, merely a work-around but that's another issue.
To explain the scenario, I am the main developer on a reasonably large web app with a SQL Server back-end (at least, it's large in my opinion, with over 100 tables and getting on for 1000 stored procedures). Every now and again, but especially in the last 6 months, a random procedure that has always worked perfectly and hasn't been touched in months, even years, will start to misbehave with the parameter sniffing issue. Typically we perform an sp_recompile to start with and if it won't stop having the issue on a regular basis (often if it crops up once, it'll crop up several times a week), we add option (recompile) to the end of the procedure and all is well again. Almost all of the procedures (but not every single one) have been procedures returning lists of data and using the common filtering format
WHERE (MY_COLUMN = @CONDITION OR @CONDITION IS NULL)
What I don't understand is the why. Why this procedure? Why now? If it's been working fine for six months, a year, two years, more even, what was the final straw? This isn't to solve any immediate problem, what I was hoping for was some ideas to use to look at what made procedure XYZ go wrong, when similar procedure ABC is working fine. I have dozens of procedures that work in the same way, so why was that procedure the one to go wrong?
Hopefully I have explained my question sufficiently well. I have already looked at a number of sites explaining what parameter sniffing is, the tell-tale signs and how to stop it from occurring. What I am interested in is more fundamental than that; I want to understand what the trigger might be that would turn a normal functional procedure that is doing the same job every day into a procedure that is frequently going wrong.
Any thoughts anyone can give would be appreciated