I have a game server running on a box with Windows 2003 equipped with 4 GB of RAM. The game server is using a database installed on the same machine, managed by SQL Server 2008. About a week ago I started to observe a weird behavior - every so often (at about 24 hour intervals) the SQL Server starts to slam the disk heavily, so heavily that the box becomes almost unresponsive. I could detect the slamming in the performance monitoring tool that comes with Windows, where the Average Disk Queue Length was staying at the max 100 when this happens. When this occurs the Process monitor tool from Sysinternals shows that the SQL Server process is performing the ReadFile call on the database .mdf file continuously (many consequent calls). The database file is 3.2 GB. What makes it look so weird is that the database is constantly used 24/7 with roughly equal load, however, this slamming occurs during specific hours only. Does anybody know what can cause this problem and how to diagnose it further? Thanks in advance.
migrated from stackoverflow.com Nov 12 '10 at 11:30
This question came from our site for professional and enthusiast programmers.
Are there any schedule database maintenance jobs? (Database backups, DBCC CHECKDB, reindexing)?
If so, that may be your culprit. If not, make some :)
To see what activity is actually going on, use the SQL Profiler utility or run a SQL Trace to log activity to a file. This should tell you what kind of activity is actually taking place, and whether you need to tune some queries, or take some other kind of action.
One final idea: is AutoShrink on? If so, turn it off!
Here's some articles supporting that last statement:
I'll let the article titles speak for themselves :)
Does your game server have any anti-virus software installed on it?
If you have SQL Server Management Studio installed, I'd check the Activity Monitor when that happens, and see if you can isolate a particular process or scheduled job.