Microsoft SQL Server has an effective pool of shared memory which ideally is used for two purposes .
- Data buffer;
- Execution plan cache.
It is commonly known that a query executed second time always gives response faster than the first time. In this post I’m going to discuss what works behind this and how can we make sure that the shared memory is effectively used.
What works behind this?
The shared memory part which is commonly known as execution plan cache (also known as procedure cache) save the execution plans of all the queries executed in SQL Server. When a query is executed this area of memory is looked into to determine if any of the existing execution plan can be re-used for this query. If the plan is not found then the query is parsed, compiled, optimized for execution and then the execution plan of the same is stored in the execution plan cache. That is the reason of queries working faster in subsequent time than the first time.
How to make effective use of execution plans
For each query executed in SQL Server an execution plan is saved but its not necessary that the execution plan stored is useful enough that its used for other queries also. To better understand this lets have an look how SQL Server saves the execution plan.
SELECT UseCounts, CacheObjType, ObjType, ST.Text FROM sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) as ST
ORDER BY ST.text
In the above screenshot let’s concentrate on the area which is in red. These three execution plans are the result of non-parameterized ad-hoc queries executed from the code of application inside an iteration. In this case none of the execution plans are effective, for each query a new execution plan is required. The query in the code was written something like below.
string sql = “SELECT * FROM URls where urlid=” + a;
where “a” is the varying value of the iteration
Had this query been a parametrized query only one execution plan would have been created and used for all the iteration. Let’s take an example to understand this better. Now my query would be something like.
string sql = “SELECT * FROM URls where urlid=@a”;
SqlCommand cmd = new SqlCommand(sql, myConn);
cmd.Parameters.Add(new SqlParameter(“@a”, a));
Now let’s have a look at the execution plans
In the above screenshot you would realize that only one execution plan has been created and its “usecounts” is 3, that means this plan has been used three time. Reusable execution plans means lesser CPU utilization , lesser utilization of memory for execution plan cache there by leaving more memory available for data buffer.
Conclusion
Though SQL Server maintains the execution plan of each query but making effective use of it is some what in our hands. There should be no piece of code in the application which is making use of ad-hoc queries it should always be parameterized queries. Though I personally prefer to use stored procedure since allowing direct access to the tables can be a security risk.
