x

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! ,
more ▼

asked Aug 21, 2012 at 02:49 PM in Default

KaluCharanMohapatro gravatar image

KaluCharanMohapatro
0 1 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, 2012 at 02:27 PM kalu.charan
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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?
more ▼

answered Aug 21, 2012 at 03:59 PM

sp_lock gravatar image

sp_lock
9.3k 25 28 31

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 22, 2012 at 08:08 PM

DirkHondong gravatar image

DirkHondong
1.3k 15 17 19

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, 2012 at 02:29 PM kalu.charan
(comments are locked)
10|1200 characters needed characters left

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...
more ▼

answered Aug 21, 2012 at 04:17 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 21, 2012 at 05:46 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Aug 22, 2012 at 08:34 PM

JGBW12 gravatar image

JGBW12
0

You can't really hijack questions on Ask. The only question is the one at the top. Can you supply more details in a question of your own?
Aug 22, 2012 at 09:32 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x739

asked: Aug 21, 2012 at 02:49 PM

Seen: 736 times

Last Updated: Aug 23, 2012 at 03:26 PM