Troubleshooting Memory Issues in SQL Server 2005

Microsoft’s Bob Dorr, who is a senior escalation engineer in PSS, has a great post that has some troubleshooting steps for memory issues in SQL Server 2005.

I have had some recent painful experience with issues similar to this, and here is what I recommend for large SQL Server 2005 deployments:

  1. Get as much RAM as you can afford or will fit in the server
  2. Run SQL Server 2005 on a dedicated server with nothing else running on the server
  3. Don’t leave Remote RDP sessions running constantly on your SQL Server
  4. Don’t leave SQL Server Management Studio running locally, constantly on your SQL Server
  5. Use the 64-bit version of Windows Server 2003 R2 with SP2
  6. Use the 64-bit version of SQL Server 2005 with SP2
  7. Grant the "Lock Pages in Memory" right to the SQL Server Service Account
  8. Set the Max Server Memory option in SQL Server to a value that leaves anywhere from 1-3GB left for the OS
  9. Monitor SQL Server:Buffer Manager – Page Life Expectancy (higher is better)
  10. Monitor SQL Server:Buffer Manager – Buffer Cache Hit Ratio(higher is better)
  11. Monitor SQL Server:Memory Manager – Memory Grants Pending (lower is better)

 

Use some of these DMV queries to understand what is going on with your memory.

-- Top 25 consumers of memory from Buffer Pool 
SELECT TOP 25 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
-- Get query mix and use counts
SELECT usecounts, cacheobjtype, objtype, bucketid 
FROM sys.dm_exec_cached_plans AS cp 
WHERE cacheobjtype = 'Compiled Plan' 
ORDER BY objtype, usecounts DESC
 
-- Breaks down buffers by object (table, index) in the buffer cache (Tom Davidson)
    SELECT b.database_id, db = db_name(b.database_id)
        ,p.object_id
        ,object_name(p.object_id) AS objname
        ,p.index_id
        ,buffer_count=count(*)
    FROM sys.allocation_units a,
        sys.dm_os_buffer_descriptors b,
        sys.partitions p
    WHERE a.allocation_unit_id = b.allocation_unit_id
    AND a.container_id = p.hobt_id
    AND b.database_id = db_id()
    GROUP BY b.database_id,p.object_id, p.index_id
    ORDER BY buffer_count DESC
 
-- Look at the contents of the buffer pool 
    SELECT count(*)AS cached_pages_count,
    obj.name as objectname, ind.name AS indexname, obj.index_id AS indexid
    FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN
    (
        SELECT object_id AS objectid, object_name(object_id) AS name, index_id,allocation_unit_id
        FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p 
        ON au.container_id = p.hobt_id 
        AND (au.type = 1 or au.type = 3)
        UNION ALL
        SELECT object_id AS objectid, object_name(object_id) AS name, index_id,allocation_unit_id
        FROM sys.allocation_units AS au
        INNER JOIN sys.partitions AS p 
        ON au.container_id = p.partition_id 
        AND au.type = 2

    ) AS obj 

    ON bd.allocation_unit_id = obj.allocation_unit_id
    LEFT OUTER JOIN sys.indexes ind  
    ON  obj.objectid = ind.object_id 
    AND  obj.index_id = ind.index_id
    WHERE bd.database_id = db_id()
    AND bd.page_type IN ('data_page', 'index page')
    GROUP BY obj.name, ind.name, obj.index_id
    ORDER BY cached_pages_count DESC

 

Technorati Tags: ,
This entry was posted in SQL Server 2005. Bookmark the permalink.

11 Responses to Troubleshooting Memory Issues in SQL Server 2005

  1. Unknown says:

    wow gold cheap wow gold buy wow gold world of warcraft gold wow guide runescape runescape money wow gold cheap wow gold buy wow gold world of warcraft gold lotro lotro gold guild wars guild wars gold dofus dofus kamas nba live 火箭队 google排名 google左侧排名 google排名服务 maple story maple story mesos maple story maple story mesos

  2. Unknown says:

    lotro lotro gold lotr online lotr online lotro money lord of the ring lotro lotr online lotro gold lotro money lord of the ring maplestory maple story maplestory mesos maple story mesos cheap maplestory mesos cheap maple story mesos buy maplestory mesos cheap maplestory mesos wow gold buy wow gold cheap wow gold wow wow power leveling wow powerleveling world of warcraft world of warcraft gold world of warcraft power leveling world of warcraft powerleveling runescape runescape gold runescape money runescape runescape gold runescape money

  3. Unknown says:

    lotro gold lord of the rings gold lord of the rings online gold lotro gold lord of the rings gold lord of the rings online gold Warhammer gold Warhammer online gold Warhammer money War gold War money Tabula Rasa Credit lotro gold lord of the rings gold lord of the rings online gold lord of the rings online gold lord of the rings gold Tabula Rasa Credit World of Warcraft gold PotBS Doubloon Pirates of the Burning Sea Doubloon PotBS Gold Pirates of the Burning Sea Gold lotro gold lord of the rings gold lord of the rings online gold Tabula Rasa Credit Warhammer gold Warhammer online gold PotBS Doubloon PotBS gold Age of Conan gold Age of Conan gold Pirates of the Burning Sea gold Pirates of the Burning Sea Doubloon

  4. Unknown says:

    sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maple story trade maple story trade maple story trade maple story trade maple story trade maple story trade maple story trade

  5. Unknown says:

     sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade

  6. Unknown says:

     maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maple story gold maple story gold maple story gold maple story gold maple story gold maple story gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maple story money maple story money maple story money maple story money maple story money maple story money maplestory money maplestory money maplestory money maplestory money maplestory money maplestory money buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos maple story maple story maple story maple story maple story maple story maplestory maplestory maplestory maplestory maplestory maplestory sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos maple story shop maple story shop maple story shop maple story shop maple story shop maple story shop cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos

  7. Unknown says:

    lotro lotro lotro lotro lotro lotro lotro gold lotro gold lotro gold lotro gold lotro gold lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold lotr lotr lotr lotr lotr lotr lotr gold lotr gold lotr gold lotr gold lotr gold lotr gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us

  8. Unknown says:

    sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maplestory mesos sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story gold sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maple story money sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory gold sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money sell maplestory money maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maplestory trade maple story trade maple story trade maple story trade maple story trade maple story trade maple story trade maple story trade

  9. Unknown says:

    sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade

  10. Unknown says:

     maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maple story gold maple story gold maple story gold maple story gold maple story gold maple story gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maple story money maple story money maple story money maple story money maple story money maple story money maplestory money maplestory money maplestory money maplestory money maplestory money maplestory money buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos maple story maple story maple story maple story maple story maple story maplestory maplestory maplestory maplestory maplestory maplestory sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos maple story shop maple story shop maple story shop maple story shop maple story shop maple story shop cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s