Are stored procedures better than direct queries?

This blog is to address an age old debate: Are stored procedures better than direct queries.

Sure from a manageability and granular security control standpoint stored procedures offer much more capabilities but the real question is if direct queries (Ad hoc queries) can scale as well as a stored procedures.

Most of the forum discussions and blog entries end in a stalemate - SQL Server is now optimized for ad hoc queries as well it creates a plan cache for the query and parameterizes the query so that i can be used again when a similar query is executed (i.e. both queries should be exactly the same even an additional whitespace or change in case will lead to the query plan not being reused). However there is proof that stored procedures consume less memory and hence improve overall scalability of the application.

To see this in action I have created a simple scenario with an Employee table having columns empId and EmpName. This file contains the scripts that I have used for creating the schema and inserting 10 rows of test data. The script also contains 10 direct SELECT statements and 10 calls to a stored procedure which contains a simple SELECT statement. Clear the procedure cache before executing the script. Once the script is executed check the DMV sys.dm_exec_cached_plans using the following script

select text,refcounts, usecounts, cacheobjtype, objtype, size_in_bytes, memory_object_address from sys.dm_exec_cached_plans qs cross apply sys.dm_exec_sql_text(plan_handle) ORDER BY UseCounts DESC

The resultset for the above script is:

Now whats interesting in the resultset is that the stored procedure plan was compiled and reused 10 time but in case of the ad hoc queries the plan was compiled and is being reused (ie 10 times) however SQL Server is also tracking the queries issued separately and memory is being consumed for these individual queries separately, and as the column memory_object_address indicate memory is being allocated separately for different queries it not reusing the same memory. Another way to see this behavior is to view the bpool_committed column in the sys.dm_os_sys_info DMV, note that SQL Server will under memory pressure throw things out of the buffer when ever required so using direct queries are not totally evil but as indicated above do not scale as well as stored procedures.

Hence Recommendation: Go with stored procedures :)