Query runs forever following database migration (2005 - 2008)

Question

Query runs forever following database migration (2005 - 2008)

DBANewbie

Hall of Fame

Points: 3095
More actions
September 6, 2012 at 6:45 am

#261285

Hi,
I recently migrated a database which resided on our old 2005 server to our newer 2008 production server, everything went perfectly well...
...except that one of the stored procedures, which used to run in under a minute now runs constantly without ever completing.
I have tried running the code within the SP directly within a query window within SSMS and the same thing happens. Runs in under a minute on our 2005 instance but never completes on our 2008 instance (exact same source data).
I also tried selecting the two sets of data into temp tables and then joining them together just to see if it would work and it did, the results came back instantly...this made me even more confused!
------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

after an upgrade to a higher version, it's pretty much manditory to rebuild all your statistics;

the statistics in place are used differently by teh updated query engine, so if you skip that step, you can see dramatic performance differencesuntil the statistics are rebuilt.

http://www.sqlservercentral.com/search/?q=slower+after+upgrade

DECLARE @Exec VARCHAR(MAX)

SELECT @Exec = ''

SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 2

Usually "the query runs forever" is db speak for "an index is missing". So I think to diagnose this, we need to know how your database was 'migrated'.

Did you perform a backup and restore? Did you detach the database and reattach? Or did you copy tables using the Import/Export tool?

If you used the last option, I'm almost certain that indexes would not have been created. Usually when I move a database, I perform a backup and restore the backup file to the new system. I then disable access to the original database so we are sure no applications are updating the old system while everybody else is updating the new system.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

My experience has been the same as Ryan's... Indexes not getting moved into prod and they would not be much help without rebuilding your stats as previously mentioned.

Take a look at Considerations for Upgrading the Database Engine (SQL 2008). Specifically the section: After Upgrading the Database Engine

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

DBANewbie Hall of Fame Points: 3095 More actions · Answer 4

The issue was a result of parameter sniffing, thanks for all your suggestions.