March 24, 2014 at 12:17 pm
We upgraded QA and production to sql server 2012 last year ( in place) leaving the user databases at sql 2005 ( 90 ). A few months ago the QA user databases were set to sql 2012 compatibility mode. Management is worried about upgrading production and wants to know if we can quickly roll back.
I want to confirm that we can roll back using the same command, and if dbcc freeproccache can be used to avoid having to update all statistics.
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE <mydatabase> SET COMPATIBILITY_LEVEL = 90
This works fine in QA on my own test user database. No errors.
March 24, 2014 at 3:16 pm
No, freeproccache is not a substitute for updating statistics, but the stats needed updating when you actually upgraded the database to SQL 2012, i.e. when it was first attached to SQL 2012 last year.
The compat level just affects how the parser and query processor handle certain T-SQL constructs and you can change it up and down as you like
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
March 24, 2014 at 4:05 pm
We update stats just about every day, and for nearly 8 hours on Saturdays --- the short answer is that we determine which one's need updating the most and go from there.
So if freeProccache won't help us, we'll have to determine which subset to update and for how long since doing all of the stats isn't possible during our Sunday maintenance window. ( we use fullscan since update stats runs multi-threaded that way and sample rates below 50% weren't considered viable )
And thanks for confirming that we go back and forth between compatibility levels ( granted this should be extremely rare and usually a single upgrade with no rollback ), without some extreme approach like exporting all of the data out etc.
We ran the upgrade advisor every which way but Sunday before upgrading -- our applications generally don't make use of any advanced features. A trace on the two deprecated flags did find that sql touching our session state ( aspstate ) database is using READTEXT so we'll have to look into that before upgrading beyond 2012.
March 25, 2014 at 11:06 am
Since our main update statistics job runs for many hours on Saturday ( the primary use database has 4.4 trillion rows in all tables and about 3 TB of data ) I suppose the best we can do is in altering the compatibility level to 110 is:
1) set DB to single_user
2) change compatibility level
3) set DB to multi_user
4) run our normal update stats job which will work down a queue of stats needing updating the most
March 25, 2014 at 11:23 am
Steps 1 and 3 aren't needed and I explained in the earlier post that the stats update was required after you upgraded the DB to 2012, not after changing compat levels (i.e. when the DB was restored to the 2012 instance last year was when you needed to run the stats update, so bit late now)
So your entire procedure is (assuming you've done all the testing):
1) Change compatibility level.
2) There is no step 2
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
March 25, 2014 at 11:30 am
got it, thanks
March 25, 2014 at 11:33 pm
Gila,
Why did statistics need to be updated when attaching to the new version of SQL Server? I did a little googling but couldn't find a reference for it.
Thanks
March 26, 2014 at 1:14 am
Cody K (3/25/2014)
Gila,Why did statistics need to be updated when attaching to the new version of SQL Server? I did a little googling but couldn't find a reference for it.
Thanks
Think simple logic... why statistics require? what it will do? You will get the answer.
Let me help, SQL 2005, 2008 R2, 2012 etc. internally processing queries differently to gain performance. Statistics are used widely for query optimizer to improve the query performance. That could be the reason why after the upgrade performance of DB not upto the mark unless execting update stats.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 26, 2014 at 2:15 am
Cody K (3/25/2014)
Why did statistics need to be updated when attaching to the new version of SQL Server?
It was mainly needed when upgrading from SQL 2000 to SQL 2005, the Query Optimiser (QO) changed and from 2005 the stats structure changed. The 2005 QO could use the 2000 stats, but not efficiently. A lot of people had performance regressions, ones which were fixed by updating stats to the new format.
I don't know whether the internal structure of stats changed from 2008 to 2012, they may have. It's just being defensive, ensuring that any changes can't cause problems.
Same reason you run CheckDB before and after an upgrade, make sure the DB was clean before upgrading, make sure that new improved CheckDB doesn't find any problems after.
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
March 26, 2014 at 5:14 am
free_mascot (3/26/2014)
Think simple logic... why statistics require? what it will do? You will get the answer.
Thanks for your reply.
In this case I get that I could generate my own answer but without hard evidence I put that stuff into the superstition basket until I see otherwise. Like I said, I did Google and check the release notes first... plus I recently went through 70-462 and didn't see it mentioned in my study of the guide.
However on a second attempt and for everyone's benefit, I found this link at the bottom of a comment on a blog post (right where you'd expect it!) http://download.microsoft.com/download/9/5/3/9533501A-6F3E-4D03-A6A3-359AF6A79877/SQL_Server_2012_Upgrade_Technical_Reference_Guide_White_Paper.pdf
In here Microsoft confirms you should rebuild statistics at least on this major version. Now I know for sure, and can back it up with information. Awesome, go team.
Cody
March 26, 2014 at 6:08 am
As general good practice, you should update all stats (with fullscan preferably) and run CheckDB after any version upgrade. Absolutely necessary, no. Avoids possible later unpleasant surprises, yes.
As for the study guide for 462, the list of things it doesn't cover far, far outweighs the list of things it does.
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
March 26, 2014 at 9:34 am
What I'm taking away here is that a sql server version upgrade requires updating stats, among other things, but once that has all been done ( in-place upgrade ) the compatibility level upgrade doesn't require any extra steps. And, you can roll back to the previous compatibility level quickly if you missed something and the application complains.
March 26, 2014 at 9:35 am
Compatibility level you can change up and down as you like. It makes no changes to the database, it just affects how the parser and query processor treat some T-SQL constructs.
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
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply