\n
\nDynamic sql has many connotations… There is “Dynamic String Execution” which can be WITHIN a stored procedure and there’s Dynamic SQL which is what an application builds and sends off to the server. As for a client who sends an ad-hoc string to the server or for a dynamically built string within a stored procedure – BOTH REQUIRE that the user have the ability to execute the command directly – which means there’s more room for error. However, if it doesn’t need to be in a dynamic string (and many statements can be parameterized) then it could be in a stored procedure WITHOUT dynamic string exection withOUT the possible room for HUGE SQL injection problems\/errors. Speaking of SQL Injection – IF you use stored procedures and mostly only allow for Identifiers as parameters (i.e. tablename, viewname column names, etc.) then you can use the QUOTENAME() function to significantly reduce SQL injection.<\/FONT><\/DIV><\/BLOCKQUOTE><\/BLOCKQUOTE>
\nCaching (in General) for better Performance:<\/FONT><\/DIV>
\n<\/FONT> <\/DIV>
\nThere are really three areas that need to be understood to really get the issues related to stored procedures:<\/FONT><\/DIV>
\n
\n- Ad-hoc statement caching (that was new in SQL Server 7.0 and higher)<\/FONT>
\n- Forced statement caching (through sp_executesql)<\/FONT>
\n- Stored procedure caching (by creating stored procedures)<\/FONT><\/LI><\/UL>
\nAd-hoc Statement Caching<\/STRONG><\/FONT><\/DIV>
\nWhen a statement is deemed “safe” sql server will take EVEN ad-hoc statements and store a plan of execution in cache for subsequent users to use. For the plan to be re-used the statement has to be almost an identical match, the parameter has to be the EXACT same data type, the objects cannot be ambiguous and MOST statements won’t really benefit from this. If you want to see an example of ad-hoc statement caching do the following:<\/FONT><\/DIV>
\n
\n— 1) Clear Cache with
<\/FONT><\/DIV>
\nDBCC FREEPROCCACHE<\/FONT><\/DIV>
\n
— 2) Look at what executable plans are in cache
<\/DIV>
\nSELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ‘Executable Plan’<\/FONT><\/DIV>
\n
— 3) execute the following statement
<\/DIV>
\nSELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99<\/FONT><\/DIV>
\n
— 4) Look again at what executable plans are in cache and you’ll find that there’s a <\/DIV>
\n— plan for a NUMERIC(4,2) (look at the “sql” column in output – far RIGHT)
\nSELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ‘Executable Plan’<\/FONT><\/DIV>
\n<\/DIV><\/FONT><\/DIV>
\n— 5) If you execute the EXACT same statement with a 4,2 then you will get THAT plan
— but if you execute with a 5,2 you’ll get a new plan (the plan is not safe). Execute this:<\/DIV>
\n
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99<\/FONT><\/DIV>
\n
— 6) Look again at what executable plans are in cache… <\/DIV>
\n
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ‘Executable Plan’<\/FONT><\/DIV><\/BLOCKQUOTE>
\n<\/FONT>OK – so the fact that SQL Server can cache the plan is good… How often is something actually deemed safe – well, it’s not that likely. A better way of getting better plan re-use IS either of the two following BUT in these two cases it’s ALWAYS cached – which might not ALWAYS be good…. <\/FONT><\/DIV><\/FONT>
\n<\/FONT> <\/DIV>
\nForced Statement Caching (through sp_executesql)<\/STRONG><\/FONT><\/DIV>
\nThis is good IF you know the plans are consistent (more on this coming up<\/EM>) and IF you want to type the data more strictly. Remember how SQL Server had to type the data above (numeric(4,2) or numeric(5,2), etc.). Well here you can type the parameters and force the plan to be cached. All subsequent uses will get the same plan:<\/FONT><\/DIV>
\n
\nDECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N’SELECT t.* FROM dbo.titles AS t WHERE t.price = @price’
EXEC sp_executesql @ExecStr, N’@price money’, 19.99<\/FONT><\/DIV><\/BLOCKQUOTE>
\nBUT this falls into the category of being hard to know and there are better ways with stored procedures. Generally, I recommend stored procedures over statement caching…<\/FONT><\/DIV>
\n<\/FONT> <\/DIV>
\nStored procedure caching (by creating stored procedures)<\/STRONG><\/FONT><\/DIV>
\nOK – I could go on for hours here and I’ll make it sufficient to say that NOT all procedures should be cached but MANY should. The ONLY way to get the security and consistency desired from stored procedures is to know when to save them and when not to. It also requires that you typically have more smaller stored procedures rather than fewer larger procedures (i.e. modular code is a KEY component to better performance). Anyway, having said this… IF the first person who executes the procedure (and there isn’t a plan for the procedure already in cache (and just to make this even more clear – stored procedure plans – when saved – are ONLY in cache they are NEVER saved to disk<\/EM>)) then a plan will be generated – and used for ALL subsequent users (unless a recompile is forced or occurs due to issues in third bullet in first section :). So now that there’s a plan – will that plan be perfect…not necessarily. I have a great script that shows this and it’s hard to explain over a short blog but the key point is this: (I took this small section from yet another of my emails so forgive the duplication<\/EM>): <\/FONT><\/DIV>
\n
\nI would say that forced statement caching and stored procedure caching have the same problem(s) and that’s that NOT all statements should be cached. There are certain strategies that can help to minimize costly overhead and there are certainly tips\/tricks that we use to see if a plan should be saved or not. I think the number one thing I’d recommend is more modular procedures where the data returned SIGNIFICANTLY varies. The key reason is that smaller procedures tend to be more controllable. And – when a complex procedure is separately out then you can set that smaller more modular proc to be recompiled on each execution. That’s probably the easiest way. There are certainly other things you can do to more effectively determine the cause of the problem (and that would be better) but I’d say that forcing recompilation on a smaller piece of code that generates widely varying sizes\/sets of data is a good and generic solution.<\/FONT><\/FONT><\/FONT><\/DIV><\/BLOCKQUOTE>
\nSo – having gotten through all of this… <\/FONT>If you really want to play with this A LOT more below are a couple of scripts that I think are VERY useful. I use these in my lectures on Optimizing Stored Procedure performance. Regardless, this is the correct order to read\/review\/learn these scripts:<\/FONT><\/DIV>
\n