0

I have a challenging issue that I am facing and would like to lean on this community for advice and possibly answers to this issue.

We are running a dedicated server: Windows 2008 R2 64bit OS, 32 GB of RAM.
Our SQL Server version: MS SQL Server 2012 (11.0.3000).

Intermittently our server will drop to 0-5% CPU usage, during that time our applications have a hard time (or just cannot) connect to the SQL Server. Further, within the database logs I am reading this:

FlushCache: cleaned up 12007 bufs with 100094 writes in 130307 ms (ovoided 667 new dirty bufs) for db 7:0
last target outstanding: 4, avgWriteLatency 44
average throughput: 0.41 MB/sec, I/O saturation: 12161, context switches 25796

The above logs are written several times per day, however a few of these instances occur when our CPU drops to an extremely low rate. Our CPU for this server normally runs around 30-60% during business hours and I have never seen it drop to this level.

The only major changes have been an update in RAM from 16 GB to 32 GB. There were some updates to the maintenance backups, but those fire at 12AM (PST).

I am available to add any further logs or information and am extremely grateful for any help concerning this issue.

  • Have you configured "max server memory"? I believe I saw this kind of issue before when "max server memory" is not set. – jyao Jan 24 '17 at 18:52
  • Couple of things to check - max memory - set it away from default for 32GB if running only sql server then set to 28GB. Power options --> high performance and check if any database is set to auto close or auto shrink - sys.databases is_auto_close and auto_shrink. – Kin Shah Jan 24 '17 at 18:55
  • Sounds like severely underpowered I/O to me. What is the disk system? Also when you "a dedicated server" do you mean a physical server, the only virtual machine on a VM host, or something else? – Aaron Bertrand Jan 24 '17 at 20:25
  • It is a dedicated server. There is a single drive that has hardware emulated split, making a C: and D: drive. Dell Virtual Disk SCSI Disk Device I hope that this answers your question. – Caleb.Mikel Jan 24 '17 at 21:05
  • Virtual SCSI? I hate to say it, but that sounds very far from optimal. – Aaron Bertrand Jan 25 '17 at 1:03
0

It sounds somewhat like you are hitting a bit of memory pressure. Dirty Buffers are pages in memory that have changes in them that have not been written to disk yet. Normally they would stay in memory until one of the various processes clean them up and/or write them to disk.

There are some things you will want to look into but with only the information you have given here I have to ask, "How much ram have you reserved for the OS?"

You are running a 64 bit version of Windows. Now, the 32 bit kernel will use 4 GB of RAM. However, we have found (the hard way) that 64 bit versions of the OS will use 8 - 12 GB of RAM dependent on how busy the Sql server is. By that I mean are you doing a lot of reading and writing to disk. If the OS can't allocate the memory it needs for this (because the Sql server has sucked it all up) you will see the CPU being consumed for the additional context switching it needs to do to write those buffers out to disk. When you added the additional RAM to the server did you reset the Sql Server Max Memory setting? When you only had 16 GB of RAM your Sql server was constrained as much as the windows kernel was and so you were likely using virtual disk (hard drive space) quite a bit. When you added more RAM you opened things up a bit and now the Sql server is attempting to keep more of the database(s) in memory.

  • Thank you! I am not sure if this corrected the issue, however I did see that the max server memory (in MB) was set to 12000. very low for as much RAM as we have. Would this explain why the SQL server seems to stop - even though the Object Explorer shows that it has a green arrow (meaning running), but it stops performing. and the CPU drops to a very low level. – Caleb.Mikel Jan 24 '17 at 19:12
  • No. There is something else going on. I do think you should increase your sql server Max Memory setting to something like 24576. That will leave 8 GB for the OS. Then you should start monitoring your memory and disk utilization. – Richard L. Dawson Jan 24 '17 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.