Solved

PAGEIOLATCH_EX suspended status problem

Posted on 2011-09-02
2
1,191 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 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

20 Experts available now in Live!

Get 1:1 Help Now