64 bit memory problem

  • I've got a standalone Microsoft SQL Server 2005 - 9.00.3042.00 (X64) running on windows server 2003 x64 SP2 with 4 dual core 3Ghz CPU and 8GB of RAM.

    SQL setup to use all 8GB of memory and

    We have a database with (sql 2000 compatibility level) which is used by 3rd party application.

    The server memory goes up to 5 GB and stays there and the server starts to slow down and eventually becomes unresponsive at which time we have to restart the server.

    Also i read about Grant 'Lock Pages In memory' right to the service account but i'm not sure where to do that.

    Any help is appreciated.

    Here is the dbcc memorystatus and sp_configure

    Memory Manager KB

    ------------------------------ --------------------

    VM Reserved 8501472

    VM Committed 3158272

    AWE Allocated 0

    Reserved Memory 1024

    Reserved Memory In Use 0

    Memory node Id = 0 KB

    ------------------------------ --------------------

    VM Reserved 8495712

    VM Committed 3152600

    AWE Allocated 0

    MultiPage Allocator 62936

    SinglePage Allocator 2649272

    MEMORYCLERK_SQLGENERAL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 49824

    MultiPage Allocator 4504

    MEMORYCLERK_SQLBUFFERPOOL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 8413184

    VM Committed 3070720

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 8

    MEMORYCLERK_SQLOPTIMIZER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 600

    MultiPage Allocator 128

    MEMORYCLERK_SQLUTILITIES (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 480

    VM Committed 480

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 88

    MultiPage Allocator 0

    MEMORYCLERK_SQLSTORENG (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 960

    VM Committed 960

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 1416

    MultiPage Allocator 304

    MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 760

    MultiPage Allocator 0

    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

    MEMORYCLERK_SQLSERVICEBROKER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 96

    MultiPage Allocator 320

    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

    MEMORYCLERK_SNI (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 80

    MultiPage Allocator 16

    MEMORYCLERK_FULLTEXT (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 24

    MultiPage Allocator 160

    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

    MEMORYCLERK_HOST (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 24

    MultiPage Allocator 0

    MEMORYCLERK_SOSNODE (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 17088

    MultiPage Allocator 13456

    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 48

    MultiPage Allocator 0

    CACHESTORE_OBJCP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 41664

    MultiPage Allocator 416

    CACHESTORE_SQLCP (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 2501808

    MultiPage Allocator 42440

    CACHESTORE_PHDR (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 17728

    MultiPage Allocator 64

    CACHESTORE_XPROC (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 256

    MultiPage Allocator 0

    CACHESTORE_TEMPTABLES (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 56

    MultiPage Allocator 0

    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

    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

    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

    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

    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

    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

    CACHESTORE_BROKERTBLACS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 456

    MultiPage Allocator 0

    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

    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

    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

    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

    CACHESTORE_BROKERREADONLY (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 104

    MultiPage Allocator 0

    CACHESTORE_BROKERTO (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 16

    MultiPage Allocator 0

    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

    CACHESTORE_SYSTEMROWSET (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 1328

    MultiPage Allocator 0

    USERSTORE_SCHEMAMGR (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 2880

    MultiPage Allocator 0

    USERSTORE_DBMETADATA (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 3056

    MultiPage Allocator 0

    USERSTORE_TOKENPERM (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 280

    MultiPage Allocator 0

    USERSTORE_OBJPERM (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 104

    MultiPage Allocator 0

    USERSTORE_SXC (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 152

    MultiPage Allocator 0

    OBJECTSTORE_LBSS (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 256

    MultiPage Allocator 880

    OBJECTSTORE_SNI_PACKET (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 2216

    MultiPage Allocator 48

    OBJECTSTORE_SERVICE_BROKER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 0

    VM Committed 0

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 288

    MultiPage Allocator 0

    OBJECTSTORE_LOCK_MANAGER (Total) KB

    ---------------------------------------------------------------- --------------------

    VM Reserved 16384

    VM Committed 16384

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 6392

    MultiPage Allocator 0

    Buffer Distribution Buffers

    ------------------------------ -----------

    Stolen 8766

    Free 2159

    Cached 322393

    Database (clean) 39882

    Database (dirty) 3632

    I/O 0

    Latched 0

    Buffer Counts Buffers

    ------------------------------ --------------------

    Committed 376832

    Target 759153

    Hashed 43514

    Stolen Potential 629486

    External Reservation 0

    Min Free 512

    Visible 759153

    Available Paging File 634374

    Procedure Cache Value

    ------------------------------ -----------

    TotalProcs 51726

    TotalPages 325547

    InUsePages 3218

    Global Memory Objects Buffers

    ------------------------------ --------------------

    Resource 258

    Locks 802

    XDES 56

    SETLS 16

    SE Dataset Allocators 32

    SubpDesc Allocators 16

    SE SchemaManager 359

    SQLCache 6125

    Replication 2

    ServerGlobal 48

    XP Global 2

    SortTables 2

    Query Memory Objects Value

    ------------------------------ -----------

    Grants 0

    Waiting 0

    Available (Buffers) 352870

    Maximum (Buffers) 352870

    Limit 352855

    Next Request 0

    Waiting For 0

    Cost 0

    Timeout 0

    Wait Time 0

    Last Target 365655

    Small Query Memory Objects Value

    ------------------------------ -----------

    Grants 0

    Waiting 0

    Available (Buffers) 12800

    Maximum (Buffers) 12800

    Limit 12800

    Optimization Queue Value

    ------------------------------ --------------------

    Overall Memory 4984823808

    Target Memory 2124873728

    Last Notification 1

    Timeout 6

    Early Termination Factor 5

    Small Gateway Value

    ------------------------------ --------------------

    Configured Units 64

    Available Units 64

    Acquires 0

    Waiters 0

    Threshold Factor 380000

    Threshold 380000

    Medium Gateway Value

    ------------------------------ --------------------

    Configured Units 16

    Available Units 16

    Acquires 0

    Waiters 0

    Threshold Factor 12

    Big Gateway Value

    ------------------------------ --------------------

    Configured Units 1

    Available Units 1

    Acquires 0

    Waiters 0

    Threshold Factor 8

    MEMORYBROKER_FOR_CACHE Value

    -------------------------------- --------------------

    Allocations 322395

    Rate 0

    Target Allocations 573019

    Future Allocations 0

    Last Notification 1

    MEMORYBROKER_FOR_STEAL Value

    -------------------------------- --------------------

    Allocations 8760

    Rate 0

    Target Allocations 259384

    Future Allocations 0

    Last Notification 1

    MEMORYBROKER_FOR_RESERVE Value

    -------------------------------- --------------------

    Allocations 0

    Rate 0

    Target Allocations 390040

    Future Allocations 139416

    Last Notification 1

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    NameMinimumMaximumConfig_valueRunning_value

    Ad Hoc Distributed Queries0100

    affinity I/O mask-2147483648214748364700

    affinity mask-2147483648214748364700

    affinity64 I/O mask-2147483648214748364700

    affinity64 mask-2147483648214748364700

    Agent XPs0111

    allow updates0100

    awe enabled0100

    blocked process threshold08640000

    c2 audit mode0100

    clr enabled0111

    cost threshold for parallelism03276755

    cross db ownership chaining0100

    cursor threshold-12147483647-1-1

    Database Mail XPs0111

    default full-text language0214748364710331033

    default language0999900

    default trace enabled0111

    disallow results from triggers0100

    fill factor (%)010000

    ft crawl bandwidth (max)032767100100

    ft crawl bandwidth (min)03276700

    ft notify bandwidth (max)032767100100

    ft notify bandwidth (min)03276700

    index create memory (KB)704214748364700

    in-doubt xact resolution0200

    lightweight pooling0100

    locks5000214748364700

    max degree of parallelism06400

    max full-text crawl range025644

    max server memory (MB)16214748364721474836472147483647

    max text repl size (B)021474836476553665536

    max worker threads1283276700

    media retention036500

    min memory per query (KB)512214748364720482048

    min server memory (MB)02147483647016

    nested triggers0111

    network packet size (B)5123276740964096

    Ole Automation Procedures0100

    open objects0214748364700

    PH timeout (s)136006060

    precompute rank0100

    priority boost0100

    query governor cost limit0214748364700

    query wait (s)-12147483647-1-1

    recovery interval (min)03276700

    remote access0111

    remote admin connections0100

    remote login timeout (s)021474836472020

    remote proc trans0100

    remote query timeout (s)02147483647600600

    Replication XPs0100

    scan for startup procs0100

    server trigger recursion0111

    set working set size0100

    show advanced options0111

    SMO and DMO XPs0111

    SQL Mail XPs0111

    transform noise words0100

    two digit year cutoff1753999920492049

    user connections03276700

    user options03276700

    Web Assistant Procedures0100

    xp_cmdshell 0111

    Alex S
  • check this out http://msdn2.microsoft.com/en-us/library/ms190730.aspx

  • local security policy on the server in the user rights assignment

    add the account that SQL is running under

  • We run a similar configuration here and Micorosft PSS has recommended to set max. server memory to 6GB.

  • Thank you all for helping me out

    bbychkov looks like sql server is flushing the memory properly after setting the max to 6GB.

    Alex S

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply