performance has been decreased after migration.

Hi,

I have migrated my database from sql server 2000 to sql server 2005,

for migration i have backed up 2000 database and restored that into sql server 2005 databse.

when we are running following insert query is 50% slower in sql server 2005.

truncate table Table1

print getdate()

INSERT INTO Table1 SELECT CONVERT(INTEGER,cust_id ), store_id, MAX(Buydate), COUNT(*) FROM Table2 (NOLOCK) GROUP BY cust_id, store_id

print getdate()

How to increase the performance for the query.

Thanks in advance.

Regards

Manoj

"Standard" recommendation is to compare plans on 2000 and 2005 if you have access to both of the systems. You can use SET SHOWPLAN_ALL and STATISTICS PROFILE statements to get more information about plans and where the time is being spent. Lookup their usage in Books Online or on MSDN.

Also you can UPDATE STATISTICS on Table2 to make sure a bad (slow) plan is not being picked due to stale statistics.

As a general recommendation read "slow running queries" and other chapters as you see fit of this whitepaper:

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

HTH,
Boris.

|||

I would suggest recompiling all of your stored procedures and rebuiling all of the indexes in your database. This really helped the performance of my system after I upgraded.

Hope this helps,

Jarret

|||

Thanks Jarret for your help.