Highest priority areas to evaluate for extreme disk latency
- The write latency on both tempdb files is 2500-2800 query the DMV io stats (see glenn berry).
- The read latency is impacted on query a few databases with the highest being 159, and another 20 between 20-100ms.
- SQL Error log shows thousands of events for the IO requests taking longer than 15 seconds. The vast majority of these are specifically on tempdb (no surprise considering the above!)
- The write latency isn't significant for many other databases (a few 30's here and there, but most are good).
- Machine has 128GB of ram, 24 CPUS
Problem's I've noted in reviewing
- VLF count is very high for around 10 databases. The primary database of 500 GB has 7865 VLF count, with the remaining following from 4276 down to 1057.
- Disk configuration shows all databases and logs are on the same drives. However, in looking at the underlying configuration, it appears that it is on NetApp, so I'm having trouble getting a clear picture on if this is a SAN disk configuration issue (the magic black box that gets hard to diagnose), or if something else is causing this. For me to go down the SAN route I need a solid indication its disk io subsystem, as there will be pushback on this unfortunately.
- Instant file initialization off
- 2 TEMPDB files for entire instance, with 24 logical cpus.
- Log grow amounts are significantly under what it should be, showing around 100MB for many databases, and the 500GB database has 450MB growth amount. Evaluating the autogrowth events in the last 22 days didn't show a significant pattern of growth events though. This was suprising. Maybe the fragmentation happened before the restart 22 days ago, as I'm not showing hardly any growth events since the reboot.
- Waits Evaluated. Max dop set to 4 (24 logical cpus hyperthreaded i believe), Degree of Parallelism set to 5 (I've advised to consider 8 maxdop and 50 DOP with caution to test impact
- 25% of waits CXPacket
- 16% writelog
- 12% LCK_M_U
- 11% PAGEIOLATCH_SH
My current recommendations
- Add 6 more tempdb files to bring total to 8 to help eliminate any contention.
- Evaluate the SAN configuration for confirmation that the setup is optimal for SQL server, ie if using a large aggregate, ensure the number of disk shelves is plenty to prevent contention between log, tempdb, and data files. Identify the "performance pool" and try to separate tempdb into a separate LUN with it's own dedicated disks,ideally SSD, to optimize tempdb performance and eliminate contention with any other SAN traffic.
- Adjust autogrowth settings of log and database to better prevent autogrowth events, as well as prevent excessive VLF counts from affecting performance.
- Implement Ola Hallengren (respect!) scripts for daily maintenance, as I'm also noticing over 100 indexes with >10% row change since last statistics update (with some anomalies show a few tables with extremely out of date stats).
I've spent hours and will continue to spend hours trying to advise on this particular area, but I'm hoping I can get clarification/confirmation/correction on any specific areas I'm off on, as there will be pushback on any direction I provide at this point.
Please let me know of any more helpful detail I could provide.
Add 2 tempdb files
Having said all of that, there are a couple things I noticed:
1) Based on your write latency numbers for tempdb and the fact that writelog waits are so high. You might want to focus attention on tempdb. It may be possible, if you don't have a lot of memory pressure (unlikely, I know) that you could get a significant boost by using a small RAMdrive (I'd try 4gb as a start) and placing the tembdb transaction log in the RAMdrive.
2) Another big wait stat is LCK_M_U, indicating waits for UPDATE statements. Trace your update activity to see what UPDATES encourage you to focus your efforts on tuning Update queries, especially any that are hitting tempdb explicitly via temp tables or implicitly perhaps via spools (HAVING clauses, GROUP BY clauses, hash and merge joins, etc). It might be that the UPDATE statement coding practices are forcing large spools into tempdb unnecessarily.
3) The PAGEIOLATCH_SH indicates a separate issue from the LCK_M_U waits. The former occurring to support read operations with SHared locks and the latter occurring for Update operations in flight. Again, somebody might be writing code that loads a lot more data into the data cache than needed and discards the unneeded data, causing a lot of thrash.
4) The CXPACKET waits are often ok, since SQL Server will always parallelize if the optimizer feels like that's its best option and it's given more than one CPU. I usually recommend also setting the Cost Threshold for Parallelism setting much higher than the default (60 is my preference). This tells SQL Server to only parallelize the bigger, longer-running queries rather than every little one that comes along. (The default is 5 – ridiculous!) Having said that, an underlying and often hidden cause of CXPACKET waits is if the files of a database reside on drives of differing speeds. So when investigating the NAS subsystem, check that the various files used by tempdb are not on paths with very different speeds.
Hope that helps,