SQL Server Performance

insufficent memory to run this query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by SQLDBAS, Oct 24, 2007.

  1. SQLDBAS New Member

    At peak I am seeing lot of memory pressure errors I am running 2k5sp2 ,win2k3
    HEre is the DBCC memory status .
    I have seen some negative values in this result set . anyone can shed some knowledge. If DTS are running in legacey mode from SQL 2005 does it create
    any memory pressure ?
    Memory Manager KB ------------------------------ --------------------VM Reserved 2458076VM Committed 178876AWE Allocated 12582912Reserved Memory 1024Reserved Memory In Use 0(5 row(s) affected)Memory node Id = 0 KB ------------------------------ --------------------VM Reserved 2453980VM Committed 174932AWE Allocated 12582912MultiPage Allocator 34696SinglePage Allocator 1930040(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 81720 MultiPage Allocator 2888(7 row(s) affected)MEMORYCLERK_SQLBUFFERPOOL (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 2401300 VM Committed 126652 AWE Allocated 12582912 SM Reserved 0 SM Commited 0 SinglePage Allocator 0 MultiPage Allocator 3496(7 row(s) affected)MEMORYCLERK_SQLQUERYEXEC (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 432 MultiPage Allocator 0(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 4280 MultiPage Allocator 88(7 row(s) affected)MEMORYCLERK_SQLUTILITIES (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 840 VM Committed 840 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 576 MultiPage Allocator 7776(7 row(s) affected)MEMORYCLERK_SQLSTORENG (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 3392 VM Committed 3392 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 3672 MultiPage Allocator 12320(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 2296 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 64 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 264 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_QSRANGEPREFETCH (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_BHF (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 96 MultiPage Allocator 0(7 row(s) affected)MEMORYCLERK_SQLQERESERVATIONS (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 0 VM Committed 0 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 52664 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 128 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 6088 MultiPage Allocator 6232(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 64 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 8176 MultiPage Allocator 0(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 292480 MultiPage Allocator 1208(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 1438416 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 24 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 64 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 136 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 3872 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 568 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 3616 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 1080 MultiPage Allocator 24(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 136 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 176 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 76960 MultiPage Allocator 208(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 312 MultiPage Allocator 0(7 row(s) affected)OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------------------------------- -------------------- VM Reserved 8192 VM Committed 8192 AWE Allocated 0 SM Reserved 0 SM Commited 0 SinglePage Allocator 4000 MultiPage Allocator 0(7 row(s) affected)Buffer Distribution Buffers------------------------------ -----------Stolen 192434Free 23588Cached 48981Database (clean) 1090292Database (dirty) 217539I/O 0Latched 30(7 row(s) affected)Buffer Counts Buffers------------------------------ --------------------Committed 1572864Target 1572864Hashed 1307861Stolen Potential 21619External Reservation 6422Min Free 800Visible 283648Available Paging File 3186538(8 row(s) affected)Procedure Cache Value------------------------------ -----------TotalProcs 30TotalPages 217538InUsePages 436(3 row(s) affected) Global Memory Objects Buffers------------------------------ --------------------Resource 645Locks 503XDES 167SETLS 8SE Dataset Allocators 16SubpDesc Allocators 8SE SchemaManager 483SQLCache 9225Replication 394ServerGlobal 31XP Global 2SortTables 1523(12 row(s) affected) Query Memory Objects Value------------------------------ -----------Grants 3Waiting 0Available (Buffers) 6071Maximum (Buffers) 12654Limit 12654Next Request 0Waiting For 0Cost 0Timeout 0Wait Time 0Last Target 13320(11 row(s) affected)Small Query Memory Objects Value------------------------------ -----------Grants 0Waiting 0Available (Buffers) 666Maximum (Buffers) 666Limit 666(5 row(s) affected)Optimization Queue Value------------------------------ --------------------Overall Memory 1862369280Target Memory 1217970176Last Notification 2Timeout 6Early Termination Factor 5(5 row(s) affected)Small Gateway Value------------------------------ --------------------Configured Units 32Available Units 26Acquires 6Waiters 0Threshold Factor 250000Threshold 250000(6 row(s) affected)Medium Gateway Value------------------------------ --------------------Configured Units 8Available Units 7Acquires 1Waiters 0Threshold Factor 12Threshold 16916252(6 row(s) affected)Big Gateway Value------------------------------ --------------------Configured Units 1Available Units 0Acquires 1Waiters 0Threshold Factor 8Threshold 152246272(6 row(s) affected)MEMORYBROKER_FOR_CACHE Value-------------------------------- --------------------Allocations 48960Rate 1358Target Allocations 39214Future Allocations 0Last Notification 2(5 row(s) affected)MEMORYBROKER_FOR_STEAL Value-------------------------------- --------------------Allocations 192148Rate -1372Target Allocations 148678Future Allocations 0Last Notification 2(5 row(s) affected)MEMORYBROKER_FOR_RESERVE Value-------------------------------- --------------------Allocations 6327Rate -599Target Allocations 14208Future Allocations 50616Last Notification 0(5 row(s) affected)DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  2. Greg Larsen New Member

  3. satya Moderator

Share This Page