question

DJSamTheCat avatar image
DJSamTheCat asked

server process memory has been paged out

Typically when I start seeing this message I know the database indexes are fragmented and a simple rebuild will resolve it. A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 5728, committed (KB): 1708096, memory utilization: 0% However, I reindexed the DB last week and now these are occuring more frequently and users are complaining of performance/application hang issues. SQL 2005 Server SP-4 32-Bit
error-messagememory
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DJSamTheCat avatar image DJSamTheCat commented ·
VM Reserved: 1705296 VM Committed: 1704408
0 Likes 0 ·
DJSamTheCat avatar image DJSamTheCat commented ·
I'm confused on the following values: DBCC MEMORYSTATUS Buffer Distribution Buffers ------------------------------ ----------- Stolen 5057 Free 8450 Cached 113802 Database (clean) 69269 Database (dirty) 8214 I/O 0 Latched 0 (7 row(s) affected) Buffer Counts Buffers ------------------------------ -------------------- Committed 204792 Target 204792 Hashed 77484 Stolen Potential 75645 External Reservation 0 Min Free 256 Visible 204792 Available Paging File 653652 (8 row(s) affected)
0 Likes 0 ·
DJSamTheCat avatar image DJSamTheCat commented ·
Physical Memory: 4GB Virtual Memory: 4095MB Max SQL server memory: 2147483647
0 Likes 0 ·
DJSamTheCat avatar image DJSamTheCat commented ·
After reviewing every log on the server I realized a correlation between the SQL memory events and when the security software would begin to update. BINGO! The software was set to update every 2hrs. The SQL events were occuring in the same minute of the hour that the update started. Furthermore, the events were spaced apart by 2/4/6/8 hour increments. Needless to say I have modified the update policy. Additionally, I had an issue last night requiring a server reboot. I took this opportunity to increase the page from from 4097MB to 5886MB. Now I wait and watch the performance over the next few weeks.
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
On startup SQL Server takes up a small amount of memory then as the queries are run the memory pool grows and grows up to the limit set for the SQL Server instance. SQL Server will take up the vast majority of memory on the server leaving little for anything else. If however there is a demand from other application SQL server is forced to give back some of the memory from the pool. The result is the error above and a server that runs very slowly as it pages memory back and forth between processes. The odd thing is the memory utilisation of 0% figure. Try running DBCC MEMORYSTATUS and check the VMCommited to VM Reserved ratio to see what it says. Check other processes running on the server and try limiting the amount of memory that SQL Server can access so the contention is less likely to happen. It also possible if you have application that use extended stored procedures or COM calls that you have a memory leak. You could have a look at select * from sys.dm_exec_sessions to see if you have any spid that are using a disproportionate amount of memory.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image tanglesoft commented ·
Eeek!!. How much physical and virtual memory does the server have and what percentage of that memory is allocated to SQL Server, right click on the SQL instance and go into the memory page and look at the maximum server memory number. Run select * from sys.dm_exec_sessions s order by memory_usage desc and look to see if any process is has memory usage much higher than other processes. Are there any processes in the same list cpu_time or logical_reads figures that are very high, these could be candidates for the high usage.
0 Likes 0 ·
tanglesoft avatar image tanglesoft tanglesoft commented ·
Buffer counts committed figure of 204792 suggests that 1.6Gb is the memory ceiling for SQL server (204792 * 8192 / 1024 / 1024). The stolen figure representing active use and the cached plans look to be a reasonable percentage of the available page count.
0 Likes 0 ·
tanglesoft avatar image tanglesoft commented ·
The following link contains many SQL statements that can be run to help diagnose memory usage. From what you have said it is as though SQL server is quite happy with it's memory allocation except external process that are demanding memory pages. What else runs on the server.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
If your indexes are fragmenting more quickly then you need to: 1 defragment them more frequently 2 Consider if the indexes are actually appropriate. You may have inserts and updates that are causing fragmentation that is simply too high a cost to justify the index. Personally I'd be looking to re-index (or at least look for fragmentation) every day or two and elect to defragment based on the results of those results. Check out dm_db_index_usage_stats. There are some great uses of this in this free eBook - http://www.red-gate.com/community/books/dynamic-management-views As a rough starter see what details you have from this: SELECT database_id, object_id, index_id, SUM(user_seeks + user_scans + user_lookups) AS [Index_Usage], user_updates AS [Index_Updates], last_user_seek, last_user_scan, last_user_lookup, last_user_update FROM sys.dm_db_index_usage_stats AS DDIUS GROUP BY DDIUS.database_id, DDIUS.object_id, DDIUS.index_id, DDIUS.user_updates, DDIUS.last_user_seek, DDIUS.last_user_scan, DDIUS.last_user_lookup, DDIUS.last_user_update, DDIUS.system_seeks, DDIUS.system_scans, DDIUS.system_lookups, DDIUS.system_updates, DDIUS.last_system_seek, DDIUS.last_system_scan, DDIUS.last_system_lookup, DDIUS.last_system_update ORDER BY user_updates desc If Index_Updates is high and Index_Usage is low then you **may** consider the future for this index.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.