SQL Server Performance

Problems with Memory consumption 93% SQL 2005 Enterprise32 bits

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by turahk1, Aug 7, 2009.

  1. turahk1 New Member

    Greetings,
    I have a problem with the memory usage on a Stratus server with SQL 2005 enterprises installed. It seems to be having a memory leak and consumes all of the resources in a small period. About a week ago the client reported a problem consisting of processes running slow after the installation of the Service Pack 2 of SQL 2005. Even though I have done various analysis including running the index tuning wizard the problem persist.
    Is my understanding that the problem does not concern programming issues since all applications are running without problems. Furthermore, I have 39 other clients using the same applications in their servers and they too have installed the same service pack and no problems have been reported.
    The information about the server is enclosed.

    OS Windows 2003 Server SP2
    Memory 8 GB
    DataBase size 53.45 GB
    Log Size 165.05 MB

    Memory
    Total Used 94.40 %
    Used By SQL 81.27%
    Memory Paging 0.18/s
    Buffer Cache hit 99.87%
    Proc. Cache hits 79.24%

    CPU Usage 34.72%

    Disk
    Page Reads 9.67/s
    Page Writes 34.80/s

    RowID Flag Totallogspace usedlogspace status
    8 1 256.7 8.4 0

    NAMEDBIDCMPTLEVELDB_SIZE_IN_MBStatus
    IHESDB_188057116Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, IsAutoCreateStatistics, IsAutoUpdateStatistics, Collation=SQL_Latin1_General_CP1_CI_AS, IsAutoShrink

    Single use plans (usecounts=1)Re-used plans (usecounts>1)re-use %total usecounts
    18444770.84631


    dbNameBufferPoolMB
    IHESDB_16287


    DBIDobjectidtable nameindex_idbuffer count
    81676493697PAT_ANCILLARY_CHARGES1183024
    81676493697PAT_ANCILLARY_CHARGES759416
    81676493697PAT_ANCILLARY_CHARGES332796
    81332772401EHR_PATIENT_HEADER113765
    8284488738PAT_ACCOUNT_RECORD113284
    8303964805PAT_INSURANCE_PLANS379702
    81868494381MPI_PATIENT_RECORD359212
    81868494381MPI_PATIENT_RECORD897846


    RowIDdbNameFlagFileidFileGroupTotal_SpaceUsedSpaceFreeSpaceFreePctReport_Date
    8IHESDB_101PRIMARY56860.251704.55155.70.0918/7/2009

    SQL setting for memory
    use of AWE enable

    Minimum of server memory in (MB) = 0
    Maximum of server memory in (MB) = 6656

    since the server have 8 gb RAM for OS have 2gb

    the problem is since this past monday all process from client to server has dramatically decrease in performance. to give you and idea, process that took 2 second befores SQL SP2 now the clients have to wait more than 3 minutes for the same results.
    I follow the querys and here is teh results

    http://sqlblogcasts.com/blogs/sqldbatips/archive/2007/07/18/troubleshooting-performance-issues-with-tokenandpermuserstore-in-sql2005.aspx

    select name, count(*) from sys.dm_os_memory_cache_entries
    group by name
    order by count(*) desc


    name(No column name)
    IHESDB_11680
    SQL Plans840
    Object Plans600
    sxcCacheStore310
    SystemRowsetStore270
    Bound Trees266
    tempdb146
    mssqlsystemresource125
    IHESSystem105
    TokenAndPermUserStore103
    Temporary Tables & Table Variables86
    msdb78
    master62
    Extended Stored Procedures17
    model14
    ReportServer$WORKING14
    ReportServer$WORKINGTempDB14
    ObjPerm - IHESDB_112
    ObjPerm - IHESSystem4
    ObjPerm - master3
    SOS_StackFramesStore1

    select* from TokenAndPermUserStore
    loaddatesizemb
    8/7/20090.45

    select
    * from TokenAndPermFlush

    no result empty

    SELECTSUM(single_pages_kb + multi_pages_kb) AS "CurrentSizeOfTokenCache(kb)"
    FROM sys.dm_os_memory_clerks
    WHERE name = 'TokenAndPermUserStore'
    CurrentSizeOfTokenCache(kb)
    464

    wait_typewait_time_spctrunning_pct
    CXPACKET59055.7524.0924.09
    WAITFOR41653.4716.9941.08
    ASYNC_NETWORK_IO29141.7311.8952.96
    PAGEIOLATCH_SH24913.6310.1663.12
    SOS_SCHEDULER_YIELD13433.235.4868.6
    OLEDB13410.915.4774.07
    MSQL_XP10202.84.1678.23
    LATCH_EX8736.333.5681.8
    LCK_M_S8032.23.2885.07
    PAGEIOLATCH_EX7503.283.0688.13
    SLEEP_BPOOL_FLUSH5971.632.4490.57
    WRITELOG4923.812.0192.58
    ASYNC_IO_COMPLETION3983.891.6294.2
    BACKUPBUFFER3932.611.695.81
    %signal (cpu) waits%resource waits
    1.298.8
    Page Life Expectancy
    7418
    typeSPA Mem, Kb
    CACHESTORE_OBJCP52128
    CACHESTORE_SQLCP27248
    OBJECTSTORE_SNI_PACKET7544
    CACHESTORE_PHDR6352
    MEMORYCLERK_SOSNODE5504
    MEMORYCLERK_SQLGENERAL5288
    USERSTORE_SCHEMAMGR3656
    MEMORYCLERK_SQLSTORENG2984
    MEMORYCLERK_SQLCONNECTIONPOOL2888
    OBJECTSTORE_LOCK_MANAGER2584
    Page Life expectancy
    empty

    Any ideas why?
  2. techbabu303 New Member

    - Have 8GB of RAM and enabled AWE on SQL server , is PAE switch enabled on the OS level.
    - Have you checked fragmentation level of indexes on the database ?
    - Also check if you have any blocks when CPU > 80%
    -Sat

Share This Page