Solved

pageiolatch cxpacket_wait on SQL server 2000 on very busy times

Posted on 2010-09-23
7
528 Views
Last Modified: 2012-05-10
HI,

on busy times we have a performance issue on our SQL server.

One of our largest tools freezes. => when i look at the stats adn do a trace of the running query  i see PAGEIOLATCH_wait and cxpacket_wait and write_log at that moment.

Our hole ERP is slowing down at that moment.

It is an SQL server 2000 ENT with the AWE option not yet enabled.

=> It's a blad server (IBM HS21) where the local OS and the TEMP db are on mirrored disks and the lagere databases are on a SAN (Netapp 3020)
The database is +- 150GB and there are 8 processors To see in Windows.
I have already split up the TEMP DB in 8 pieces and in the next maintenance window i will enable the /3GB /PAE and in sql the AWE function.

=> all indexes are defragmented and stats updated.

The explanation i get on the NET for these stats are very confusing.

There seem to be 3 possible causes but which is it?

1) the server can't handle CPU paralelism and i need to swithc to:

EXEC dbo.sp_configure 'max degree of parallelism', 1;

2) there is a memory issues that causes the memory to be written to the disk and this causes a Disk bottlenet.

3 there simply is a disk bottleneck and i need to get it upgraded.
here ar my wait stats:

=> can someone tell me looking at my config and wait stats, what could be the cause?


Wait Type                        Requests      Wait Time     Signal Wait Time
-------------------------------- ------------- ------------- ----------------
MISCELLANEOUS                    9614          16            16
LCK_M_SCH_S                      25            8110          0
LCK_M_SCH_M                      23            145126        0
LCK_M_S                          257941        5.144616E+08  3549273
LCK_M_U                          104369        3.135931E+07  905839
LCK_M_X                          49367         3.325593E+07  205745
LCK_M_IS                         5112          2.510078E+08  17035
LCK_M_IU                         10            0             0
LCK_M_IX                         10432         1.469938E+08  27513
LCK_M_SIU                        0             0             0
LCK_M_SIX                        0             0             0
LCK_M_UIX                        0             0             0
LCK_M_BU                         0             0             0
LCK_M_RS_S                       0             0             0
LCK_M_RS_U                       0             0             0
LCK_M_RIn_NL                     0             0             0
LCK_M_RIn_S                      0             0             0
LCK_M_RIn_U                      0             0             0
LCK_M_RIn_X                      0             0             0
LCK_M_RX_S                       0             0             0
LCK_M_RX_U                       0             0             0
LCK_M_RX_X                       0             0             0
SLEEP                            1.015836E+07  7.267063E+08  6.114286E+08
IO_COMPLETION                    6.767902E+07  1.550455E+09  5.640959E+07
ASYNC_IO_COMPLETION              4992          39268         0
RESOURCE_SEMAPHORE               390           353580        1059
DTC                              0             0             0
OLEDB                            2.855572E+08  1.304756E+09  1.329989E+09
FAILPOINT                        0             0             0
RESOURCE_QUEUE                   2.683647E+08  1.71068E+09   7.893226E+08
ASYNC_DISKPOOL_LOCK              76555         32            0
UMS_THREAD                       0             0             0
PIPELINE_INDEX_STAT              51            235           79
PIPELINE_LOG                     0             0             0
PIPELINE_VLM                     0             0             0
WRITELOG                         2.18987E+08   9.41066E+08   3.915004E+07
PSS_CHILD                        0             0             0
EXCHANGE                         1843725       3.211856E+08  1243859
XCB                              0             0             0
DBTABLE                          6             40359         16
EC                               120           1217          203
TEMPOBJ                          0             0             0
XACTLOCKINFO                     0             0             0
LOGMGR                           0             0             0
CMEMTHREAD                       2.195103E+07  790994        712121
CXPACKET                         4.742927E+08  6.046611E+08  1.193538E+08
PAGESUPP                         1.176357E+07  7.406066E+07  3144843
SHUTDOWN                         0             0             0
WAITFOR                          0             0             0
CURSOR                           0             0             0
EXECSYNC                         13662         503693        2418
LATCH_NL                         0             0             0
LATCH_KP                         0             0             0
LATCH_SH                         762           19726         1487
LATCH_UP                         1.045899E+07  2660435       305767
LATCH_EX                         4.874097E+08  1.363994E+09  1.43757E+08
LATCH_DT                         0             0             0
PAGELATCH_NL                     0             0             0
PAGELATCH_KP                     293           15            15
PAGELATCH_SH                     2.18622E+08   1.071008E+07  2045946
PAGELATCH_UP                     1.107202E+08  1.644599E+07  4653208
PAGELATCH_EX                     2.288194E+08  7847481       3418246
PAGELATCH_DT                     0             0             0
PAGEIOLATCH_NL                   0             0             0
PAGEIOLATCH_KP                   0             0             0
PAGEIOLATCH_SH                   7.16795E+08   3.540927E+09  1.227704E+08
PAGEIOLATCH_UP                   529786        2.46937E+07   152500
PAGEIOLATCH_EX                   3.810907E+07  4.199054E+08  3287004
PAGEIOLATCH_DT                   0             0             0
TRAN_MARK_NL                     0             0             0
TRAN_MARK_KP                     0             0             0
TRAN_MARK_SH                     0             0             0
TRAN_MARK_UP                     0             0             0
TRAN_MARK_EX                     0             0             0
TRAN_MARK_DT                     0             0             0
NETWORKIO                        3.181227E+08  6.171387E+08  0
Total                            3.490718E+09  1.421688E+10  3.235856E+09

