-1

We built one windows 2012R2 server (it has enough free memory/disk space, high performance of CPU), but it would be frozen for around half hour every 12 hours.

The symptoms:

  1. couldn't access the website hosted in the server

  2. couldn't remote into the server

The findings:

  1. many EventID=508 (for svchost.eve), 833 (for sqlserver) in the EventView of the server.

One of the EventID=508: svchost (2128) A request to write to the file "C:\Windows\system32\LogFiles\Sum\Svc.log" at offset 4775936 (0x000000000048e000) for 4096 (0x00001000) bytes succeeded, but took an abnormally long time (36 seconds) to be serviced by the OS. This problem is likely due to faulty hardware. Please contact your hardware vendor for further assistance diagnosing the problem.

  1. below is the chart for Disk performance:

    you will see % IDLE Time is less than 40%, Queue Length reaches 100 sometimes. Even it seems the data collector stop working between 8:37AM and 8:50AM.

It seems the bottleneck is Disk I/O, and it caused the server was frozen. But I have no idea how to further investigate.

My questions:

  1. is % IDEL Time < 40% very bad? what about Queue length reaches 100?

  2. what may cause the data collector stop working?

  3. If the root cause is faulty hardware, how the server is working fine during most time of one day?

enter image description here

2
  • Does the problem always occur between 8:30 and 9:00 am? Oct 21, 2020 at 18:39
  • occured same time at least two days after the server was built up three days ago.
    – Sphinx
    Oct 21, 2020 at 18:42

1 Answer 1

0

Ah, and always the same...

(it has enough free memory/disk space, high performance of CPU)

Yes, but you know what you do not talk of? DISK PERFORMANE.

bytes succeeded, but took an abnormally long time (36 seconds)

An operation on the dis did not take milliseconds, as it should, but 36 SECONDS. You basically got a 40 ton truck and complain it is not as fast as a formula 1 car. Let me guess - super slow and large SATA Hard Disk where everyone using databases either uses dozens of discs or has LOOONG moved to SSD which sort of have 100 times the IO capacity of a high end disc.

There are 4 reasons for this, possibly:

  • Defective hardware. Happens, nothing WE can do here. Check on 2nd machine.
  • Defect drivers. See first topic.
  • AWFUL hardware selection that is totally unsuitable because it does totally NOT provide the IO bandwidth required for the application.

And finally:

  • Incompetent database developer that never bothered to learn proper db programming and thus has no idea what an index is, resulting in every query using WAAAAAAYY more IO bandwidth than it should overloading a IO subsystem that otherwise MAY be good enough. Seen that way too often. https://use-the-index-luke.com/ - and yes, "Incompetent" is the friendly way, what would you call a taxi driver that blows up the car when he starts it because he never learned driving ;)

That sort of nails it down. The fact that things happen at the same time and last ONLY half an hour indicates that it is not one of the first 2 items - those happen more random, i.e. not always at the same time.

Start assessing thhe hardware capacity and checking which queries are slow. Then possibly hammer the programmers to actually do their job and add proper indices.

3
  • Thanks for the analysis. I am checking on SQL logs.
    – Sphinx
    Oct 21, 2020 at 18:41
  • finnaly we found out there was one job to fully backup the database every 12 hours. If stop it, the server worked fine.
    – Sphinx
    Oct 26, 2020 at 16:33
  • Ah, so not enough IO - in your case because the backup was taking it over the possible budget. SERIOUSLY over it ;)
    – TomTom
    Oct 26, 2020 at 18:59

You must log in to answer this question.

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