Slow query performance after upgrading from SQL2000

Question

Post reply

Slow query performance after upgrading from SQL2000

jamesnjamesn

SSC Enthusiast

Points: 126
More actions
November 29, 2010 at 11:13 pm

#157337

Slow query performance after upgrading from SQL2000
We are having query performance issues with our data warehouse server after upgrading from SQL2000 to SQL2008R2.
Our problem is that *some* queries which ran in 5 seconds in SQL2000 now take 11 hours in SQL2008R2.
Worth noting that we get the same problem in SQL2005.
The queries which are stalling usually involve subselects. Some of those queries can be rewritten to remove the subselect but other queries are **dynamically** generated by an application which can not be changed.
We have tried altering the following server settings:
- Max worker thread (0 or 256)
- Degree of parallelism (0 , 1 or 4)
- Max memory (Reserve 1GB memory for system)
- Cost threshold (for parallelism) 1,5 or 20
Some of the other things We have tried:
- Adding new indexes
- Update statistics
- Rebuilt all indexes
- Migrating original data into new tables
Environment
-----------
SQL2000 and SQL2005 instances: 4CPU, 4GB RAM, Windows Server 2000 SP4
SQL2008R2: 4 vitual CPU cores, 6GB RAM, Windows Server 2008R2

Viewing 11 posts - 1 through 10 (of 10 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

32 bit or 64 bit?

You said virtual cores, are you running it in a VM?

Can you provide a query sample?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Shanalise Hall of Fame Points: 3188 More actions · Answer 2

I suggests that run your queries with either the estimated or actual execution plan enabled then look at what wasting more space then maybe refactor or design a new query.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

It sounds like the regressions I've heard from other people. In general, SQL Server 2005 and 2008 are much better with execution plans, etc, than 2000. But, there were some edge cases where 2000 was much more forgiving of questionable TSQL practices and generated better executions than what people are seeing in 20005 & 2008. It kind of stinks, but the answer is, rewrite the queries to use better TSQL.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

We had performance issues after upgrading to 2005. Some of them were resolved after closely looking at the joins and finding we needed to modify or add the CONVERT functions between dissimilar data types. Not sure why, but it seemed as though 2000 was more forgiving of data type differences in joins. Look at some executions plans for areas to tune.

Here's a previous thread on this topic:

http://www.sqlservercentral.com/Forums/Topic809627-149-1.aspx

jamesnjamesn SSC Enthusiast Points: 126 More actions · Answer 5

Thanks for the feedback

The SQL2005 instance is on 32bit on a non-VM.

The SQL2008R2 instance is on 64bit running on VM.

Performance issue is the same on both machines.

--SAMPLE QUERY START--

select * from S18_SALES

where afile_code+cast(anum as varchar(9)) not in

(select afile_code+cast(anum as varchar(9)) from s18_account)

and afile_code+cast(anum as varchar(9)) not in

(select pk_afile_code+cast(pk_anum as varchar(9)) from st_account)

--SAMPLE QUERY END--

I know subselects are generally bad for performance however this query is just an example of queries dynamically generated by a 3rd party application.

One thing I can change is the cast/convert - Tried changing this without success.

sebastien piche Ten Centuries Points: 1070 More actions · Answer 6

the tap direcly table or views defrag or reindex is done after migration tempdb is same size in each server 2005-2008 same memory of 2000

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

1) did you run update statistics on EVERYTHING with FULLSCAN?

2) have you done a waitstats analysis while the queries are running for hours?

3) have you done a fileIO stall analysis while the queries are running for hours?

4) have you compared the query plans between versions?

5) get a performance tuning professional onto your system for 1-2 hours and he/she should be able to tell you what is fubar (possibly within minutes).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

What is the RAID configuration of the disks for both the old and the new? How many disks in the arrays? What is the allocation unit size of the disks for the old server and new server?

Have you checked fragmentation of the disks?

Have you checked index fragmentation?

There are so many things that can contribute to poor performance. Kevin made a good suggestion about getting a consultant in to help.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 9

jamesnjamesn (11/29/2010)
Slow query performance after upgrading from SQL2000
We are having query performance issues with our data warehouse server after upgrading from SQL2000 to SQL2008R2.
Our problem is that *some* queries which ran in 5 seconds in SQL2000 now take 11 hours in SQL2008R2.
Worth noting that we get the same problem in SQL2005.
The queries which are stalling usually involve subselects. Some of those queries can be rewritten to remove the subselect but other queries are **dynamically** generated by an application which can not be changed.
We have tried altering the following server settings:
- Max worker thread (0 or 256)
- Degree of parallelism (0 , 1 or 4)
- Max memory (Reserve 1GB memory for system)
- Cost threshold (for parallelism) 1,5 or 20
Some of the other things We have tried:
- Adding new indexes
- Update statistics
- Rebuilt all indexes
- Migrating original data into new tables
Environment
-----------
SQL2000 and SQL2005 instances: 4CPU, 4GB RAM, Windows Server 2000 SP4
SQL2008R2: 4 vitual CPU cores, 6GB RAM, Windows Server 2008R2

Hi,

We were having the same problem after upgrade from 2000 to 2005. We tried everything you mentioned above and could not find the root cause of the problem. We contacted MS and after a week of troubleshooting, the solution was to use

option (force order)

Ref: http://decipherinfosys.wordpress.com/2007/11/13/forcing-the-optimizer-to-execute-the-query-in-a-specific-order/

Try it in your test environment and let us know if it worked or not.

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 10

jamesnjamesn (11/29/2010)
Slow query performance after upgrading from SQL2000
We are having query performance issues with our data warehouse server after upgrading from SQL2000 to SQL2008R2.
Our problem is that *some* queries which ran in 5 seconds in SQL2000 now take 11 hours in SQL2008R2.
Worth noting that we get the same problem in SQL2005.
The queries which are stalling usually involve subselects. Some of those queries can be rewritten to remove the subselect but other queries are **dynamically** generated by an application which can not be changed.
We have tried altering the following server settings:
- Max worker thread (0 or 256)
- Degree of parallelism (0 , 1 or 4)
- Max memory (Reserve 1GB memory for system)
- Cost threshold (for parallelism) 1,5 or 20
Some of the other things We have tried:
- Adding new indexes
- Update statistics
- Rebuilt all indexes
- Migrating original data into new tables
Environment
-----------
SQL2000 and SQL2005 instances: 4CPU, 4GB RAM, Windows Server 2000 SP4
SQL2008R2: 4 vitual CPU cores, 6GB RAM, Windows Server 2008R2

Hi,