March 29, 2011 at 8:04 am
Background information: Same job ran on two equivalent servers (1 prod, 1 QA), during 2 different timeframes, with the same basic data. The one took 6 hours longer than the other. One job took 24.5 hr (prod) to complete and the other 30.5 hr (QA). One particular step took 3.5 hours longer in prod then QA, even though the overall job in prod was faster. Why the difference?
Upon examining, I saw that the step that ran 3.5 hours longer in prod than QA had alot of Latch Wait, particularly Latch Buffer Wait. I'm trying to figure out what this means and how to fix it. A lot of articles I have read have said to add indexes, but it is proprietary code, so I can't touch it. Articles have also said to add secondary tempdb files, which I've tried and it didn't make a difference.
The other aspects of the job look like a lot of the steps in QA had more I/O wait. I'm wondering if the QA SAN is different then Prod and not performing as well. Any other reasons that I/O Wait might be occurring more in QA than Prod?
Any help in figuring this out would be greatly appreciated. Thanks in advance!
March 29, 2011 at 8:15 am
What's the exact name of the wait that you're seeing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2011 at 8:35 am
I am actually just using an admin tool (Quest's Performance Analysis) that just displays it as "Latch Buffer Wait". When I ran "SELECT * FROM sys.dm_os_latch_stats," I am seeing a lot of PAGELATCH_SH, PAGELATCH_UP, PAGELATCH_EX, PAGEIOLATCH_SH, PAGEIOLATCH_UP, and PAGEIOLATCH_EX wait.
I think that's what you're looking for. If not, let me know. I'm still kinda new to this....
April 4, 2011 at 5:18 am
IO latches mean hardware - Your I/O subsystem, i.e. the disk is not responding fast enough which means that the SQL Server is spending more time waiting for the data to come to it rather than processing it.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply