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,951 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

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
Optimizing a query 3 34
SQL Exceptions 3 38
How do I Start SQL Management Studio 2012 17 19
SQL Query with WHERE clause for an entire day 5 16
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
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.

920 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