DeMyu
asked on
Getting Wait type: Pageiolatch_sh and CXPACKET when I attempt to UPDATE a table with 200 million records. (500 Points)
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),@MinC ount)+ ' AND ' +convert(varchar(30),@MaxC ount)
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.
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),@MinC
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
agree