Solved

pageiolatch cxpacket_wait on SQL server 2000 on very busy times

Posted on 2010-09-23
7
534 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

785 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