Queries running slower in SQL 2008 than in SQL 2000

Question

Queries running slower in SQL 2008 than in SQL 2000

Frank Carrelli

SSC-Addicted

Points: 460
More actions
April 30, 2009 at 10:12 am

#209072

We are in the process of migrating from SQL 2000 to SQL 2008. We encountered a few queries that run faster in the 2000 database than in 20008.
I found many blogs online of other users complaining of this issue. None of the blogs I read had any resolutions however. Usually the answers given to the posts were to rebuild the indexes and the statistics. We did all of that and still have the same problem.
One query in question has many sub-queries within the select statement. I comment out some of the sub-queries and there is huge performance gain.
I am digging into the Query Plan and trying to tune the query but I will hate having to do this for every query statement in question.
Is there something I am missing that causes this issue when migrating from 2000 to 2008?
Thanks in advance...

Viewing 15 posts - 1 through 15 (of 23 total)

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

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Does the execution plan look solid and does the indexing etc look good. It may be that the optimizer can't find a good plan based on the indexes and stats that are in place whereas it did find one in 2000. Doesn't make 2008 bad just different and we need to be prepared for that. The changes / improvements in the optimizer will make things better for the great majority of the queries but you may need to help it out with some as well.

So, I would certainly look at the plan and make sure that it looks sound. If you feel up to it save the execution plan out and post it up for others to look at.

I would also be interested to know if your servers are the same configuration from the hardware perspective, i.e. CPU, memory, disk.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Lowell SSC Guru Points: 323518 More actions · Answer 2

this is a common issue after upgrading;

you've got to update statistics and rebuild indexes after upgrading or restoring a database that came from a previous version in order to address this kind of old performance vs new performance issue;

the upgrade doesn't do the stats and indexes thing for you, so you've got to make it part of your routine .

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!

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Lowell (4/30/2009)
this is a common issue after upgrading;
you've got to update statistics and rebuild indexes after upgrading or restoring a database that came from a previous version in order to address this kind of old performance vs new performance issue;
the upgrade doesn't do the stats and indexes thing for you, so you've got to make it part of your routine .

The OP stated he did that. I watched for that too. 😛

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

David Benoit (4/30/2009)
If you feel up to it save the execution plan out and post it up for others to look at.

Absolutely.

There are cases where the 2005/2008 optimiser comes up with very bad plans where the 2000 optimiser came up with very good ones. They are cases in the minimum, but they do exist.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 5

doh i missed he said he did that already;

only advice i have left is one post i read said that rather than update statistics, they dropped and recreated the statistics, which fixed the issue for them, but that was an restore from SQL7 db toa 2005.

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!

Frank Carrelli SSC-Addicted Points: 460 More actions · Answer 6

Thanks for the fast replies.

The SQL 2008 server is a major upgrade from the 2000 Server. It is a Dell PowerEdge 2950 2 QuadCode X5450 3 Ghz Processors with 16GB of RAM running Win 2008. I do not think the server is the issue.

SQL 2008 however is a 64bit version, but again I do not think that should matter.

The Query Plan does look solid except for two Key Lookups on two different sub-select statements. They are not using the indexes we originally created for those select statements way back when so that does tend to suggest the optimizer is picking the wrong indexes. I am reluctant to specify the index so I am tweaking the indexes and queries so it will select the correct index.

I am also attempting to drop all the Statistics and recreated as mentioned earlier. I will have to do that for the entire database as this trouble query of mine touches quite a number of tables.

I will post my progress of this issue as I determine what works and what does not.

Thanks again for the help.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Frank Carrelli (4/30/2009)
The Query Plan does look solid except for two Key Lookups on two different sub-select statements. They are not using the indexes we originally created for those select statements way back when so that does tend to suggest the optimizer is picking the wrong indexes. I am reluctant to specify the index so I am tweaking the indexes and queries so it will select the correct index.

Can you post the query and execution plan (saved as a.sqlplan file, zipped and attached)?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 8

