Solved

Getting Wait type: Pageiolatch_sh and CXPACKET when I attempt to UPDATE a table with 200 million records. (500 Points)

Posted on 2004-09-01
5
1,958 Views
Last Modified: 2012-06-21
I have a table that has about 200 million records in a database that is about 300 GB. The database is on a server with 4 CPUs.
I also have a Stored Procedure that is to clean the data in one of the columns of the table. When I run the SP shown below via Query Analyzer I get four processes with the same spid in current activity with waits as shown below and the processes go into the "sleeping" mode:

Wait time: 235
Wait type: Pageiolatch_sh
Wait resource: 8:4:2693304
CPU: 189938
Physical I/O: 682059
Memory Usage: 0

I also get the CXPACKET wait type with a wait time of 317016.
*********************************************************************************

Below is my update Stored Procedure:

CREATE PROCEDURE dbo.usp_CleanMyLog
AS
BEGIN
SET NOCOUNT ON
DECLARE @MinCount int,@MinLogID int, @MaxCount int, @MaxLogID int

SELECT @MinLogID = MIN(tblMy_Log_ID), @MaxLogID = MAX(tblMy_Log_ID) FROM dbo.tblMy_Log

--SELECT @MinLogID As MinLogID, @MaxLogID As MaxLogID

SELECT @MinCount = @MinLogID , @MaxCount = @MinLogID + 100000

WHILE @MaxCount < @MaxLogID
BEGIN

UPDATE tblMy_Log
SET Description = CASE
WHEN PATINDEX('Name Changed From%',Description)<> 0 THEN 'Name Changed'
WHEN PATINDEX('Social Security Number Changed From%',Description)<> 0 THEN 'Social Security Number Changed'
WHEN PATINDEX('Date of Birth Changed From%',Description)<> 0 THEN 'Date of Birth Changed'
WHEN PATINDEX('Address Changed From%',Description)<> 0 THEN 'Address Changed'
WHEN PATINDEX('Home Phone Number Changed From%',Description)<> 0 THEN 'Home Phone Number Changed'
WHEN PATINDEX('Work Phone Number Changed From%',Description)<> 0 THEN 'Work Phone Number Changed'
WHEN PATINDEX('Fax Phone Number Changed From%',Description)<> 0 THEN 'Fax Phone Number Changed'
WHEN PATINDEX('Email Address Changed From%',Description)<> 0 THEN 'Email Address Changed'
ELSE Description
END
WHERE Activity_Type = 'UPDATE: Customer' AND tblMy_Log_ID BETWEEN @MinCount AND @MaxCount

PRINT 'Activity with Log_ID BETWEEN ' +convert(varchar(30),@MinCount)+ '  AND ' +convert(varchar(30),@MaxCount)

      SELECT @MinCount = @MaxCount + 1 , @MaxCount = @MaxCount + 100000

      IF @MaxCount >= @MaxLogID
            SELECT @MaxCount = @MaxLogID

END
SET NOCOUNT OFF
END
GO

********************************************************************
I have done the following:

sp_configure 'show advanced option', 1

sp_configure 'max degree of parallelism', 1 to disable parallelism but this did not help

What can I do to prevent the waits? Recommendations to the improvement of my SP is welcome.
0
Comment
Question by:DeMyu
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
ShogunWade earned 250 total points
ID: 11951835
Not a lot but vastly scaling up your kit.   It is waiting for a light weight lock.
depending on other tasks, you could TABLOCK the table which reduces the numer of writes and reads required from syslock table.     often vastly improving performance.    but of course you will then block everything else wanting acess to this table.
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
ID: 11955374
CXpacket usually comes from parallel plans--one part of your task is waiting on the results from another thread in the task.....I think the above waits are pretty normal for a large update with parallelism.

You might make sure you have the LOGS and the data files on different DISKS/RAID Groups and I would check windows Perfmon and see what your IO waits look like--IO waits are probably your biggest issue.....

Is your update querying using an index on tblmy_log_id?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12158479
agree
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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