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

ironpen45Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lcohanConnect With a Mentor Database AnalystCommented:
Not quite sure what you are trying to do in the UPDATE but here's some advice from SQL performance point of view:

do not use dynamic sql unless you have NO other choice
try use sp_execute sql instead of just EXEC for dynamic SQL


"the process ran optimally without an index and, perhaps, after adding an index it's slow to a crawl."
wrong especialy you perfrom an UPDATE...FROM so an index will help the update unless you don't add a clustered on a column that you update.

after adding index please run statements below - actualy you can run them anyway and check the execution plan/time after you did that.

USE TMP2
GO
UPDATE STATISTICS tblQuery2;
exec sp_recompile tblQuery2;
GO

USE TMP1
GO
UPDATE STATISTICS tblQuery1;
exec sp_recompile tblQuery1;
GO
0
 
ironpen45Author Commented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.