Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joshgilson
Starting Member
1 Post |
 Posted - 2014-10-07 : 21:34:12
|
| I have a MDS and my server crashes 2-4 times every day or so. I'm constantly having to repair the SQL Databases. My host said the following:"Our log monitoring caught, on every occasion, a RAID card failure. To address this point we took the original RAID card (fully updated BIOS) and replaced it with a factory-stock RAID card. The exact same errors persisted following the swap. This lead to further investigation about the storage input and output statistics -- where we found that your server's writes per second were exceptionally high. To give you some perspective, it's spiking to about 50% of what one of our shared servers do with far more clients and traffic. It's notable that we're using a higher end card on our shared servers (same as in MDS quote below) with far more drives to spread the IO load.The underlying issue here seems to be one of your client's site software (Joomla and/or SQL). It could be something that seems innocent, like user tracking, that causes a heavy write load. There could also be factors that compound the situation, like bot traffic that take an already bad situation and make it worse. That said, a good starting point would be to review your sites individually for write-heavy applications such as: live chat scripts, user tracking scripts, misconfigured caching, self-spidering software. Those are just some examples of what to look for."How do I narrow it down to find the site causing the issues? Any help would be greatly appreciated. -Josh |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-08 : 12:51:49
|
Well this is awkward, Josh. It's Tara from the MB team, Tam's friend.  When you say "repair the SQL Databases", are you referring to the repair option of DBCC CHECKDB? Does DBCC CHECKDB return clean for each of the databases? Which version and edition of SQL Server are you using? Do you have access to view the SQL Server Error Log? Are there any disk errors in there?I don't have experience with MDS, but I can help out on the SQL Server side and potentially on the Windows server side. To narrow down where the issue is, I would run SQL Profiler and Performance Monitor. For a Profiler trace, I would filter it so that I only trace for expensive things, such as reads > 5000 or duration > 1000 (Profiler has the data in milliseconds). For Performance Monitor, I would be looking at CPU, various SQL Server counters (Page Life Expectancy, Batches Requests/sec, SQL Compilations and SQL Recompilations to name a few), and the disks. For the disk counters, I would look at Avg Disk Sec/Read and Avg Disk Sec/Write for the logical disks that are relevant to the SQL Server. I would also check the event log. If DBCC CHECKDB is not coming back clean, you will likely see errors in the event log that point to a disk problem, which would be the host's issue and not yours. You may want to add a monitoring job that runs once a minute to track activity in the SQL Server. We use sp_WhoIsActive for that. It is invaluable when it comes to performance analysis. http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
Server Crashing A Lot