sql login permissions

Friday, March 30, 2012

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 what
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...
> >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
> >
>
>|||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:
> 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:
> > 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...
> > >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
> > >
> >
> >
> >|||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:
> 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:
> > 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:
> >
> > > 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...
> > > >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
> > > >
> > >
> > >
> > >|||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...
>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:
>> 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...
>> >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
>> >
>>|||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:
> 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:
> > 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:
> >
> > > 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:
> > >
> > > > 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...
> > > > >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
> > > > >
> > > >
> > > >
> > > >|||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 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...
> >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:
> >
> >> 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...
> >> >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
> >> >
> >>
> >>
> >>
>
>|||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...
> 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
>> 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...
>> >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:
>> >
>> >> 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...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>|||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/default.aspx?scid=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...
>> 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
>> 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...
>> >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:
>> >
>> >> 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...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>|||<g>. Even though I was stating Latch I was thinking PageIOLatch. Santeri
is correct in that regular LatchEX's may be attributed to lots of Heap or
Blob access.
--
Andrew J. Kelly SQL MVP
"Santeri Voutilainen [MSFT]" <santeriv@.online.microsoft.com> wrote in
message news:%23onKG5DRFHA.2080@.TK2MSFTNGP10.phx.gbl...
> 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/default.aspx?scid=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...
>> 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 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...
>> >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:
>> >
>> >> 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...
>> >> >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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>sql

Long wait times for simple SQL on 2005

I have an existing application deployed on many SQL 2000 servers running without issue. We installed SQL 2005 (8 way DualCore) and the most simple trivial SQL statements are taking an extreme amount of time (e.g. an identical query in 2000 took 10 ms is taking a couple of minutes in 2005). The data in the tables being queried are very small (low row count < 400; and low row size < 1k).

When profiling 2005 we see the connection going into a status of 'suspended' with a wait type of 'ASYNC_NETWORK_IO'. There just simply is not enough data being retrieved for a lag to occur returning data to the client. Has anyone seen anything like this? Any thoughts?

I haven't seen anything relating to NETWORK_IO, one gotcha is not doing index rebuilds. http://sqlblogcasts.com/blogs/simons/archive/2006/10/20/SQL-2005-UpgradeRebuild-your-indexes-.aspx

Can you include more details on the queries.

If you run the queries in SSMS do they still take a long time?

How many users are using the server? Is there server doing anything else?

|||

This is a new client of ours (installing our web app, which, has been in production for a few years and we have no issues in house on our 2005 or 2000 SQL boxes) so there are no current active users (save the one that I was using to test with); so the current activity on the server is virtually nill. This is one of our default databases (again, virtually no data except for so default info in the one table I am querying) - all indexes are up to date.

The table has simple varchar columns and two text columns (the data in the columns vary from 100 bytes to 10k [the high end]. Here is teh defintion of the table:

Table [REPORT_DEF]
[REPORT_GU] [uniqueidentifier] NOT NULL,
[NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAMESPACE] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NUMBER] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PAGE_ORIENTATION] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OWNER_TYPE] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORGANIZATION_GU] [uniqueidentifier] NULL,
[XML_DEF] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CLASS_NAME] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSEMBLY] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMPANY_GU] [uniqueidentifier] NULL,
[LOCATION] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ICON] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TITLE] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SHORT_TITLE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOCALE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[REPORT_VIEW_TYPE] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MODULE] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DEPLOY_VERSION] [numeric](7, 0) NOT NULL DEFAULT ((0)),
[MAIN_REPORT_RPX] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASSOCIATED_REPORT_GU] [uniqueidentifier] NULL,
CONSTRAINT PRIMARY KEY CLUSTERED - Column: REPORT_GU
CONSTRAINT UNIQUE NONCLUSTERED - columns: NAMESPACE and NAME

The query that is being run is as follows:

SELECT R0.[COMPANY_GU],R0.[NUMBER],R0.[NAME],R0.[ASSOCIATED_REPORT_GU],R0.[MAIN_REPORT_RPX],
R0.[REPORT_GU],R0.[PAGE_ORIENTATION],R0.[SHORT_TITLE],R0.[ASSEMBLY],R0.[MODULE],R0.[TITLE],
R0.[DEPLOY_VERSION],R0.[NAMESPACE],R0.[LOCATION],R0.[ORGANIZATION_GU],R0.[ICON],R0.[OWNER_TYPE],
R0.[XML_DEF],R0.[LOCALE],R0.[REPORT_VIEW_TYPE],R0.[CLASS_NAME]
FROM [REPORT_DEF] R0

ORDER BY R0.[NAMESPACE] ASC,R0.[NAME] ASC,R0.[REPORT_GU] ASC

Again, when I execute this query on the DB server (via Mgmt Studio) it executes in about 80 ms (give or take a couple ms). When run on a machine on the same network (gigabit backbone, the only two machines on this network) the execution time jumps to 75 seconds (give or take 5 seconds).

Thoughts?

|||

Can you run profiler and capture the XML plans, and then compare them, to check they are getting the same plan.

Also be aware that by default SSMS uses the in memory data provider which will give performance improvements compared with running on the client.

How much data is being returned?

Are you using SSMS on the client as well?

|||

Simon,

Thanks for your thoughts on this one. I broke down and called MS and after about 6 hours we found that there was a network issue. Both SQL and the web server machines have gigabit NICs but the switch that the client had the two machines plugged into is a 10/100 switch...with the NICs set to autosensing basic network traffic was extremely delayed (tested via explorer copying a 70MB file to the SQL server occured in 8 seconds, from the SQL server to the web sever was taking about 35 minutes). Anyway, chalk this one up to experience and something to look for next time.

Thanks for your help!

Friday, March 30, 2012

Long waits on LATCH_EX and CXPACKET

Long waits on LATCH_EX and CXPACKET

Long wait times for simple SQL on 2005

sql login permissions

Blog Archive

About Me