asked on

PAGEIOLATCH_EX suspended status problem

will greatly appreciate your help in fixing this PAGEIOLATCH_EX suspended status problem. below is the problematic part of the stored procedure.

the process ran optimally without an index and, perhaps, after adding an index it's slow to a crawl.

please help! thanks!

DECLARE @SQLStr nvarchar(1000)
DECLARE @IntNumber Int
DECLARE @Knum varchar(22)

SET @intNumber = 0
WHILE @intNumber < 41
	BEGIN
	
	if LEN(@intNumber)=1
		SET @Knum = '0' + CONVERT(VARCHAR(5),@intNumber)
	else
		SET @Knum = CONVERT(VARCHAR(5),@intNumber)	
	
	SET @SQLStr = 'UPDATE TMP2.dbo.tblQuery2
	SET TMP2.dbo.tblQuery2.SUBFSI' + CONVERT(VARCHAR(5),@intNumber) + 
	' = TMP1.dbo.tblQuery1.KST'+@Knum +
	' FROM TMP2.dbo.tblQuery2 LEFT JOIN TMP1.dbo.tblQuery1 
	ON (TMP2.dbo.tblQuery2.FSI = TMP1.dbo.tblQuery1.FSI_KST' + @Knum +') 
	AND (TMP2.dbo.tblQuery2.Year = TMP1.dbo.tblQuery1.Year) 
	AND (TMP2.dbo.tblQuery2.KEART = TMP1.dbo.tblQuery1.KEART)  
	AND (TMP2.dbo.tblQuery2.Month = TMP1.dbo.tblQuery1.Month) 
	AND (TMP2.dbo.tblQuery2.SKU = TMP1.dbo.tblQuery1.SKU)'
	
	EXEC (@sqlstr)
	PRINT 'KST' + @Knum + ' done.'	
	SET @intNumber = @intNumber + 1

END

Open in new window

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ironpen45

ASKER

thanks. the additional indexes seemed to help a bit. we also:

1. moved the database to another drive (outside of the native C:\)
2. deleted & recreated the database (it's just a temporary storage to handle just under 10GB since this is SS Express, with just 1 table and a bunch of stored procedures)