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

SQL Server - Optimize as an in memory database

Currently working on a migration project for Java+Oracle to Java+SQL Server as you may expect there are several documented approaches for doing this migration however the challenge in this case is that the client is using Berkeley DB (in memory DB licensed by Oracle) in their distributed architecture and Microsoft currently doesn’t have a corresponding in memory DB product. So how can we build a database centric migration strategy (i.e. no change to client code)?
Initial brainstorming helped us identify 2 possible approaches to replacing the Berkeley DB: a. Use SQLCE for data caching b. Use SQL Express for data caching (also applies to SQL Standard and Enterprise skew) In both the above choices the key to arriving at the target solution is buffer pool management, SQL Server internally tries to reduce the disk I/O by storing data pages into the buffer pool.
SQL CE seemed a good choice initially because its an inproc DB an this inherent nature could improve query performance however we concluded that it was not the right fit since:•

• SQL CE doesn’t have a native JDBC compliant driver, worst case we will have to use some .NET interop like JNBridge/ Web Services etc.
• SQL CE doesn’t expose performance counters it will be very difficult to evaluate performance and SQL CE behavior under load, we can only measure response time and throughput but would not be able to observe internal resource utilization.
• SQL CE doesn’t support very high level of concurrency (ideally meant for desktop and device usage)
• SQL CE does not offers good buffer pool management

So with SQL CE out of the way we could just focus on leveraging SQL Express and optimizing the buffer pool management. So how does the buffer pool work?
When SQL Server starts it calculates the virtual address space for the buffer based on several parameters the most important being the physical memory on the system. SQL Server only identifies the max amount it should reserve for the buffer pool it doesn’t reserve the memory on startup it only commits memory on a need basis. The DMV sys.dm_os_sys_info can be used to view the amount of buffer memory, bpool_commit_target show "target" memory limit for the buffer pool and bpool_committed displays the amount of memory currently committed to the buffer pool( both number represent the number of 8 KB pages). This is also impacted by the configuration in the min server memory and max server memory. Note that when SQL Server starts it tries to commit large number of pages to buffer pool memory this is called ramp up time, SQL 2005 Enterprise edition tries to improve the performance by allocating 8 pages at a time (i.e. 64KB). Buffer manager performs disk I/O asynchronously. Optimizing the table data types and length to fit optimally into pages, identifying the indexing strategy + index usage and optimizing the I/O subsystem would all be important in optimizing the buffer pool management.
Scenarios that are not discussed in this blog which impact the buffer pool include

• AWE (for 32 bit environment) - most organizations are going for 64bit servers so AWE itself would fade away
• NUMA
  

There is news in the air that Microsoft is planning to release an in-memory DB however we are under NDA not to disclose further information, will blog about it separately when the product plans are more concrete and when the NDA is lifted.