April 14, 2008 at 7:39 am
Hello,
I asked you some time ago about one query in DTS job which was running very fast on SQL 2000 (<1 sec) and very slow on SQL 2005 (25 mins).
SELECT HWID1, HWID2,Status FROM REF_UPD_HWID WHERE HWID1 NOT IN (SELECT HWID1 FROM HWID)
You fixed this query by changin a little bit syntax which end in in following code:
SELECT HWID1, HWID2, Status FROM REF_UPD_HWID R
WHERE NOT exists
(SELECT 1 FROM HWID x where x.HWID1=R.HWID1)
I thought about this example as a potential migration showstopper.
If some queries run fast in SQL 2000 and very slow in SQL 2005 how we can be sure that our application will run without performance degradation after migration?
If there will be some queries which run much slower in SQL 2005 than Server can easily reach 100 % of CPU after upgrade.
So two questions for you:
1. Is it possible to get list of all TSQL which run much slower in new engine?
2. How I can prepare failback in such case? Restore from SQL 2005 to sql 2000 is not possible but maybe we can set up replication from SQL 2005 to SQL 2000 for first 1-2 weeks.
Any ideas welcomed....
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
April 14, 2008 at 8:17 am
a production DBA should understand what TSQL workload is being thrown at the db-engine, for comparisons or trend analysis (call it capacity planning if you wish). what I mean is baselines.
if you have Profiler (aka Trace in SQL2000/earlier) output you can identify the top-10 by frequency, duration etc as representative popular/worst-cases.
before migration from version X to version Y, DBA should see how such perform before/after.
in SQL2005 (and later) there are DMV's which are easy to highlight heavy-hitters via SSMS
reports - top queries by tot I/O
reports - top queries by total CPU
etc
you should also buy/borrow/steal the Ben-Gan "TSQL Querying" book (maybe chapter 3 - my copy has walked!) as that deals with reducing the huge mass of Profile data down to common denominators.
You may find some cases where the SQL2005 optimiser takes different compile choices than earlier SQL2000, and for some rare situations you might have to apply HINTs. If your indexes and statistics are decent you should be OK without such overides.
Once a Query Plan is compiled it will be used by subsequent clients with "same" query, but this can blight you if that first query is too simple (may decide a tablescan is adequate) and this may harm later runs. You can force sp_recompile and or sp_refreshview, or put WITH RECOMPILE on any troublesome sproc.
I am surprised [source PASS] there are so many sites still using SQL2000 (not yet upgraded to SQL2005). MS have pledged to assist such backwaters to hike directly to SQL2008, so you could wait just that little bit loger if you wanted !
HTH
Dick
April 14, 2008 at 5:02 pm
berto (4/14/2008)
If some queries run fast in SQL 2000 and very slow in SQL 2005 how we can be sure that our application will run without performance degradation after migration?
The same way the you can be sure that your application will work at all or that it will work correctly: TESTING
All critical production applications should have a test plan as an essential part of a migration, or any change actually.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 6:39 pm
Take a look this post. Performance varies depends on how the query is written (interesting though). http://www.sqlservercentral.com/Forums/Topic476887-146-1.aspx
Have you run update statistics after the migration?
April 14, 2008 at 6:47 pm
2. How I can prepare failback in such case? Restore from SQL 2005 to sql 2000 is not possible but maybe we can set up replication from SQL 2005 to SQL 2000 for first 1-2 weeks.
--> You’d better have it full tested/signed off for the migration. Backing it out is not easy or realistic approach.
April 15, 2008 at 8:03 am
Of course testing is urgent but you can't check everything and it will be enough that there will be 1-2 such queries and you end in with 100 % of CPU. I understand that Microsoft changed Query Optimizer but such performance degradation is not acceptable.
I ran update statistics of course.
Regarding fallback scenario you should always prepare yourself for the worse, so is it possible to set up replication from SQL 2005 to SQL 2000.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
April 15, 2008 at 8:50 am
Berto, you have said that there is a query that can max out your CPU for extended period. I am presuming this is in minority and that all other queries are acceptable [hopefully quicker in SQL2005]
Please tell us what query is giving you grief (complete with all joined table definitions incl indexes) then community can see what might be ailing you and some workarounds.
Suffice it to say that there are very few cases of SQL2005 making the "wrong" choices that require DBA/Dev intervention. You have done update stats (actually I think this is done on restore anyway), and presumably your hardware at least meets the minimum MS spec (ie you have enough memory, your raid arrays aren't challenged etc).
When running your query, what does PerfMon tell you ? - are you CPU-bound, I/O bound or what ??
the Profiler can also show "Error and Warnings" (in properties, events selection check show all events and check most of the events). This will reveal what's going on, and without such solid detail it would be spurious for community to make other empty recommendations!
Dick
April 15, 2008 at 8:54 am
berto (4/15/2008)
Of course testing is urgent but you can't check everything and it will be enough that there will be 1-2 such queries and you end in with 100 % of CPU. I understand that Microsoft changed Query Optimizer but such performance degradation is not acceptable.I ran update statistics of course.
Regarding fallback scenario you should always prepare yourself for the worse, so is it possible to set up replication from SQL 2005 to SQL 2000.
All due respect - but the "you can't check everything" adage is worded a little differently in my shop: you can't afford NOT to check everything. This is not a trivial update, and yes - some things need some help getting "used" to the new DB engine. All in all - I'd say 2005 ends up usually being faster than 2000 - it just needs more help getting there.
Set your expectations where they should be: don't expect that the compilers and optimizers to come up with the same plans, so don't expect that code that ran well in 2000 will run flawlessly in 2005 without a little work. I realize it's a frustrating reality - but it is what it is.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 15, 2008 at 9:09 am
can't check everything? must check everything? bug-free software? hmm !
forgot to answer Berto's last question - yes you can replicate from SQL2005 to SQL2000
there are some gotchas if trying to flow indexes with INCLUDE columns (blows up the SQL2000 subscriber as it tries to apply SQL2005 syntax - nice one MS !)
April 16, 2008 at 1:16 am
So it seems that replication is very good fallback option as I thought. Any cons?
Regarding query I have no particular one. I would like to know if there are some other MS improvements beside this famous 1sec to 25 mins example which I gave you.
We have hundreds of stored procs and a lot of databases and in test we did not recognize problems but Production is always different (not easy to reproduce real traffic). Currently we tested couple of applications and SQL2005 is always slower from 10 to 100 %, but in prod it can end in with 100% of cpu and downtime for customers.
So to summary test is urgent but doesn't cover everything. So returning to my question Are you aware about some syntax other than my subquery example where SQL 2005 is hundresd % worse than SQL 2000. (I'm not talking about 1-2 times worse because this is true for all of them in my case)
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply