I have a weekly task that runs at 4 AM Sundays to rebuild all indexes on about 10 databases. In the past few weeks, after that occurs (or maybe during), the system starts complaining about memory pressure, and either shuts down SQL server or grinds to a slow crawl. Restarting SQL server process fixes it. There is 8 GB of memory and it is SQL 2008 R2. This used to happen a few years ago also, but I added 4 GB of memory and it stopped. I can't just keep adding memory, can anyone advise what might be going on?
Without a lot more information about what's going on, no, I can't. I'd strongly suggest getting a lot more monitoring in place to understand what's happening on your system. Collect metrics around memory and around wait statistics. Some of the metrics are: Memory: Available MB, Paging File: %Usage, Process: Private Bytes. Also check DBCC MEMORYSTATUS to see if COMMITTED is above TARGET. You'll need to collect these during normal operations in order to establish a baseline and then capture them during your index rebuild in order to understand what's going on. Additional counters that are good to collect include: Memory:Available Bytes, Pages/sec, Page Fautls/sec, Pages Input/sec, Pages Output/sec, Paging File%Usage Peak, Paging File: %Usage SQL Server:Buffer Manager:Buffer cache hit ratio, Page Life Expectancy, Checkpoint, Pages/sec, Lazy writes/sec SQL Server:Memory Manager: Memory Grants Pending, Target Server Memory, Total Server Memory Process:Private Bytes Again, capture these over time when things are normal and then compare them to the time when things are sketchy.