question

spectrum24 avatar image
spectrum24 asked

System crash after index rebuild

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?
sql-server-2008-r2memorysystem-databases
10 |1200

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
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.