Few weeks ago when I was teaching the SQL Server PTO Workshop, I didn’t do a deep dive into how to troubleshoot writelog wait type. Before I wrote something, I decided to look what was out there. I think Sakthivel does a far more justice to the issue then I would have. Please have a read, how T-Log works and why you might see writelog wait type.
- Sakthivel Chidambaram; What is WRITELOG waittype and how to troubleshoot and fix this wait in SQL Server? (Link)
- SQLCAT, Lindsey Allan; Diagnosing Transaction Log Performance Issues and Limits of the Log Manager (Link)
- SQLCAT Blog; Deploying SQL Server 2005 with SAN #3 (Link)
- CSS SQL Server Engineer Blog; Discussion About SQL Server I/O (Link)
- MSDN, SQL Server 2005 Technical Articles; SQL Server I/O Basics, Chapter 2 (Link)
- Bob Dorr, Microsoft SQL Server Principal PFE; Microsoft SQL Server Database Engine I/O PowerPoint Presentation (Link)
(copied links from Sakthivel’s Blog).
SQL Canada (SQLCAN) 
Mohit:
I am having quite a bit of I/O issues and wanted your thoughts on how to determine my current configuration of read\write on the HBA Controller.
I know how to investigate performance of reads\writes via Perfmon. But, will like to know how ti go about determine whether someone has set explicit ratios on SCSI or HBA Controllers.
Thanks,
Daniel
Hi Daniel,
Why do you suspect the problem is in HBA? What is you’re latency you are experiencing? How much throughput is being generated by SQL Server? IOPS? Have you eliminated SQL Server as the cause (e.g. database design, indexes, statistics, SQL code being executed)?
For HBA cards, we can look at the queue depth setting; have a read at http://blogs.msdn.com/b/joesack/archive/2009/01/28/sql-server-and-hba-queue-depth-mashup.aspx.