0

I have a machine that has SQL Server 2008 Standard installed on a Windows 2003 R2 server. Periodically (about once an hour) I am getting Event ID 17890 several times in a row. An example:

6:28:54 "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): 10652, committed (KB): 628428, memory utilization: 1%%.

6:34:27 "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 332 seconds. Working set (KB): 169780, committed (KB): 546124, memory utilization: 31%%."

6:38:55 "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 600 seconds. Working set (KB): 245068, committed (KB): 546124, memory utilization: 44%%."

This pattern repeated at 7:26 - 7:37, 8:26 - 8:36, 9:24 - 9:35 and so with the same increasing working set and memory utilization pattern. I don't have any (known) background tasks running at this time. Backups run at 2:00

This subsided from 11:00 at night until it resumed at 4:00 in the morning and has been continuing the intermittent 10 minute glitch periods.

As this server has plenty of RAM (the commit charge has peaked at 2,871,564 of 4,194,012 physical) I disabled the paging files after reading several items I dug up searching Google and not finding any of them changing the situation. This pattern I am documented is after removing the paging files, so I'm not even sure where we are paging the SQL process could be going. I also changed the SQL process memory to have a minimum of 500MB and a maximum of 2GB of RAM (as this is a light duty database server serving only a small workgroup).

Has anyone encountered this? Prior to disabling the page files this error would cause 5 minutes of disk thrashing that disabled access to the databases, files, IIS webs and so on. Since disabling the page files it just logs strange things, but I'm not seeing a performance drop at least. Any suggestions would be welcome.

Improve this question

3 Answers 3

Reset to default
2

After a talk with Microsoft Support, the official answer is: http://support.microsoft.com/kb/2001745

Will not fix, will not workaround, only recommended upgrading operating system to some edition of 2008. I have moved my SQL install and decommissioned the server.

Improve this answer
0

Best thing to do is run some profiling to catch on of the occurrences. Most likely it's a single bad query that requiring a large table to be pulled due to a bad where clause or poor indexing. This is more dependent on the size of the tables and Database as a whole.

After profiling the issue a time or two, you should be able to narrow the issue down to a query or two that needs tweaking.

Another thing to check is the settings for IIS's application pools and workers. If you have heavy code in the App start for the sight(s), then they will have to run that code when the worker spins back up again. IIS will kill the worker threads after a certain amount of idle time, too much CPU time, or after using too high a percentage of resources. The timer based one could be the problem, and that would cause it to happen during idle times at night. The other issue could be search indexers hitting the site at night.

Improve this answer
2
  • I would think this might be possible (and it still might be, so I will run some profiler time), but two things seem odd. One is that it happens at 10:30 at night and 4:30 in the morning (as examples), when this database should be idle. Additionally, it seems to happen on the half hour when it does occur, but we don't have any periodic processes on that schedule (that I know of: I did review our background tasks looking for any on such a schedule but all of them are one shot per day).
    – Godeke
    Jan 12, 2011 at 4:10
  • Well, being paged out during idle times isn't as huge a concern as it is during business use. But the paging during idle times could be a result of SQL's own optimizations and tweaking during idle times, but not having enough RAM for the task. That will depend more on the size of the tables and indexes.
    – Benjamin Anderson
    Jan 12, 2011 at 17:42
-1

Had the same problem and it was caused by Kaspersky Antivirus trying to update itself every hour or so. Uninstalling Kaspersky solved it.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .