Long waits on LATCH_EX and CXPACKET

I executed 'DBCC SQLPERF(WAITSTATS)' command. After I looked at results, I
was quite surprised. Lots of waiting on LATCH_EX, with average wait time 102
ms, and lots of waiting on CXPACKET, with average wait time 78 ms. What can
be reasons of such behavior, how to remove this waits? Below is results of
'DBCC SQLPERF(WAITSTATS)' command.
Wait Type Requests Wait Time
Signal Wait Time
-- --
-- --
MISCELLANEOUS 100.0 0.0
0.0
LCK_M_SCH_S 22.0 50424.0
16.0
LCK_M_SCH_M 10.0 1080.0
0.0
LCK_M_S 2173.0 600388.0
677.0
LCK_M_U 74.0 1189.0
16.0
LCK_M_X 202.0 198498.0
20206.0
LCK_M_IS 53.0 452577.0
16.0
LCK_M_IU 0.0 0.0
0.0
LCK_M_IX 28.0 20435.0
16.0
LCK_M_SIU 0.0 0.0
0.0
LCK_M_SIX 0.0 0.0
0.0
LCK_M_UIX 0.0 0.0
0.0
LCK_M_BU 0.0 0.0
0.0
LCK_M_RS_S 0.0 0.0
0.0
LCK_M_RS_U 0.0 0.0
0.0
LCK_M_RIn_NL 0.0 0.0
0.0
LCK_M_RIn_S 0.0 0.0
0.0
LCK_M_RIn_U 0.0 0.0
0.0
LCK_M_RIn_X 0.0 0.0
0.0
LCK_M_RX_S 0.0 0.0
0.0
LCK_M_RX_U 0.0 0.0
0.0
LCK_M_RX_X 0.0 0.0
0.0
SLEEP 544534.0 2.0353794E+8
2.0340557E+8
IO_COMPLETION 266375.0 641165.0
671.0
ASYNC_IO_COMPLETION 32.0 578000.0
0.0
RESOURCE_SEMAPHORE 0.0 0.0
0.0
DTC 0.0 0.0
0.0
OLEDB 2362526.0 4.1322363E+9
8.3835622E+8
FAILPOINT 0.0 0.0
0.0
RESOURCE_QUEUE 1092937.0 6.0596307E+8
2.0275229E+8
ASYNC_DISKPOOL_LOCK 25752.0 0.0
0.0
UMS_THREAD 0.0 0.0
0.0
PIPELINE_INDEX_STAT 0.0 0.0
0.0
PIPELINE_LOG 0.0 0.0
0.0
PIPELINE_VLM 0.0 0.0
0.0
WRITELOG 638209.0 7396935.0
58439.0
PSS_CHILD 0.0 0.0
0.0
EXCHANGE 64.0 0.0
0.0
XCB 0.0 0.0
0.0
DBTABLE 3.0 13844.0
0.0
EC 0.0 0.0
0.0
TEMPOBJ 0.0 0.0
0.0
XACTLOCKINFO 0.0 0.0
0.0
LOGMGR 0.0 0.0
0.0
CMEMTHREAD 2891.0 16.0
16.0
CXPACKET 51390.0 4018126.0
47608.0
PAGESUPP 1260.0 301.0
110.0
SHUTDOWN 0.0 0.0
0.0
WAITFOR 54018.0 5.4056058E+8
5.4057062E+8
CURSOR 0.0 0.0
0.0
EXECSYNC 0.0 0.0
0.0
LATCH_NL 0.0 0.0
0.0
LATCH_KP 0.0 0.0
0.0
LATCH_SH 103.0 4673.0
63.0
LATCH_UP 2.0 0.0
0.0
LATCH_EX 323585.0 3.3484998E+7
29814.0
LATCH_DT 0.0 0.0
0.0
PAGELATCH_NL 0.0 0.0
0.0
PAGELATCH_KP 0.0 0.0
0.0
PAGELATCH_SH 66041.0 388224.0
3297.0
PAGELATCH_UP 21179.0 3266.0
3127.0
PAGELATCH_EX 84432.0 67740.0
7268.0
PAGELATCH_DT 0.0 0.0
0.0
PAGEIOLATCH_NL 0.0 0.0
0.0
PAGEIOLATCH_KP 0.0 0.0
0.0
PAGEIOLATCH_SH 41689.0 642546.0
3088.0
PAGEIOLATCH_UP 12771.0 125472.0
324.0
PAGEIOLATCH_EX 3701.0 48921.0
61.0
PAGEIOLATCH_DT 0.0 0.0
0.0
TRAN_MARK_NL 0.0 0.0
0.0
TRAN_MARK_KP 0.0 0.0
0.0
TRAN_MARK_SH 0.0 0.0
0.0
TRAN_MARK_UP 0.0 0.0
0.0
TRAN_MARK_EX 0.0 0.0
0.0
TRAN_MARK_DT 0.0 0.0
0.0
NETWORKIO 319784.0 661935.0
0.0
Total 5915940.0 5.5316987E+9
1.7852595E+9This might be of use:
http://www.sqldev.net/articles/wait_types.htm
In any case that usually means you have I/O problems. You are reading too
much data for the amount of memory you have and are going to disk allot.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:48CE780A-D74D-451D-8642-922DC2788C09@.microsoft.com...
>I executed 'DBCC SQLPERF(WAITSTATS)' command. After I looked at results, I
> was quite surprised. Lots of waiting on LATCH_EX, with average wait time
> 102
> ms, and lots of waiting on CXPACKET, with average wait time 78 ms. What
> can
> be reasons of such behavior, how to remove this waits? Below is results of
> 'DBCC SQLPERF(WAITSTATS)' command.
> Wait Type Requests Wait Time
> Signal Wait Time
> -- --
> -- --
> MISCELLANEOUS 100.0 0.0
> 0.0
> LCK_M_SCH_S 22.0 50424.0
> 16.0
> LCK_M_SCH_M 10.0 1080.0
> 0.0
> LCK_M_S 2173.0 600388.0
> 677.0
> LCK_M_U 74.0 1189.0
> 16.0
> LCK_M_X 202.0 198498.0
> 20206.0
> LCK_M_IS 53.0 452577.0
> 16.0
> LCK_M_IU 0.0 0.0
> 0.0
> LCK_M_IX 28.0 20435.0
> 16.0
> LCK_M_SIU 0.0 0.0
> 0.0
> LCK_M_SIX 0.0 0.0
> 0.0
> LCK_M_UIX 0.0 0.0
> 0.0
> LCK_M_BU 0.0 0.0
> 0.0
> LCK_M_RS_S 0.0 0.0
> 0.0
> LCK_M_RS_U 0.0 0.0
> 0.0
> LCK_M_RIn_NL 0.0 0.0
> 0.0
> LCK_M_RIn_S 0.0 0.0
> 0.0
> LCK_M_RIn_U 0.0 0.0
> 0.0
> LCK_M_RIn_X 0.0 0.0
> 0.0
> LCK_M_RX_S 0.0 0.0
> 0.0
> LCK_M_RX_U 0.0 0.0
> 0.0
> LCK_M_RX_X 0.0 0.0
> 0.0
> SLEEP 544534.0 2.0353794E+8
> 2.0340557E+8
> IO_COMPLETION 266375.0 641165.0
> 671.0
> ASYNC_IO_COMPLETION 32.0 578000.0
> 0.0
> RESOURCE_SEMAPHORE 0.0 0.0
> 0.0
> DTC 0.0 0.0
> 0.0
> OLEDB 2362526.0 4.1322363E+9
> 8.3835622E+8
> FAILPOINT 0.0 0.0
> 0.0
> RESOURCE_QUEUE 1092937.0 6.0596307E+8
> 2.0275229E+8
> ASYNC_DISKPOOL_LOCK 25752.0 0.0
> 0.0
> UMS_THREAD 0.0 0.0
> 0.0
> PIPELINE_INDEX_STAT 0.0 0.0
> 0.0
> PIPELINE_LOG 0.0 0.0
> 0.0
> PIPELINE_VLM 0.0 0.0
> 0.0
> WRITELOG 638209.0 7396935.0
> 58439.0
> PSS_CHILD 0.0 0.0
> 0.0
> EXCHANGE 64.0 0.0
> 0.0
> XCB 0.0 0.0
> 0.0
> DBTABLE 3.0 13844.0
> 0.0
> EC 0.0 0.0
> 0.0
> TEMPOBJ 0.0 0.0
> 0.0
> XACTLOCKINFO 0.0 0.0
> 0.0
> LOGMGR 0.0 0.0
> 0.0
> CMEMTHREAD 2891.0 16.0
> 16.0
> CXPACKET 51390.0 4018126.0
> 47608.0
> PAGESUPP 1260.0 301.0
> 110.0
> SHUTDOWN 0.0 0.0
> 0.0
> WAITFOR 54018.0 5.4056058E+8
> 5.4057062E+8
> CURSOR 0.0 0.0
> 0.0
> EXECSYNC 0.0 0.0
> 0.0
> LATCH_NL 0.0 0.0
> 0.0
> LATCH_KP 0.0 0.0
> 0.0
> LATCH_SH 103.0 4673.0
> 63.0
> LATCH_UP 2.0 0.0
> 0.0
> LATCH_EX 323585.0 3.3484998E+7
> 29814.0
> LATCH_DT 0.0 0.0
> 0.0
> PAGELATCH_NL 0.0 0.0
> 0.0
> PAGELATCH_KP 0.0 0.0
> 0.0
> PAGELATCH_SH 66041.0 388224.0
> 3297.0
> PAGELATCH_UP 21179.0 3266.0
> 3127.0
> PAGELATCH_EX 84432.0 67740.0
> 7268.0
> PAGELATCH_DT 0.0 0.0
> 0.0
> PAGEIOLATCH_NL 0.0 0.0
> 0.0
> PAGEIOLATCH_KP 0.0 0.0
> 0.0
> PAGEIOLATCH_SH 41689.0 642546.0
> 3088.0
> PAGEIOLATCH_UP 12771.0 125472.0
> 324.0
> PAGEIOLATCH_EX 3701.0 48921.0
> 61.0
> PAGEIOLATCH_DT 0.0 0.0
> 0.0
> TRAN_MARK_NL 0.0 0.0
> 0.0
> TRAN_MARK_KP 0.0 0.0
> 0.0
> TRAN_MARK_SH 0.0 0.0
> 0.0
> TRAN_MARK_UP 0.0 0.0
> 0.0
> TRAN_MARK_EX 0.0 0.0
> 0.0
> TRAN_MARK_DT 0.0 0.0
> 0.0
> NETWORKIO 319784.0 661935.0
> 0.0
> Total 5915940.0 5.5316987E+9
> 1.7852595E+9
>|||I readed the link before post question, but I can not understand reason of
waiting on LATCH_EX and CXPACKET. It looks like LATCH_EX have no relation to
I/O problems, only PAGEIOLATCH_XX have relation to I/O. Is it right? And wha
t
is reason of waiting on CXPACKET?
"Andrew J. Kelly" wrote:

> This might be of use:
> http://www.sqldev.net/articles/wait_types.htm
> In any case that usually means you have I/O problems. You are reading too
> much data for the amount of memory you have and are going to disk allot.
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:48CE780A-D74D-451D-8642-922DC2788C09@.microsoft.com...
>
>|||Hi,
How many CPU do you have on your box?
Did you try to change max degree of parallelism setting via sp_configure.
(To start number of cpu - 1)
"andsm" wrote:
[vbcol=seagreen]
> I readed the link before post question, but I can not understand reason of
> waiting on LATCH_EX and CXPACKET. It looks like LATCH_EX have no relation
to
> I/O problems, only PAGEIOLATCH_XX have relation to I/O. Is it right? And w
hat
> is reason of waiting on CXPACKET?
> "Andrew J. Kelly" wrote:
>|||2 CPU. I think about change max degree of parallelism, but I not sure it is
possible - it can decrease performance, which is already not good. Is any
ways to estimate how it would affect performance? And even with the change,
it looks like it can, in best case, only solve CXPACKET issue. What to do
with LATCH_EX, how to decrease waiting/number of latches acquired?
"Umut Nazlica" wrote:
[vbcol=seagreen]
> Hi,
> How many CPU do you have on your box?
> Did you try to change max degree of parallelism setting via sp_configure.
> (To start number of cpu - 1)
>
> "andsm" wrote:
>|||CXPackets can be due to several factors but together with Latch_EX waits it
"usually" is due to poor I/O subsystem. If sql server is reading or writing
lots of pages to and from disk and the disks can not keep up with the
requests the processors may push off the current thread while it waits for
the I/O to be available. When using multiple processors to read a lot of
data they can get kind of out of sync with each other. That is essentially
what packet waits are. When one thread has to wait for others to complete
before it can continue on.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:EA45E977-EDDE-4A16-863F-702E7CB24D56@.microsoft.com...[vbcol=seagreen]
>I readed the link before post question, but I can not understand reason of
> waiting on LATCH_EX and CXPACKET. It looks like LATCH_EX have no relation
> to
> I/O problems, only PAGEIOLATCH_XX have relation to I/O. Is it right? And
> what
> is reason of waiting on CXPACKET?
> "Andrew J. Kelly" wrote:
>|||Hi,
We had problems with parallesim with >= 8 CPU that's why i ask how many cpu
you have. Don't think you have problem with CXPACKET.
I recommend you to read Tom Davidson article on SQLMAG (#40925). As Andrew
said i think you have io performance problems.
"andsm" wrote:
[vbcol=seagreen]
> 2 CPU. I think about change max degree of parallelism, but I not sure it i
s
> possible - it can decrease performance, which is already not good. Is any
> ways to estimate how it would affect performance? And even with the change
,
> it looks like it can, in best case, only solve CXPACKET issue. What to do
> with LATCH_EX, how to decrease waiting/number of latches acquired?
> "Umut Nazlica" wrote:
>|||In the system about 70 heap tables, out of 300 total tables. Can heap tables
be reason of high LATCH_EX?
"Andrew J. Kelly" wrote:

> CXPackets can be due to several factors but together with Latch_EX waits i
t
> "usually" is due to poor I/O subsystem. If sql server is reading or writi
ng
> lots of pages to and from disk and the disks can not keep up with the
> requests the processors may push off the current thread while it waits for
> the I/O to be available. When using multiple processors to read a lot of
> data they can get kind of out of sync with each other. That is essentiall
y
> what packet waits are. When one thread has to wait for others to complete
> before it can continue on.
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:EA45E977-EDDE-4A16-863F-702E7CB24D56@.microsoft.com...
>
>|||Heaps can certainly increase I/O for both reads and writes. In general
every table should have a clustered index. You should be able to run a
trace to see what queries are hitting what tables with a lot of I/O.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:BFE86B95-4570-4E0E-B306-8C56C70D23AF@.microsoft.com...[vbcol=seagreen]
> In the system about 70 heap tables, out of 300 total tables. Can heap
> tables
> be reason of high LATCH_EX?
> "Andrew J. Kelly" wrote:
>|||In general LATCH_EX waits are not directly related to I/O -- the
PAGEIOLATCH_XX group mentioned earlier in this thread is directly related to
I/O.
Do you have heavy concurrent insert/delete/modify activity on any of the 70
heaps? Access to several internal heap data structures are synchronized
using latches and very heavy concurrent insert/delete/modify activity on the
heap can cause contention on the latch resulting in the LATCH_EX waits you
are observing. (Note that this insert/delete/modify activity does not
necessarily result in increased I/O demands.)
If this is the case, you can convert the heavily accessed heap into a
clustered index as this eliminates some of the synchronization requirements.
However, be careful not to introduce index hot spots. For example, if you
have heavy concurrent insert activity you may not want to use an identity as
the index key because it is likely to cause a hot spot. SQL Server 2000 SP4
also contains improvements which reduce this contention, and also help
determine the cause of LATCH_EX waits. For SP4 availability see
http://support.microsoft.com/defaul...kb;en-us;290211
Santeri (Santtu) Voutilainen
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23k2fuFDRFHA.3364@.TK2MSFTNGP15.phx.gbl...
> Heaps can certainly increase I/O for both reads and writes. In general
> every table should have a clustered index. You should be able to run a
> trace to see what queries are hitting what tables with a lot of I/O.
> --
> Andrew J. Kelly SQL MVP
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:BFE86B95-4570-4E0E-B306-8C56C70D23AF@.microsoft.com...
>