Parameter Sniffing: Why Does This Become An Issue? Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds).

After suspecting parameter sniffing as the culprit, I masked the input parameters, and the query executed faster.

My question is: why did this happen? 

This system has been in production for more than 5 years, and this is the first time we've seen anything like this on our stored procedures. Is this "Database wear and tear"?

We've resolved the issue, so it isn't a big deal, but I'm just curious as to why this was happening.
Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan.  This may or may not be a good thing, but it is what happens.

For instance, say you have a table of fruit (100 rows).  There are 98 rows that are Apple, and only 2 rows that contain the fruit Orange.  If you query that table for Apple, then the plan will most likely compile with a Scan.  This is a good thing, as it is optimized for that Apple query.  But then when you want to query for the Orange, that Scan is inefficient.  But it is the stored plan that is being used.

The fact of the matter is that it happens.  It happens all the time.  It's typically not an outstanding issue, but in some cases it can be a pretty big problem.  A solution to an ongoing bad parameter sniffing problem is that you can utilize the OPTIMIZE FOR query hint to force SQL Server to use certain parameter values when generating the execution plan upon initial compilation.