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
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.
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.