Solved

PAGEIOLATCH_EX suspended status problem

Posted on 2011-09-02
2
1,184 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:ironpen45
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 36474557
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
 

Author Closing Comment

by:ironpen45
ID: 36495890
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

708 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

14 Experts available now in Live!

Get 1:1 Help Now