slowness in SQL Query after db upgraded to 2008

We have recently upgraded our databases from SQL 2005 to SQL Server 2008 in Test and Production environments (2 different machines). Now We are experiencing 3 times slowness on retrieving data from a simple SQL statement "select * from TableName" in PROD compare to Test (when PROD is on better hardware). Response time of same query (same db) in Test is 3 times better than Production. Can someone advise what are the possible reasons? Anything we need to check (or missed) at the db (or server) level in Production)? FYI- we rebuild indexes daily and run defrag process on weekends.

Thanks for your help! ,

asked Aug 21 '12 at 02:49 PM in Default

KaluCharanMohapatro
0 ● 1 ● 1

Thanks for your response,

It is a Physical Machine, we had SQL 2005 and it is side by side upgrade to SQL Server 2008. We have SQL 2008 in Test environment and it gives more firster result than our production.

Aug 23 '12 at 02:27 PM kalu.charan

(comments are locked)

6 answers:

I have a few questions

Did you manually update all of the stats?
Also, what other post upgrade tasks did you perform?
Have you need able to analyse the execution plans?
Did you benchmark the IO subsystem before the upgrade?
How did your test migration perform?

answered Aug 21 '12 at 03:59 PM

sp_lock
8.1k ● 20 ● 26 ● 29

(comments are locked)

Just a few thoughts from my side: after moving to a new server run DBCC updateusage (yourdatabase) with count_rows, run a checkdb (just to be sure), index maintenance, stats etc pp

so that's all the basic stuff

But beside that: is it still a physical maschine or a virtualized system and the servers are in different resource groups or some kind of over-commitment? That caused me some pain in the past until I have noticed.

Was it an inplace upgrade, side-by-side installation or also a new server? I'll ask these questions just to narrow down the possibilities.

Regards Dirk

answered Aug 22 '12 at 08:08 PM

DirkHondong
1.3k ● 7 ● 14 ● 17

Thanks Dirk,

It is a physical server. We had SQL Server 2005 and we upgrade it to SQL Server 2008 so i think it is side by side upgrade.

Aug 23 '12 at 02:29 PM kalu.charan

(comments are locked)

Is the system migrated from 32 to 64? Have you configured the tempdb, SQL Server memory, CPU Affinity, and storage properly(including RAID, DB and Log Files, auto growth)? Did you checked the performance problem is not because of missing plans? As you are in new instance, you may observe excessive compilation because of missing procedure and query plans. So SQL Server spends enough time to create the plan and reserve for later user. Also, is your statistics are set to Automatic update? If auto update statistics are synchronous (the default if auto update enabled), SQL Server first update the stats if they are outdated before executing the query, turn them asynchronous or update the stats manually.

Please share the upgrade method you followed...

answered Aug 21 '12 at 04:17 PM

Cyborg
10.1k ● 29 ● 39 ● 44

(comments are locked)

For such a simple query, both execution plans should be identical. Start there and validate that. Assuming they are the same, then it almost has to be a configuration issue. Check the basic structures, tables, indexes, etc., to be sure they're the same (although if they were different, it would certainly show up in the execution plan).

I've seen the occasional slow query. But for a very basic query to be this slow, I'm fairly certain the difference is a large configuration issue, not a difference in optimization between the two versions of SQL Server.

answered Aug 21 '12 at 05:46 PM

Grant Fritchey ♦♦
62.2k ● 12 ● 20 ● 66

(comments are locked)

I'm having a similar issue again with no solution. If you find a solution please discuss because I'm looking for one as well. Sorry to high jack your thread.

answered Aug 22 '12 at 08:34 PM