SQL Server Performance

Memory issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by programmer76, Sep 25, 2008.

  1. programmer76 New Member

    Hi,
    I am troubleshooting a sql server where sql server buffer cache hit ratio is between 70 to 80 percent on couple of times during a day. This server has AWE enabled with 16 GB memory. Here is the out put of DBCC memorystatus command. I tried to run performance counters but couldn't find anything in particular over there. Can any one please help me out?
    Memory Manager KB
    ------------------------------ --------------------
    VM Reserved 1656592
    VM Committed 181104
    AWE Allocated 13983744
    Reserved Memory 1024
    Reserved Memory In Use 0
    (5 row(s) affected)
    Memory node Id = 0 KB
    ------------------------------ --------------------
    VM Reserved 1652432
    VM Committed 177096
    AWE Allocated 13983744
    MultiPage Allocator 22384
    SinglePage Allocator 876208
    (5 row(s) affected)
    MEMORYCLERK_SQLGENERAL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16552
    MultiPage Allocator 3560
    (7 row(s) affected)
    MEMORYCLERK_SQLBUFFERPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 1613856
    VM Committed 139296
    AWE Allocated 13983744
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 3680
    (7 row(s) affected)
    MEMORYCLERK_SQLOPTIMIZER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8248
    MultiPage Allocator 88
    (7 row(s) affected)
    MEMORYCLERK_SQLUTILITIES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 240
    VM Committed 240
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 528
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLSTORENG (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 9664
    VM Committed 9664
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 5976
    MultiPage Allocator 936
    (7 row(s) affected)
    MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 784
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLCLR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLSERVICEBROKER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 160
    MultiPage Allocator 192
    (7 row(s) affected)
    MEMORYCLERK_SQLHTTP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SNI (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 72
    MultiPage Allocator 16
    (7 row(s) affected)
    MEMORYCLERK_FULLTEXT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLXP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_BHF (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 240
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_HOST (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 72
    MultiPage Allocator 64
    (7 row(s) affected)
    MEMORYCLERK_SOSNODE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 18992
    MultiPage Allocator 9552
    (7 row(s) affected)
    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 24
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_OBJCP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 282296
    MultiPage Allocator 304
    (7 row(s) affected)
    CACHESTORE_SQLCP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 494312
    MultiPage Allocator 3800
    (7 row(s) affected)
    CACHESTORE_PHDR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 3752
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_XPROC (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 56
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_TEMPTABLES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 168
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_NOTIF (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_VIEWDEFINITIONS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_XMLDBTYPE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_XMLDBELEMENT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_XMLDBATTRIBUTE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_STACKFRAMES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 8
    (7 row(s) affected)
    CACHESTORE_BROKERTBLACS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 272
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERKEK (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERDSH (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERRSB (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERREADONLY (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 32
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_BROKERTO (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_EVENTS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0
    (7 row(s) affected)
    CACHESTORE_SYSTEMROWSET (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 6008
    MultiPage Allocator 0
    (7 row(s) affected)
    USERSTORE_SCHEMAMGR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 13544
    MultiPage Allocator 0
    (7 row(s) affected)
    USERSTORE_DBMETADATA (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 14568
    MultiPage Allocator 0
    (7 row(s) affected)
    USERSTORE_TOKENPERM (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 112
    MultiPage Allocator 0
    (7 row(s) affected)
    USERSTORE_OBJPERM (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 976
    MultiPage Allocator 0
    (7 row(s) affected)
    USERSTORE_SXC (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 184
    MultiPage Allocator 0
    (7 row(s) affected)
    OBJECTSTORE_LBSS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 336
    MultiPage Allocator 0
    (7 row(s) affected)
    OBJECTSTORE_SNI_PACKET (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 1136
    MultiPage Allocator 48
    (7 row(s) affected)
    OBJECTSTORE_SERVICE_BROKER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 256
    MultiPage Allocator 0
    (7 row(s) affected)
    OBJECTSTORE_LOCK_MANAGER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 4096
    VM Committed 4096
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 6352
    MultiPage Allocator 0
    (7 row(s) affected)
    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 6468
    Free 171
    Cached 103058
    Database (clean) 1577112
    Database (dirty) 60734
    I/O 0
    Latched 0
    (7 row(s) affected)
    Buffer Counts Buffers
    ------------------------------ --------------------
    Committed 1747543
    Target 1747543
    Hashed 1637846
    Stolen Potential 65578
    External Reservation 0
    Min Free 256
    Visible 184320
    Available Paging File 494041
    (8 row(s) affected)
    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 12166
    TotalPages 98065
    InUsePages 3070
    (3 row(s) affected)

    Global Memory Objects Buffers
    ------------------------------ --------------------
    Resource 546
    Locks 797
    XDES 50
    SETLS 8
    SE Dataset Allocators 16
    SubpDesc Allocators 8
    SE SchemaManager 1646
    SQLCache 1551
    Replication 2
    ServerGlobal 26
    XP Global 2
    SortTables 2
    (12 row(s) affected)

    Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 58311
    Maximum (Buffers) 58311
    Limit 58340
    Next Request 0
    Waiting For 0
    Cost 0
    Timeout 0
    Wait Time 0
    Last Target 61410
    (11 row(s) affected)
    Small Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 3069
    Maximum (Buffers) 3069
    Limit 3069
    (5 row(s) affected)
    Optimization Queue Value
    ------------------------------ --------------------
    Overall Memory 1210318848
    Target Memory 320069632
    Last Notification 1
    Timeout 6
    Early Termination Factor 5
    (5 row(s) affected)
    Small Gateway Value
    ------------------------------ --------------------
    Configured Units 32
    Available Units 32
    Acquires 0
    Waiters 0
    Threshold Factor 250000
    Threshold 250000
    (6 row(s) affected)
    Medium Gateway Value
    ------------------------------ --------------------
    Configured Units 8
    Available Units 8
    Acquires 0
    Waiters 0
    Threshold Factor 12
    (5 row(s) affected)
    Big Gateway Value
    ------------------------------ --------------------
    Configured Units 1
    Available Units 1
    Acquires 0
    Waiters 0
    Threshold Factor 8
    (5 row(s) affected)
    MEMORYBROKER_FOR_CACHE Value
    -------------------------------- --------------------
    Allocations 103059
    Rate 78
    Target Allocations 135746
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_STEAL Value
    -------------------------------- --------------------
    Allocations 6462
    Rate 0
    Target Allocations 39071
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_RESERVE Value
    -------------------------------- --------------------
    Allocations 0
    Rate 0
    Target Allocations 65505
    Future Allocations 32896
    Last Notification 1
    (5 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  2. programmer76 New Member

    I reconfirmed on client side. Buffer to cache hit ratio is above 95 for a while now. Because when i ran performance monitor I always found buffer cache hit ratio above 98 and I was wondering why I couldn't replicate the problem from my side.
    They are having problem with sql server plan cache: cache to hit ratio that value is sometimes going below 80%. Is that even considered a problem or that number should be higher also in 90s?
    Also other counter SQL cache memory is also less than 10 MB even after having the physical memory of 14 GB on the server.
    I hope this helps and may be now and gurus can suggest me the right path to go to take care of this!
    Thanks
  3. moh_hassan20 New Member

    as you use AWE with memory 16G , be sure that you configure AWE as to my reply in the post:
    http://sql-server-performance.com/Community/forums/p/28332/151625.aspx#151625

    review: http://support.microsoft.com/kb/271624



  4. programmer76 New Member

    I went through your article and confirmed that everything is configured the way you have mentioned on this server. No problems in configuration. Boot.ini file has /pae switch. Administrator account is running the server and has lock pages permission. Awe is enabled as well which you can see from dbcc memorystatus command output.
    So this problem is not occuring because of configuration issue. What should I look next to find the cause for this problem? Also please not again that buffer manager cache hit ratio is good above98%. It is the sql server plan cache: cache to hit ratio which is going in 70% range.
    Thanks
  5. moh_hassan20 New Member

    I seems that sql server plan cache: is a low, but it can be reasonable if you run lot of ad-hoc sql that is not parsed and not in cache ,
    or there is a recomilation for some stored procedures.
    to avoid Re-compilation, review:
    http://support.microsoft.com/kb/243586
    http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx


    review what is going on in the cache by running:
    select TOP 100 percent
    usecounts, objtype, cp.cacheobjtype ,
    cp.size_in_bytes,
    LEFT([sql].[text], 100) as [text]
    from sys.dm_exec_cached_plans cp
    outer apply sys.dm_exec_sql_text (cp.plan_handle) sql
    ORDER BY usecounts DESC


    you will find valuable information which may help in tunning cache value.
  6. gurucb New Member

    CACHESTORE_OBJCP, CACHESTORE_SQLCP add up to procedure cache and in our case 780 MB is used for procedure cache 780712 which is good amount. As far as i know (I may be wrong) but Min Free < Free but in this case Free is less than Min Free.
    It will be when
    1 SQL Server is just restarted.
    2 SQL Server just released memory and then it is again grabbing memory.
    3 SQL Server is under memory bottlenect.
    But in this case it may be the case or 1 and 2 as SQL Server seems to be grabbing memory.SQL Server buffer cache hit ratio should be as high as possible (as sql server can do read ahead reads). Higher cache hit ratio better. And Proc cache can not take memory from extended (AWE) region it is only data and index pages.
    Can you please clarify when was DBCC Memorystatus collected. Suggestion would be to collect that in a loop with date time for over a period of time till issues ouccrs couple of time to look at trend instead of just one snapshot.

Share This Page