I want to validate the optimal performance of our SQL Server and in doing so, I have gone through all the performance counters listed at the following link and create a trace log to analyze the data.
I have gone through three days worth of collected data and compared it to the recommended values listed in that chart, and though most of my values look good, there a just a few that are outside of the recommended values listed and I want to get some feedback from others on how their systems compare and if any of my values should raise a red flag.
As a point of clarification, our SQL Server is 2008, running on a virtual machine, with 4 cores and 8 GB of memory. The SQL VM connects directly, via iSCSI, to our SAN which has three separate disk groups (separate spindles) for the Database files, Log files and TempDB, and all are a RAID 1 configuration.
Here are the counters that I am concerned by:
For the SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms. According to the chart listed above, it states that this value should be around 0 ms. Why would my value be significantly higher?
For SQLServer:Databases\Log Growths, I'm at 325 and for SQLServer:Databases\Log Truncations, I'm at 14200. I assume that this is since the start of SQL Server, correct? If so, the chart says that growths should be around 0. Is this something I should worry about? The last reboot of the server was a month ago and there are about 100 databases on the server.
For the Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400). According to the chart, this value should be less than 10. Should I be concerned by these spikes?
Finally for the PhysicalDisk\Avg. Disk sec/Write counters:
4a. For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms. The guide states that these values should be less than 20 ms. Should I be concerned by these tenth of a second spikes?
4b. For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?
4c. Regarding these disk counters, in Windows Disk Management > Properties > Policies, the disks don’t have the “Enable write caching on the device” checked nor the “Turn off Windows write-cache buffer flushing on the device” checked. Should these be enabled for a SAN that has a battery backed cache? Could this be the cause of 4a and 4b? If so, can they enabled during production hours without interrupting data flow?
Any input in regards to what others see on their production systems or if any of these values seem abnormal are appreciated.