0
Comment
Question by:capsugel
  • 3
  • 2
7 Comments
 
LVL 12

Expert Comment

by:ill
ID: 33752234
I would go with 3)
or 4)
transaction conflict  ( selects and updates on same table running at one), can you monitor the transaction status a the time those locks occure?


0
 

Author Comment

by:capsugel
ID: 33752284
YEs I can,

i see consequently (runnable, pageIOlatch, runnable, cpxpacket, ...)

It is verywell possible that it is a conflict,

=> this query is executed by multiple persons at the same time, but when i look at the querys  in my tools i don't see the locking each other and other than createing temp tables, it's a select query which creates a #mytable and updates this table.

The query is very complex.

here is a part of the code:

-- Base Data
--DECLARE   #mytable  TABLE(
CREATE TABLE #mytable (
      -- link fields
      [inExpectShip_ObjectID]            int,
      [coOrderSumry]                  int,
      [coOrderDeliv]                  int,
      [OrderSite]                  varchar(4),
      [OrderNr]                  varchar(35),
      [OrderLineNr]                  varchar(5),
      [OrderDelNr]                  varchar(5),
      [OByTP]                        varchar(20),                        -- TP code
      -- report fields
      [OutShip]                  varchar(20),
      [ExpShipDocNum]                  varchar(50),
      [ExpShipDate]                  datetime,
      [ShipDate]                  datetime,
      [DeliveryDate]                  datetime,
      [DNS_before_date]            datetime,
      [COAorCOC]                  varchar(3),
      [ResvPrmQty]                  float,
      [ExpPrimQty]                  float,
      [OByName]                  varchar(50),
      [Invoice to Country]            varchar(30),
      [Ship to Country]            varchar(30),
      [aoIEresp]                  varchar(20),
      [CSR]                        varchar(20),
      [Location]                  varchar(20),
      [hold_order]                  smallint,
      [hold_delivery]                  smallint,
      [JIT]                        varchar(10),
      [CheckShipDate]                  smallint,
      [Assembled]                  smallint,
      [OrdCat]                  varchar(30),

      [ScheduleType]                  varchar(128),
      [OrderStatus]                  smallint,
      [group_number]                  int default -1,

      [Priority]                  int,
      
      [TPKnownAs]                  varchar(30),
      [FreightMode]                  varchar(30),
      [OD_ObjectID]            int,
      [ShipToTPObjectID]      int,
      [InvcToTPObjectID]      int
)

INSERT INTO #mytable(OutShip, inExpectShip_ObjectID, ExpShipDocNum, OrderSite, OrderNr, OrderLineNr, OrderDelNr, ExpShipDate, DNS_before_date,
                  COAorCOC, ResvPrmQty, ExpPrimQty, OByName, Assembled,
              coOrderSumry, coOrderDeliv, OByTP, ShipDate, DeliveryDate, OrdCat, ScheduleType, OrderStatus, Priority, TPKnownAs, FreightMode,OD_ObjectID,      od.ShipToTPObjectID,      od.InvcToTPObjectID)
