Poor query performance post SQL 2008 upgrade

Question

Poor query performance post SQL 2008 upgrade

Viewing 9 posts - 31 through 38 (of 38 total)

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

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

Actually I am looking for the reason why SQL 2000 would work ok and SQL 2008 not. There is hundreds of views queries and SP's in the database that comes standard with the app that I do not want to change not realy allowed. If I understand you right you are off the opinion that there is nothing wrong with the database but there is problems with the query and indexes and how SQL 2008 is using them to create the execplan? In that case I would have to say that the software (PeopleSoft) is not compatible with SQL 2008 and most probibly SQL 2005 so upgrading is out of the question at this moment.

Thanks for all the help

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

Sorry, didn't read all the posts, but did you update indexes and statistics after the update?

OomBoom Hall of Fame Points: 3481 More actions · Answer 3

Hi Steve

Yes I did. I rebuild all indexes and updated all stats with fullscan. The difference I see is that SQL 2008 scans the Index on the bigest table first and then goes over to the tables that is returning nothing. Sql 2000 does the small queries first and then joins to the big table, this uses a index seek which is why it comes down from 8min to 1 sec. In short SQl 2008 is using the wrong execution plan.

I did clear execution plan cache on the server so it had to rebuild the execution plan.

I have seen this on other app databases that was upgraded to SQL 2008 and what we did was to remove the views from the custom queries and optimize them but in this case not a option as it is standard code that comes with the app. I suppose I will have to open a ticket with the application desingners.

If you have any suggetions it would be greatly appreciated

Thanks for the reply.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

I would open the ticket with designers and see if you can push them to open a ticket with MS and work on this. I'm not sure why this would be a worse performing query on 2008.

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

Thanks for all the replies. I will take it up with MS and designers of the app.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

OomBoom Hall of Fame Points: 3481 More actions · Answer 6

Hi

Microsoft MSDN support gave me a link that suggested a update to CU9 and enabling traceflag 4199. This fix did not work for my problem, actually made it worse. After installing the stuff and enabling trace flag 4199 the query ran more than 20min, I stopped it.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 7

I'd go back to MSDN with the same case number and let them know it didn't work. They ought to keep helping you.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 8

Can you try setting the compatibility flag back to 8.0 and see if the queries performance increase while you research the problem. I have read that instead of a rebuild do a drop and recreate on the indexes. Also you could try to recreate the database using imp/exp. Once again, I haven't tried it, but I've read that some of the 2000 structures are kept around when you just restore the database, and you loose those when you create the shell and import the data in. Let us know.

Tom

OomBoom Hall of Fame Points: 3481 More actions · Answer 9

Hi All

I scripted the database tables used with there indexes and recreated it on test server. I imported the data to new database but this also did not help. Execution plan is still doing scan on index.

What we did was to change one of the inner joins to left outer join. This reduced the query time from 8 min to 4 sec. Has anyone else seen this before?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>