I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a little Googling, I followed the suggestion in this answer and it solved my problem.
I don't like fixing things without learning how they work, so I decided to read up on "parameter sniffing". My understanding is that SQL Server occasionally builds and caches an execution plan for the stored proc, and optimizes that plan based on whatever parameter values it happens to see at the time. So if you pass in different parameters later, you might get bad performance.
But in my case, I ran the stored proc with exactly the same parameter values from Management Studio and C#. Management Studio was fast, C# was slow. I would expect both to use the cached plan, so how could it be fast in one place and slow in another?
I unfortunately can't paste the code, but I can provide any additional details necessary. Just trying to understand this!