SELECT  OutShip.OutShip,
      ExpShip.ObjectID,
      ExpShip.ExpShipDocNum + '-' + ExpShip.ExpShipLine + '-' + ExpShip.ExpShipDelivery,
      ExpShip.OrderSite,
      ExpShip.ExpShipDocNum,
      ExpShip.ExpShipLine,
      ExpShip.ExpShipDelivery,
      OutShip.ExpShipDate                                as 'Exp ship date',            -- uit OS
      OutShip_addon.aoDNSbefor                        as 'DNS before date',            -- DNS before date uit OS
      --CASE WHEN OutShip_addon.aoCOAcusto = 1 THEN 'COA'
      --     WHEN OutShip_addon.aoCOCcusto = 1 THEN 'COC'
      --     ELSE ''
      --END                                           as 'COAorCOC',
      
      (SELECT top 1 ISNULL(LEFT(Alphanumericvalue,3),'COA')
      FROM Protean..coSCICharact ci
      WHERE charact = 'CUSTOMER SPECS'
      AND      ci.parentobjectid = od.objectid
      AND ci.ParentClassID = 11345 AND ci.CollectionID = 1) as 'COAorCOC',
      
      ExpShip.ResvPrmQty                              as 'Available quantity',          
      ExpShip.ExpPrimQty                              as 'Primary quantity',        
      ExpShip.CustomerName                              as 'Customer name',
      OutShip.AssembChecked,
      os.ObjectID,
      od.ObjectID,
      os.OByTP,
      ISNULL(DATEADD(ss,od.LeadTme * (-1),od.UDFDate1), od.PromShipDate),
      ISNULL(od.UDFDate1, od.PromDelivDate),
      os.OrdCat,
      CASE WHEN c.CommentName = @comment_pick THEN 'PICK'
           WHEN c.CommentName = @comment_24h  THEN '24 HOURS'
           WHEN os.SellLOB = @spare_parts_lob THEN os.SellLOB
           ELSE NULL
           END,
      od.DlvStat
,      od.DevPrty      as 'Priority'
,      tp.KnownAsName      as 'TPKnownAs'
,      ISNULL(od.FrghtMode,'')       as 'FreightMode'
,      od.ObjectID
,      od.ShipToTPObjectID
,      od.InvcToTPObjectID
FROM       Protean..inOutboundShipm            OutShip(NOLOCK)
       INNER JOIN Protean..inVehiclePos vp (NOLOCK)
             ON vp.ParentObjectID                   =             outship.ObjectID
            AND vp.ParentClassID                   =             11444
            AND vp.CollectionID                   =             1
      INNER JOIN Protean..inShipLine sl (NOLOCK)
             ON sl.ParentObjectID                   =             vp.ObjectID
            AND sl.ParentClassID                   =             11439
            AND sl.CollectionID                   =             1
      INNER JOIN Protean..inExpectShip ExpShip (NOLOCK)
             ON ExpShip.ObjectID                   =             sl.ExpShipUKObjectID
      INNER JOIN Protean..inReservation res(NOLOCK)
             ON res.ExpShipObjectID             =             sl.ExpShipUKObjectID
      INNER JOIN @locations loc
             ON loc.location                  =            res.InvUnitLocation
      LEFT JOIN  Protean..AOQAOS OutShip_addon
             ON OutShip_addon.ParentObjectID       =            OutShip.ObjectID
            AND OutShip_addon.ParentClassID       =             11444  
      INNER JOIN Protean..coOrderSumry os(NOLOCK)
             ON       os.OrderSite                  =            ExpShip.OrderSite
            AND      os.OrderOrdName                  =            ExpShip.ExpShipDocNum
      INNER JOIN Protean..coOrderLineItem oli(NOLOCK)
             ON       oli.ParentObjectID            =            os.ObjectID
            AND      oli.ParentClassID             =             11325
            AND       oli.CollectionID             =             1
            AND      oli.SequenceNumber            =            ExpShip.ExpShipLine
      INNER JOIN Protean..coOrderDeliv od(NOLOCK)
             ON      od.ParentObjectID            =            oli.ObjectID
            AND      od.ParentClassID             =             11341
            AND       od.CollectionID             =             1
            AND      od.SequenceNumber            =            ExpShip.ExpShipDelivery
      LEFT JOIN Protean..coCommentSub c (NOLOCK)
             ON c.ParentObjectID                   =             od.ObjectID
            AND c.ParentClassID                   =             11345
            AND c.CollectionID                   =             1
            AND c.CommentName                  IN            (@comment_24h, @comment_pick)

      INNER JOIN Protean..fdTradingPartne tp(NOLOCK)
         ON tp.ObjectID = os.OByTPObjectID

WHERE        (ExpShip.ExpShipSite = @site or @site = '')
AND      ISNULL(DATEADD(ss,od.LeadTme * (-1),od.UDFDate1), od.PromShipDate) between @ShipDateFrom and @ShipDateTo
AND      (OutShip.Status = @status OR @status = '')
AND      OutShip.Status in (0,1,2)

--

0
 
LVL 12

Accepted Solution

by:
ill earned 500 total points
ID: 33752377
As the query you posted uses #temp tables, there should be no locks.
What is averagerowcount of #mytable ?
How many of these tables exists simultaneously?

These tables are created in tempdb, which is on mirrored disk. Fast SAN is not helping in this case.
Please check diskQueue length on tempdb mirror and compare to disk queue length on SAN.
Perhaps moving tempdb away from mirror can help.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:capsugel
ID: 34733009
dfhfd
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34734606
There is simply no reason to close this question that way.  Please give a valid explanation.
0
 

Author Comment

by:capsugel
ID: 36143630
this question was solved, as this query is not used by our company nymore
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now