none
"CACHESTORE_SQLCP" using 11GB in Whole SQL Server memory...how to resolve from memery bottle neck ?

    Question

  • Hi all

    I cluster server running with SQL Server 2005 RTM and allocated min memory is 8GB and max memory is 24 GB.(Total memory 32GB)

    Today  SQL Server showing usage of 26GB,I seached for who are uisng memory and I found that "CACHESTORE_SQLCP" using 11GB memory.


    What is the best way to clean this....

     

    Thanks in advance


    SNIVAS
    • Moved by Alex Feng (SQL)Moderator Monday, March 07, 2011 7:54 AM move to an appropriate forum (From:Getting started with SQL Server)
    Thursday, March 03, 2011 2:11 PM

Answers

  • Either , create the SPs around the queries or run them using sp_executesql :

    How Memory is being used :
    To determine what plans are in the cache and how often they're used we can use sys.dm_os_memory_cache_counters dm view .

    SELECT  TOP 6
     LEFT([name], 20) as [name],
     LEFT([type], 20) as [type],
     [single_pages_kb] + [multi_pages_kb] AS cache_kb,
     [entries_count]
    FROM sys.dm_os_memory_cache_counters
    order by single_pages_kb + multi_pages_kb DESC

    here :

    CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.
    CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.  This includes any dynamic SQL or raw SELECT statements sent to the server.
    CACHESTORE_PHDR  These are algebrizer trees for views, constraints and defaults.  An algebrizer tree is the parsed SQL text that resolves the table and column names.

    (you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

    Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.
    That is the reason the sal statements are going in to  Plan cache.

    You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.  There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

    After this to know the querry we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries

    select  TOP 100
     objtype,
        usecounts,
     p.size_in_bytes/1024 'IN KB',
     LEFT([sql].[text], 100) as [text]
    from sys.dm_exec_cached_plans p
    outer apply sys.dm_exec_sql_text (p.plan_handle) sql
    ORDER BY usecounts DESC


    and then we can check the query plans(and size) for the one we have some doubt .


    Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache).
    We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.

    Further , the query below can give us total currrent size of buffer pool .
    select count(*) AS Buffered_Page_Count
     ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
    from sys.dm_os_buffer_descriptors

    After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

    SELECT LEFT(CASE database_id
       WHEN 32767 THEN 'ResourceDb'
       ELSE db_name(database_id)
            END, 20) AS Database_Name,
     count(*)AS Buffered_Page_Count,
     count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
    FROM sys.dm_os_buffer_descriptors
    GROUP BY db_name(database_id) ,database_id
    ORDER BY Buffered_Page_Count DESC

    And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

    SELECT TOP 25
     obj.[name],
     i.[name],
     i.[type_desc],
     count(*)AS Buffered_Page_Count ,
     count(*) * 8192 / (1024 * 1024) as Buffer_MB
        -- ,obj.name ,obj.index_id, i.[name]
    FROM sys.dm_os_buffer_descriptors AS bd
        INNER JOIN
        (
            SELECT object_name(object_id) AS name
                ,index_id ,allocation_unit_id, object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p
                    ON au.container_id = p.hobt_id
                        AND (au.type = 1 OR au.type = 3)
            UNION ALL
            SELECT object_name(object_id) AS name  
                ,index_id, allocation_unit_id, object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p
                    ON au.container_id = p.hobt_id
                        AND au.type = 2
        ) AS obj
            ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
    WHERE database_id = db_id()
    GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
    ORDER BY Buffered_Page_Count DESC

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by SNIVAS Monday, March 07, 2011 4:58 PM
    Monday, March 07, 2011 10:18 AM
  • Hi,

    The CACHESTORE_SQLCP is storing cache plans for SQL statement or batches that aren't in stored procedure, functions and triggers, which are less to be reused than stored procedure (Many of them are only used once). In your scenario, CACHESTORE_SQLCP is ued up to 11GB of physical memory, which indicates that there are a lot of ad-hoc queries running on the server. Please run the following query to have a overview of the size of the plan cache used by object type:

    SELECT 
    	objtype AS 'Cached Object Type',
    	COUNT(*) AS 'Number of Plans',
    	SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
    	AVG(usecounts) AS 'Avg Use Count'
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY 'Plan Cache Size (MB)' DESC
    

    To work around this issue, please try the following:

    • Use stored produces instead of Ad-hoc queries as many as possible.
    • Run the follow statement to flush the ad hoc plans (this will not flush store procedure plans):
      USE master;
      GO
      DBCC FREESYSTEMCACHE('SQL Plans');
      GO
      

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Alex Feng (SQL)Moderator Monday, March 07, 2011 8:52 AM remove OPTIMIZE FOR ADHOC WORKLOADS suggestion which is only in SQL Server 2008
    • Marked as answer by SNIVAS Monday, November 14, 2011 1:08 PM
    Monday, March 07, 2011 7:53 AM

