memory wait spikes

  • We can't say definitively that that is enough RAM. It depends on the workload and traffic rates more database size and number of databases. For very intensive OLTP applications, such as SAP, it is recommended to use a minimum of 4 GB of RAM per logical processor.

    It also depends on what else you have running on the server. For example, SSIS/DTS packages can use a lot of memory. So can SSAS.

    My main production server has 128 GB of RAM with 96 GB allocated to SQL Server .... and it uses 100% of its allocated memory. It's not running SAP, but it is OLTP intensive like SAP.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • TheSQLGuru (3/4/2010)


    Robert, that is not true at all, at least not since SP1 drastically restricted the allowable size of the procedure cache.

    Are you saying that the procedure cache is not part of the buffer pool?


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (3/4/2010)


    TheSQLGuru (3/4/2010)


    Robert, that is not true at all, at least not since SP1 drastically restricted the allowable size of the procedure cache.

    Are you saying that the procedure cache is not part of the buffer pool?

    Technically they are not part of the buffer pool, they steal pages from it for their own cache. But that isn't what I was getting at. I was addressing your comment that they are the largest part of the buffer pool.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • You said this was a histric problem. have you looked at and retained the default trace. it may tell you what was going on at the time of the problem.

  • I ran

    SELECT * FROM ::fn_trace_getinfo(0)

    and then

    SELECT *

    FROM fn_trace_gettable

    (...................\log_186.trc', default)

    GO

    don't see any dates in that table

    Do you know what is the default for retention days? How far back you can see?

Viewing 5 posts - 16 through 19 (of 19 total)

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