Frank Carrelli (4/30/2009)
The Query Plan does look solid except for two Key Lookups on two different sub-select statements. They are not using the indexes we originally created for those select statements way back when so that does tend to suggest the optimizer is picking the wrong indexes. I am reluctant to specify the index so I am tweaking the indexes and queries so it will select the correct index.

That scenario can cause problems and while it may just appear to be a wrong index selection it can alter the order in which tables are read, sometimes introducing significant reads from one or more tables. So, if you still have the 2000 instance up with similar data I would look at the STATISTICS IO output as well as posting the plan as Gail suggested again. The output of your reads might show you something as well.

I wouldn't be too surprised if you get the indexes straightened out that the plan resolves itself and you start getting the results you were expecting.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Frank Carrelli SSC-Addicted Points: 460 More actions · Answer 9

I attached the Query Plan and the Query itself. This is a very large Query Plan and it is hard to read.

This query had many previous versions with each causing timeouts, dead locks, and undo stress load on the server. The final version attached here runs in a couple of seconds on the live SQL 2000 with 50 users and in 10 seconds in SQL 2008 on a souped up server with 0 users. 8 seconds does not sound like much but my salesreps do not want to here that.

FYI: I dropped all the Statistics and rebuilt all the indexes, and recreated the Statistics to no avail. I am planning on moving the sub queries to their own functions which would be called from the main query. We have a similar Sales History Query written that way and it is much faster on the SQL 2008 than it is on the SQL 2000 database.

Again, thanks for the help.

dkhattar SSCrazy Points: 2037 More actions · Answer 10

Hi,

Could you view your memory ussage :

Procedure Cache and buffer cache.

Quite often on 64 bit version, procedure cache grow (query syscacheobjects) and it cause data cache to be cleared, which in turn causes disk I/0 and performace issues.

Not very sure but worth looking at it.

Deepak

Frank Carrelli SSC-Addicted Points: 460 More actions · Answer 11

I found my issue that was causing my slow running queries. It appears the Coalesce statements we use to encapsulate the sub-queries would cause the Optimizer to run the query twice. In the execution plan it displays the query twice as a nested inner loop join so I am guessing it is running twice. I replace the coalesce statement with a isnull and my query executes in one second compared to 12 seconds and the nested inner loop join disappears from the execution plan.

These sub-queries were all part of the Main Query's Select statement and not used in the where clause of the query as I know using colaeseces within the where clause does cause indexing problems.

Do not know why coalesce does this in SQL2008 and not SQL2000.

Anyway thanks for all the help.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 12

Frank Carrelli (5/4/2009)
I found my issue that was causing my slow running queries. It appears the Coalesce statements...

How serendipitous... I've recently made that same discovery with doing things like concatenation in 2k5.

BWAA-HAA!!! So much for ANSI compliant code! 😉 I'm starting to think that "ANSI" is an abbreviation for "ANother Serious Impediment" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

christophe.mulotti Valued Member Points: 52 More actions · Answer 13

Jeff Moden (5/4/2009)
Frank Carrelli (5/4/2009)
I found my issue that was causing my slow running queries. It appears the Coalesce statements...
How serendipitous... I've recently made that same discovery with doing things like concatenation in 2k5.
BWAA-HAA!!! So much for ANSI compliant code! 😉 I'm starting to think that "ANSI" is an abbreviation for "ANother Serious Impediment" 😛

Hi,

I'm stuck with a similar problem, but my case seems to be more related to the concatenation or to the NOT IN or NOT EXISTS statement....

Can you explain me what you figured out about theses points (slow queries with concatenation in 2k5) ?

thanks !

Frank Carrelli SSC-Addicted Points: 460 More actions · Answer 14

Jeff,

How I determined my problem was by viewing the execution plan for the queries that were giving me the problems. In the Execution plan it displayed everything twice. The Coalesce statements were causing the query to run a second time based on a boolean type of statement within the Execution Plan. By replacing the Coalesce with ISNULL caused the boolean statement phrase to disappear from the plan and thus making the query run one time only.

I would suggest you view your plan and tweak the queries accordingly. Try removing or rephrasing the Exists / Not IN statements and see how it affects the plan. Hope that helps.