Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

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:

  1. 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?

  2. 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.

  3. 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?

  4. 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.

share|improve this question
    
Sweet poster. Do they sell a full-size printed version? –  db2 Mar 20 '13 at 17:24
    
"and all are a RAID 1 configuration." -- can you confirm this is not a typo? This is a pretty unusual configuration. –  Jon Seigel Mar 20 '13 at 17:29
    
Jon, on our SAN, I have 6 physical disks seperated into 3 disk groups (LUN's) allocated for SQL usage. I have one mirrored (RAID 1) pair for TempDB, one mirrored pair for TransLog (ldf) files and one mirrored pair for Database (mdf) files. Each of the LUN's is then mapped as a mount point with direct iSCSI access to the VM. Does that make sense and does it still seem unusual? –  bmccleary Mar 20 '13 at 17:54
    
Yes, it's quite unusual. Given 6 disks, I would normally expect it's configured into a 6-disk pool of either RAID 10 or RAID 5. How many different databases are on this server, and what are their access patterns (OLTP or DW, mostly reads or mostly writes)? –  Jon Seigel Mar 20 '13 at 18:05
1  
100 dbs menas 100 logs, means your log is just as random as the data. make a raid 10 out of those 6 disks and place everything together, just the striping will give better IO in the end, imho (I'm not a storage expert). And try to identify those spike cause(s), both in tempdb and in memory paging. –  Remus Rusanu Mar 20 '13 at 19:56
show 3 more comments

1 Answer

up vote 3 down vote accepted

SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms

750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.

SQLServer:Databases\Log Growths, I'm at 325

Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.

PhysicalDisk\Avg. Disk sec/Write ... 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?

Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.

PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.

It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?

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

So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.

share|improve this answer
    
Remus, thank you for your detailed explanation. With this information, for the past hour I have setup a trace log to look at the Log Flush Wait Time for each database independently. I have realized that the average value of 750ms is for the "_total" counter, but looked at independently, each database is really only showing wait times of 6 to 15 milliseconds. Therefore, I "think" (though I could be mistaken) that the perf counter is simply adding these all up. Knowing now that each database is showing a relatively low value, would this make sense and does it look normal? –  bmccleary Mar 20 '13 at 19:38
    
OK, now that aligns with the 8ms write time on the disk writes. It seems though you have a lot of logs on that drive. Not much streaming, head is moving all the time, latency is significant. But not much you could do with that many databases, short of SSDs. –  Remus Rusanu Mar 20 '13 at 19:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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