April 23, 2013 at 8:34 am
My devs seem to use sp_execute even for the execution of SPs. Is there any downside to this dynamic model?
Greetz
Query Shepherd
April 24, 2013 at 4:42 am
As long as they are calling procedures or creating parameterized queries, not really. It might tend to bloat the cache a little depending on how the calls are generated since additional statements around the EXEC sp_executesql could be stored there. That would be my one concern once I was sure they weren't just generating ad hoc T-SQL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2013 at 9:19 am
As a side note, using sp_executesql is pretty much how every query from SMO (objects used by SSMS) is issued against the instance. The SQL Agent team coded their queries this way as well and they did a lot of things wrong initially and it did result in plan cache bloat. If you run a Trace on your instance and then navigate around the Object Explorer in SSMS and run a few SQL Agent jobs you'll see what I mean.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply