up vote 2 down vote favorite
share [g+] share [fb]

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.

link|improve this question
feedback

migrated from stackoverflow.com Nov 12 '10 at 11:30

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 2 down vote

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 :)

link|improve this answer
Can you elaborate on the AutoShrink thing please? – mikhailitsky Nov 10 '10 at 23:36
feedback
up vote 0 down vote

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.

link|improve this answer
No, there is no antivirus software installed. But viruses are very unlikely, as almost no additional software is installed on the box, let alone from untrusted sources. – mikhailitsky Nov 10 '10 at 23:12
What about the activity monitor? I'm wondering if your server has some jobs scheduled to run at regular intervals to do something like backup log files or otherwise archive data. – jwiscarson Nov 10 '10 at 23:14
Sorry, didnt finish answering due to a blackout. Regarding the activity - I forgot to mention that I was able to take the game server down once during such a slamming and it stopped immediately. However, a very single consequent call to a stored procedure in the DB with a regular SELECT triggered the slamming again. – mikhailitsky Nov 10 '10 at 23:18
There is a single job scheduled which is performed about every 10 or 15 minutes, but all it does is just a number of selects, so this is an unlikely culprit I think. – mikhailitsky Nov 10 '10 at 23:19
Still worth a shot, right? You can check the performance of each of these queries by highlighting one, right-clicking it, and then selecting "Analyze Query in Database Engine Tuning Advisor" or "Display Estimated Execution Path". Either of these options will show bottlenecks in the query's execution. The Database Engine Tuning Advisor will also recommend indexes and provide you with statistics regarding performance gains. – jwiscarson Nov 10 '10 at 23:23
show 1 more comment
feedback

Your Answer

 
or
required, but never shown
discard