SQL Server Performance

Memory Consumption on SQL 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by pradepravindran, Jun 17, 2008.

  1. pradepravindran New Member

    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.
    pradepravindran, Jun 17, 2008
    #2

  2. satya Moderator

    Have you compared DBCC MEMORYSTATUS results between the servers?
    There are other ways to obtain information such as :
    Using sys.dm_os_memory_clerks DMV that will help how much memory SQL Server has allocated through AWE.
    SELECT SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb] FROM sys.dm_os_memory_clerks
    The Same DMV can be used to get the memory consumption by internal components of SQL Server 2005.
    SELECT TOP 10 type, SUM(single_pages_kb) as [SPA Mem, Kb] FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY SUM(single_pages_kb) DESC
    To see the physical memory allocation:
    SELECT (Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb FROM sys.dm_os_sys_info
    satya, Jun 18, 2008
    #3

  3. pradepravindran New Member

    By running the above command it just shows 72 mb of ram utilized by sql.Pasting both the servers dbccmemstatus report.
    Thanks for the time.
    Production server where the performance is slow.
    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.
    Preproduction server where the performance is fast when compared.
    Memory Manager KB
    ------------------------------ --------------------
    VM Reserved 1644440
    VM Committed 98848
    AWE Allocated 6450576
    Reserved Memory 1024
    Reserved Memory In Use 0
    (5 row(s) affected)
    Memory node Id = 0 KB
    ------------------------------ --------------------
    VM Reserved 1640344
    VM Committed 94904
    AWE Allocated 6450576
    MultiPage Allocator 13664
    SinglePage Allocator 161744
    (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 3088
    MultiPage Allocator 2336
    (7 row(s) affected)
    MEMORYCLERK_SQLBUFFERPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 1615584
    VM Committed 70992
    AWE Allocated 6450576
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 2000
    (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 480
    MultiPage Allocator 88
    (7 row(s) affected)
    MEMORYCLERK_SQLUTILITIES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 120
    VM Committed 120
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 120
    MultiPage Allocator 0
    (7 row(s) affected)
    MEMORYCLERK_SQLSTORENG (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 5696
    VM Committed 5696
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 1176
    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 288
    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 72
    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 48
    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 24
    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 3568
    MultiPage Allocator 7360
    (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 23488
    MultiPage Allocator 176
    (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 91536
    MultiPage Allocator 1152
    (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 12016
    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 120
    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 16
    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 104
    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 1040
    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 2112
    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 2488
    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 408
    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 176
    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 40
    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 408
    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 616
    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 17784
    MultiPage Allocator 0
    (7 row(s) affected)
    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 1125
    Free 179
    Cached 19093
    Database (clean) 783620
    Database (dirty) 1472
    I/O 0
    Latched 0
    (7 row(s) affected)
    Buffer Counts Buffers
    ------------------------------ --------------------
    Committed 805489
    Target 820743
    Hashed 785092
    Stolen Potential 163154
    External Reservation 0
    Min Free 256
    Visible 193024
    Available Paging File 1048230
    (8 row(s) affected)
    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 1388
    TotalPages 16061
    InUsePages 324
    (3 row(s) affected)

    Global Memory Objects Buffers
    ------------------------------ --------------------
    Resource 176
    Locks 2226
    XDES 32
    SETLS 8
    SE Dataset Allocators 16
    SubpDesc Allocators 8
    SE SchemaManager 267
    SQLCache 204
    Replication 2
    ServerGlobal 26
    XP Global 2
    SortTables 2
    (12 row(s) affected)

    Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 137784
    Maximum (Buffers) 137784
    Limit 137784
    Next Request 0
    Waiting For 0
    Cost 0
    Timeout 0
    Wait Time 0
    Last Target 145035
    (11 row(s) affected)
    Small Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 7251
    Maximum (Buffers) 7251
    Limit 7251
    (5 row(s) affected)
    Optimization Queue Value
    ------------------------------ --------------------
    Overall Memory 1267384320
    Target Memory 1063583744
    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 19094
    Rate 0
    Target Allocations 147807
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_STEAL Value
    -------------------------------- --------------------
    Allocations 1119
    Rate 0
    Target Allocations 129832
    Future Allocations 0
    Last Notification 1
    (5 row(s) affected)
    MEMORYBROKER_FOR_RESERVE Value
    -------------------------------- --------------------
    Allocations 0
    Rate 0
    Target Allocations 154710
    Future Allocations 34446
    Last Notification 1
    (5 row(s) affected)
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.



    pradepravindran, Jun 18, 2008
    #4

Share This Page