September 15, 2010 at 7:30 am
*ninjaedit: crazy because it's called letch, not latch 🙂 sry
Hi folks,
we have installed a brand new 2008 Sql Server. After moving our databases we discover an performance issue:
Sometimes the response time of the SQL Server raises for some seconds and then it works fine again.
After some investigations wey discovered, that there is a thingy with Letches. Here is a picture of out sys.dm_os_wait_stats
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
PAGELATCH_NL0000
PAGELATCH_KP0000
PAGELATCH_SH1964838253641973
PAGELATCH_UP12813175180319
PAGELATCH_EX2980775781536910759
PAGELATCH_DT0000
PAGEIOLATCH_NL0000
PAGEIOLATCH_KP0000
PAGEIOLATCH_SH27531633413135919993
PAGEIOLATCH_UP33455659402726
PAGEIOLATCH_EX5236472117001002
PAGEIOLATCH_DT0000
As u see we have a lot of entrys for Latches EX/UP/SH for Pages and PageIO.
Fine. 😛
But wtf I can't find any good solution proposal anywhere. We have no queuing on the discs, nor any other hardware related stuff.
I hope someone of you has some experiance with this problem and can give my an hind.
Thx in advance
Roland
September 15, 2010 at 7:59 am
r.desort (9/15/2010)
*ninjaedit: crazy because it's called letch, not latch 🙂 sryHi folks,
we have installed a brand new 2008 Sql Server. After moving our databases we discover an performance issue:
Sometimes the response time of the SQL Server raises for some seconds and then it works fine again.
After some investigations wey discovered, that there is a thingy with Letches. Here is a picture of out sys.dm_os_wait_stats
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
PAGELATCH_NL0000
PAGELATCH_KP0000
PAGELATCH_SH1964838253641973
PAGELATCH_UP12813175180319
PAGELATCH_EX2980775781536910759
PAGELATCH_DT0000
PAGEIOLATCH_NL0000
PAGEIOLATCH_KP0000
PAGEIOLATCH_SH27531633413135919993
PAGEIOLATCH_UP33455659402726
PAGEIOLATCH_EX5236472117001002
PAGEIOLATCH_DT0000
As u see we have a lot of entrys for Latches EX/UP/SH for Pages and PageIO.
Fine. 😛
But wtf I can't find any good solution proposal anywhere. We have no queuing on the discs, nor any other hardware related stuff.
I hope someone of you has some experiance with this problem and can give my an hind.
I'll start by sorting the report by wait_time_ms desc then analyze one by one.
In regards to the I/O subsystem... PAGELATCH_EX is telling there is waiting on physical I/O.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 15, 2010 at 8:27 am
I would take a look at this sort of things maybe ... :
select ...
from
sys.dm_os_waiting_tasks wtt
inner join sys.dm_exec_requests r on wtt.session_id = r.session_id
inner join sys.dm_exec_sessions s on r.session_id = s.session_id
inner join sys.dm_os_tasks t on wtt.session_id = t.session_id
and t.exec_context_id = wtt.exec_context_id
Cheers,
R
September 15, 2010 at 8:31 am
PageIOLatch is a wait on physical IO, not PageLatch.
None of those waits look particularly high. Also, that DMV shows info since SQL started. If SQL's been running a long time, those values may fairly low.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply