• 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 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.

• 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)

-- 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'

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

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.