Solved

pageiolatch cxpacket_wait on SQL server 2000 on very busy times

Posted on 2010-09-23
7
531 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Exceptions 3 37
grouping logic 6 46
Counting connections to SQL Server through C# 3 27
SQL Pivot add row totals 2 10
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

18 Experts available now in Live!

Get 1:1 Help Now