Basically yes – parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where … Read more
It is good but can be bad sometimes. Parameter sniffing is about the query optimizer using the value of the provided parameter to figure out the best query plan possible. One of many choices and one that is pretty easy to understand is if the entire table should be scanned to get the values or … Read more
tl;dr update statistics We had a delete query with one parameter (the primary key) that took ~7 seconds to complete when called through EF and sp_executesql. Running the query manually, with the parameter embedded in the first argument to sp_executesql made the query run quickly (~0.2 seconds). Adding option (recompile) also worked. Of course, those … Read more