![]() ![]() NetRocks), I chose to discuss ONE set of options that can be VERY helpful to reduce parameter sniffing problems. But, for my pre-session (prior to recording. In IE2 (our Immersion Event on Performance Tuning), I spent an entire day on the plan cache and optimizing for procedural code. To be honest, this is a HUGE discussion and there are LOTS of tangents. ![]() The end result – reusing a plan is not always good and recompilation is not always bad. If that plan is not good for ALL executions then you start to have parameter sniffing problems. It’s that plan (defined by those parameters) that gets saved (in cache) and reused for subsequent executions. Why? Because SQL Server “sniffs” the parameters passed and chooses the execution plan based on those parameters. It’s predicated on the fact that you understand that recompiling a plan is NOT always a bad thing. ![]() But, that’s predicated on the fact that you understand how they work. Having said that, I do – strongly – believe that you can be the most successful using stored procedures. But, does that mean that each feature is perfect for every use, all the time? Basically, what I said is that you should never say always and never say never. You shouldn’t always use prepared statements… And, dare I say – you shouldn’t always use stored procedures? In fact, I kicked off the evening with the starting statements that SQL Server is a general purpose RDBMS. I guess I had something to do with it having chosen procedures and recompilation to kick off my part of the discussion… But, still, a fun group for sure! And, why did I choose stored procedures and recompilation?Įvery developer that works with SQL Server has to access their data in SOME way… how? Adhoc SQL, prepared statements (like sp_executesql) or stored procedures. What a great time and a great group! Always fun visiting NY but even more fun when I present to a group that really gets into the topic. NetRocks was recording as part of their Visual Studio Road Trip… ![]()
0 Comments
Leave a Reply. |