After a group of RDs started chatting about stored procedures I finally felt like I could join in. Often they talk about .Net and client stuff and well, quite honestly, I don't care what you do with the data...only that I serve it up quickly. Ok, I'm really kidding here but my primary focus is backend server tuning and availability, so when the group started talking about stored procedures, I had to chime in. It all started because someone had blogged that stored procedures are not precompiled and are therefore of no use... which is COMPLETELY wrong. However, the irony is that I don't even believe that they should be compiled (and saved and therefore reused) ALL the time. In fact, a precompiled plan is also an optimized plan and that plan may or may not always be the best plan for every execution....... So, here's a bunch of stuff about sprocs. You definitely want to use them - but use them effectively!!!!
Benefits of stored procedures:
- Centralized logic which can be changed with minimal client impact
- Logic on the server so roundtrips are minimized
- Compiled plans saved in cache. Not every plan is saved, not every plan should be saved. However, the default is that stored procedure plans are compiled and saved - on first exection. The plan is NOT created when the stored procedure is created (I've also seen this written up incorrectly before) NOR is this plan saved permanently. There are many reasons for why the procedure's plan may fall out of cache:
- Server restart
- Falls out of cache due to low re-use (and not enough cache to keep it around)
- Specifically being removed from cache by:
- Executing DBCC FREEPROCCACHE to kick ALL plans out of cache (fyi - you can see what's in cache by querying master.dbo.syscacheobects)
- DATA on which the procedure depends changing enough to cause the statistics to be invalidated and therefore sql server proactively invalidate the plan
- Executing an sp_recompile (on the object(s) on which the procedure depends. This last one is also nice and something I sometimes force (off hours when a lot of indexes are being rebuilt and/or added). You can execute sp_recompile tname and it will cause all plans which access this table to be invalidated.
Stored Procedures for Security:
- Stored Procedures can be secure AND easier to manage - in terms of permissions.
- If I am the owner of a table and I create a procedure based on my table - I can give people access to the procedure withOUT giving base table permissions. This will allow better security and minimize the accidental "oh darns" when someone forgets a where clause on a delete. Imagine a user who types in DELETE Sales WHERE invnum = 1234 BUT highlights ONLY the DELETE Sales...whose problem is that? Mine as the DBA. We can all argue that users should not have ad-hoc access to the server (and I agree) BUT stored procedures create a layer of abstraction which is ALWAYS good especially if you are using DYNAMIC SQL.
Dynamic 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.
Caching (in General) for better Performance:
There are really three areas that need to be understood to really get the issues related to stored procedures:
- Ad-hoc statement caching (that was new in SQL Server 7.0 and higher)
- Forced statement caching (through sp_executesql)
- Stored procedure caching (by creating stored procedures)
Ad-hoc Statement Caching
When 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:
-- 1) Clear Cache with
DBCC FREEPROCCACHE
-- 2) Look at what executable plans are in cache
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- 3) execute the following statement
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99
-- 4) Look again at what executable plans are in cache and you'll find that there's a
-- plan for a NUMERIC(4,2) (look at the "sql" column in output - far RIGHT)
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
-- 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:
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99
-- 6) Look again at what executable plans are in cache...
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = 'Executable Plan'
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....
Forced Statement Caching (through sp_executesql)
This is good IF you know the plans are consistent (more on this coming up) 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:
DECLARE @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
BUT 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...
Stored procedure caching (by creating stored procedures)
OK - 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)) 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):
I 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.
So - having gotten through all of this... 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:
Use "ModularProcedures.SQL (4.28 KB)" to get insight into what happens even with procedures that use conditional logic - not that there's anything wrong with that?!