November 29, 2010 at 11:13 pm
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
November 29, 2010 at 11:29 pm
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
November 30, 2010 at 12:43 am
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.
November 30, 2010 at 7:35 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 30, 2010 at 7:51 am
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://qa.sqlservercentral.com/Forums/Topic809627-149-1.aspx
November 30, 2010 at 5:53 pm
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.
November 30, 2010 at 6:18 pm
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
November 30, 2010 at 10:34 pm
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
November 30, 2010 at 11:08 pm
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
December 2, 2010 at 8:35 am
jamesnjamesn (11/29/2010)
Slow query performance after upgrading from SQL2000We 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)
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 🙂
December 2, 2010 at 8:35 am
jamesnjamesn (11/29/2010)
Slow query performance after upgrading from SQL2000We 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)
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 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply