SQL server manages memory quite unlike other applications.  This has been a cause of many heartburns, especially when it comes to explaining to customers and test teams as to why SQL server consumes so much memory or explaining why the “memory leak” that is observed is not really a leak.

One key factor that contributes to this perceived memory leak is SQL server’s aggressive caching policy. It primarily caches the data, and the SQL execution plans and dig this “never gives up the memory unless and until it feels system is starved of memory”.  However this behavior is not all that great when it comes to less than perfect applications.  There seems to be a flaw (atleast wrt SQL server 2000) in the way it releases memory in that, the operating system seems to become aware of a memory starvation quite before SQL server realizes this fact and expands its paging system to increase the amount of “total memory” available in the system.

So it is in the best interest of the application to not eat up un-necessary cache unless it is required. This post covers the plan cache and how its growth can be curtailed without sacrificing any performance. In fact you gain more performance by better cache utilization in terms of cpu and memory. Read ahead to find out how this can be done.

Facts of life

  • SQL server will create execution plans for all queries and stored procedures it executes. Query plans are detailed steps as to how to get the results for the query specified. So this would include which tables to query, which indexes to use, how to join or merge the results, and so on. Quite understandably all this magic does not come free.
  • Creating query plans consumes CPU, as the server needs to know what is asked of it, and quite probably run it through tons of algorithms and data structure to create a reusable generic plan, which is going to fetch the results.
  • This means that, if SQL server were able to reuse the plans it creates, quite a bit of savings in terms of CPU consumption could be had whenever it runs the same queries again and again. Therefore, it follows that

SQL SERVER TRIES TO CACHE ITS QUERY PLANS

What does this imply for applications and administrator?

  1. Cache means store. So if you are going to store the plans (in memory of-course) then that means that your memory requirements are going to go up. However if that saves CPU consumption that is good.
  2. However you have a problem, if the store grows uncontrollably or if the savings in CPU does not measure up to your memory costs.
  3. You can measure what amount of memory is used by the procedural cache from Perfmon. SQLInstance – Buffer Manager – Procedure Cache Pages (8 Kb pages). Do check out the other counters in the Buffer Manager object to get a relative measure of how much memory the ProcedureCache occupies wrt other pages
  4. Most installations face this problem. i.e. the memory that SQL server consumes for the query plan goes up and unless you know exactly what you are looking for, its hard to keep this under control, much less account for what eats up this memory. Therefore,

IT’S IMPERATIVE THAT ONE UNDERSTANDS THE QUERY PLAN CACHE

How does query caching work?

  • Exec “Proc_Get_Orders”
    1. Plan for the stored procedure “Proc_Get_Orders” is created once and used multiple times.
  • sql_string = “select * from orders where customerid=” + customerid
    Execute(sql_string)

    1. Plan gets created for the string ‘select * from orders where customerid=1’
    2. Plan gets created for the string ‘select * from orders where customerid=2’
    3. ….ad -infinitum

    SQL server creates n plans for n ad-hoc queries. THIS is what bites you. THIS is what contributes towards bulging memory pages for the query plan cache. To control this growth either switch to stored procedures or switch to prepared statements with parameter place holders, which has the added benefit of preventing SQL injection attacks.

  • Prepared statementssql_string = ‘select * from orders where customerid=?’AdoCommandObject.CreateParameter(customerid)
    AdoCommandObject.AddParameter()
    AdoCommandObject.Execute(sql_string)

    Plan gets created for the string ‘select * from orders where customerid=?’Each time this query is executed using parameters, the cache plan gets reused, with the customer id as the varying input.

How does one see what is inside the plan cache ?

  • Or in other words how can one verify that these recommendation works?
  • Use these commands to display the contents in the plan cache (syscacheobjects)
  • The great thing about this is that you can actually see the the exact text of the plan which is in the cache – talk about accountability !!!
  • query_cache_plan
  • The key in understanding the output is the usecounts. Good applications will have a high ratio for the reused plans.
  • Note : Its easy to see from this output how much memory is wasted due to a particular ad-hoc statement. You could calculated the amount of savings by converting a particular module to use prepared statements by adding up the memory usage counts.
  • Use the MSDN explanation of syscacheobjects to further understand what the different fields signify and to find out other issues that may be plaguing your application.

Once you are done optimizing away all your sql statements, the memory growth or leak if you would prefer, from procedural cache would be well under control for your application. Happy optimization !!! 🙂

For further detailed reference –

Technet Article on MSDN

Minding Memory, from Microsoft’s customer Advisory Team

Advertisements