All replies

  • Hi,

    The CACHESTORE_SQLCP is storing cache plans for SQL statement or batches that aren't in stored procedure, functions and triggers, which are less to be reused than stored procedure (Many of them are only used once). In your scenario, CACHESTORE_SQLCP is ued up to 11GB of physical memory, which indicates that there are a lot of ad-hoc queries running on the server. Please run the following query to have a overview of the size of the plan cache used by object type:

    SELECT 
    	objtype AS 'Cached Object Type',
    	COUNT(*) AS 'Number of Plans',
    	SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)',
    	AVG(usecounts) AS 'Avg Use Count'
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY 'Plan Cache Size (MB)' DESC
    

    To work around this issue, please try the following:

    • Use stored produces instead of Ad-hoc queries as many as possible.
    • Run the follow statement to flush the ad hoc plans (this will not flush store procedure plans):
      USE master;
      GO
      DBCC FREESYSTEMCACHE('SQL Plans');
      GO
      

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Edited by Alex Feng (SQL)Moderator Monday, March 07, 2011 8:52 AM remove OPTIMIZE FOR ADHOC WORKLOADS suggestion which is only in SQL Server 2008
    • Marked as answer by SNIVAS Monday, November 14, 2011 1:08 PM
    Monday, March 07, 2011 7:53 AM
  • Chunsong Feng

     

    The OP is using SQL Server 2005 so I think that OPTIMIZE FOR ADHOC WORKLOADS  is SQL Server 2008 feature

    http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx

     

    You can use Parameterisation to Force if you know what db caused that problem


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 07, 2011 8:39 AM
  • Hi Uri,

    Thanks for your correction, I have updated my reply accordingly.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, March 07, 2011 8:53 AM
  • Either , create the SPs around the queries or run them using sp_executesql :

    How Memory is being used :
    To determine what plans are in the cache and how often they're used we can use sys.dm_os_memory_cache_counters dm view .

    SELECT  TOP 6
     LEFT([name], 20) as [name],
     LEFT([type], 20) as [type],
     [single_pages_kb] + [multi_pages_kb] AS cache_kb,
     [entries_count]
    FROM sys.dm_os_memory_cache_counters
    order by single_pages_kb + multi_pages_kb DESC

    here :

    CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.
    CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.  This includes any dynamic SQL or raw SELECT statements sent to the server.
    CACHESTORE_PHDR  These are algebrizer trees for views, constraints and defaults.  An algebrizer tree is the parsed SQL text that resolves the table and column names.

    (you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

    Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.
    That is the reason the sal statements are going in to  Plan cache.

    You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.  There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

    After this to know the querry we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries

    select  TOP 100
     objtype,
        usecounts,
     p.size_in_bytes/1024 'IN KB',
     LEFT([sql].[text], 100) as [text]
    from sys.dm_exec_cached_plans p
    outer apply sys.dm_exec_sql_text (p.plan_handle) sql
    ORDER BY usecounts DESC


    and then we can check the query plans(and size) for the one we have some doubt .


    Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache).
    We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.

    Further , the query below can give us total currrent size of buffer pool .
    select count(*) AS Buffered_Page_Count
     ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
    from sys.dm_os_buffer_descriptors

    After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

    SELECT LEFT(CASE database_id
       WHEN 32767 THEN 'ResourceDb'
       ELSE db_name(database_id)
            END, 20) AS Database_Name,
     count(*)AS Buffered_Page_Count,
     count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
    FROM sys.dm_os_buffer_descriptors
    GROUP BY db_name(database_id) ,database_id
    ORDER BY Buffered_Page_Count DESC

    And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

    SELECT TOP 25
     obj.[name],
     i.[name],
     i.[type_desc],
     count(*)AS Buffered_Page_Count ,
     count(*) * 8192 / (1024 * 1024) as Buffer_MB
        -- ,obj.name ,obj.index_id, i.[name]
    FROM sys.dm_os_buffer_descriptors AS bd
        INNER JOIN
        (
            SELECT object_name(object_id) AS name
                ,index_id ,allocation_unit_id, object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p
                    ON au.container_id = p.hobt_id
                        AND (au.type = 1 OR au.type = 3)
            UNION ALL
            SELECT object_name(object_id) AS name  
                ,index_id, allocation_unit_id, object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p
                    ON au.container_id = p.hobt_id
                        AND au.type = 2
        ) AS obj
            ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
    WHERE database_id = db_id()
    GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
    ORDER BY Buffered_Page_Count DESC

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    • Marked as answer by SNIVAS Monday, March 07, 2011 4:58 PM
    Monday, March 07, 2011 10:18 AM
  • Just to clarify, the "max memory" setting only controls the size of the "buffer cache", not the entire size of the SQL Server application.  There are several other memory allocations in SQL Server.
    Monday, March 07, 2011 2:38 PM
  • Thanks Abhay Chaudhary,

     

    Thanks for all the help


    SNIVAS
    Monday, March 07, 2011 4:59 PM