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:
- Get as much RAM as you can afford or will fit in the server
- Run SQL Server 2005 on a dedicated server with nothing else running on the server
- Don’t leave Remote RDP sessions running constantly on your SQL Server
- Don’t leave SQL Server Management Studio running locally, constantly on your SQL Server
- Use the 64-bit version of Windows Server 2003 R2 with SP2
- Use the 64-bit version of SQL Server 2005 with SP2
- Grant the "Lock Pages in Memory" right to the SQL Server Service Account
- Set the Max Server Memory option in SQL Server to a value that leaves anywhere from 1-3GB left for the OS
- Monitor SQL Server:Buffer Manager – Page Life Expectancy (higher is better)
- Monitor SQL Server:Buffer Manager – Buffer Cache Hit Ratio(higher is better)
- 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: SQL Server, SQL Server 2005
Glenn Berry's SQL Server Performance 
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
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
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
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
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
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
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
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
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
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
v