Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PAGEIOLATCH_EX suspended status problem

Posted on 2011-09-02
2
Medium Priority
?
1,310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 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