SQL Server Performance

DBCC Memory status output.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by pradepravindran, Jun 17, 2008.

  1. pradepravindran New Member

    My SQL server is running slow,here is output of DBCCMEMORY STATUS.What does this tell?
    Physical Memory :8 GB
    VM :16
    AWE :6
    Memory Manager KB
    ------------------------------ --------------------
    VM Reserved 1642688
    VM Committed 97520
    AWE Allocated 6837096
    Reserved Memory 1024
    Reserved Memory In Use 0
    (5 row(s) affected)
    Memory node Id = 0 KB
    ------------------------------ --------------------
    VM Reserved 1638592
    VM Committed 93576
    AWE Allocated 6837096
    MultiPage Allocator 13416
    SinglePage Allocator 279376
    (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 5624
    MultiPage Allocator 2312
    (7 row(s) affected)
    MEMORYCLERK_SQLBUFFERPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 1614040
    VM Committed 69848
    AWE Allocated 6837096
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 1960
    (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 520
    MultiPage Allocator 88
    (7 row(s) affected)
    MEMORYCLERK_SQLUTILITIES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 360
    VM Committed 360
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 112
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLSTORENG (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 5568
    VM Committed 5568
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 1696
    MultiPage Allocator 128
    (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 344
    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 80
    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 24
    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_HOST (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_SOSNODE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 5344
    MultiPage Allocator 7248
    (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 22880
    MultiPage Allocator 128
    (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 211552
    MultiPage Allocator 1128
    (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 10616
    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 104
    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 24
    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 240
    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 16
    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 72
    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 968
    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 2496
    MultiPage Allocator 32
    (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 2888
    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 160
    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 144
    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 16
    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 400
    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 1160
    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 272
    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 11392
    MultiPage Allocator 0
    (7 row(s) affected)
    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 1735
    Free 95
    Cached 33187
    Database (clean) 818633
    Database (dirty) 44
    I/O 0
    Latched 1
    (7 row(s) affected)
    Buffer Counts Buffers
    ------------------------------ --------------------
    Committed 853695
    Target 866992
    Hashed 818678
    Stolen Potential 148450
    External Reservation 0
    Min Free 256
    Visible 193024
    Available Paging File 1575253
    (8 row(s) affected)
    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 3211
    TotalPages 30801
    InUsePages 407
    (3 row(s) affected)

    Global Memory Objects Buffers
    ------------------------------ --------------------
    Resource 183
    Locks 1427
    XDES 84
    SETLS 8
    SE Dataset Allocators 16
    SubpDesc Allocators 8
    SE SchemaManager 315
    SQLCache 516
    Replication 2
    ServerGlobal 25
    XP Global 2
    SortTables 2
    (12 row(s) affected)

    Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 132212
    Maximum (Buffers) 132212
    Limit 132198
    Next Request 0
    Waiting For 0
    Cost 0
    Timeout 0
    Wait Time 0
    Last Target 139155
    (11 row(s) affected)
    Small Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 6958
    Maximum (Buffers) 6958
    Limit 6958
    (5 row(s) affected)
    Optimization Queue Value
    ------------------------------ --------------------
    Overall Memory 1267384320
    Target Memory 948076544
    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 33191
    Rate 3
    Target Allocations 147195
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_STEAL Value
    -------------------------------- --------------------
    Allocations 1731
    Rate 0
    Target Allocations 115732
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_RESERVE Value
    -------------------------------- --------------------
    Allocations 0
    Rate 0
    Target Allocations 148447
    Future Allocations 34446
    Last Notification 1
    (5 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  2. techbabu303 New Member

    I presume you have already checked the sysprocess in tables to see if there are queries which are long running
    Try the code below just to see which process is occupying CPU
    select top 10 pro.program_name,pro.cpu,pro.spid,
    pro.memusage,pro.physical_io,db.name,pro.status,
    pro.last_batch
    from sysprocesses pro,sysdatabases db
    where
    spid > 50
    and cpu > 50
    and memusage > 500
    and pro.dbid = db.dbid
    and pro.status !='sleeping'
    Once you know the program then you look further but if you are looking for more information on DBCC command check forum articles here.
    Cheers
    Sat
  3. pradepravindran New Member

    I have tried the same but couldnt find any process consuming more cpu.Am looking process occupying more memory.How to release memory allocated by AWE.
  4. techbabu303 New Member

    Are we looking at 32 bit OS with PAE enabled at windows level and AWE at database level ?
    If the answer to above is yes , can you look at task manager for sqlservr.exe process how much memory it is occupying now ?
    Cheers
    Sat
  5. sramesh New Member

    AWE enabled in database server and sql server occupied more than 8 GB RAM. we want to reduce the memory with out reboot.
  6. pradepravindran New Member

    Hi Sat
    Mem:8GB VM:16 AWE Enabled. SQL 2005 +Windows 2003 SP1 -64bit .AWE consumes 6/8gb
    We have a Preprod server with the identical confg.The jobs run at this server(produciton) takes twice the time of the preproduction server.CPU utlization is normal,since AWE is enable task manger dosent give you the exact value of memory used.By the output of the DBCC memory status can we conclude which process is consuming more mem?or do we have alternative script or tool to check it.
  7. satya Moderator

Share This Page