November 20, 2018 at 3:45 pm
I have compatibility levels 90 for few of the DB's and I want to bump it upto 110 since it is sql 2012 with sp4..for better performance.
Can I rollback/reverse the changes if required?
Does it require an restart to take effect?
Can I update the settings on PROD anytime?
Will it impact any application or user negatively?
Thanks
November 20, 2018 at 4:05 pm
sizal0234 - Tuesday, November 20, 2018 3:45 PMI have compatibility levels 90 for few of the DB's and I want to bump it upto 110 since it is sql 2012 with sp4..for better performance.
Can I rollback/reverse the changes if required?
Does it require an restart to take effect?
Can I update the settings on PROD anytime?
Will it impact any application or user negatively?
Thanks
What are you trying to fix, and why?
Changing the compatibility level can be done at any time. There is no re-start required.
However, I recommend testing thoroughly before you make these changes. This may be set to 90 compatibility for a reason.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 20, 2018 at 4:12 pm
Thanks!
I am trying to optimize the sql server and make changes which improves the performance. it is 2012 enterprise edition but has 90 lvls which I think is not the good option to be on.
Having said that, what could be possible reasons for keeping it on older compatibility levels and do I have to approach application side or developer team ?
November 20, 2018 at 4:40 pm
sizal0234 - Tuesday, November 20, 2018 4:12 PMThanks!
I am trying to optimize the sql server and make changes which improves the performance. it is 2012 enterprise edition but has 90 lvls which I think is not the good option to be on.
Having said that, what could be possible reasons for keeping it on older compatibility levels and do I have to approach application side or developer team ?
How can you optimize SQL when you are unsure of the effect your changes may have?
You have cleared the cache, which is not an optimization technique.
You have turned on auto update stats, which is normally a good thing, but there may be additional things that need to be done in regards to statistics.
You have turned on the default trace, or at least have made sure it's on. Do you have a use for this?
As far as compatibility levels, 90 is SQL 2005. The internal changes that you may or may not be aware of may cause your code to break. For example, the system tables changed If any of your code is referring to the system, tables, it may break.
Blindly changing the compatibility level without testing it first is not optimizing SQL. You may actually be doing more damage than good.
In another post, I asked what problems you are trying to solve.
The answers, with as many details as possible, will help provide you the right information and set of suggestions to actually accomplish what you are trying to do.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 20, 2018 at 4:51 pm
Also, how do you fix a "slow database" when you don't know exactly what is slow? Do you have benchmarks? A list of the most used stored procedures?
November 20, 2018 at 4:54 pm
pietlinden - Tuesday, November 20, 2018 4:51 PMAlso, how do you fix a "slow database" when you don't know exactly what is slow? Do you have benchmarks? A list of the most used stored procedures?
Same question.
What specific symptoms are you experiencing?
Is it the entire system, or parts of it?
Can you provide some details on the following:
Number of CPU's
Amount of RAM
Disk Configuration
Size of databases
Has maintenance been performed? Such as updating statistics.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 20, 2018 at 5:30 pm
Thank you..server has 4 processors, 64gb ram, approx 25gb 7 user dbs, 5 drives abcdef.
November 20, 2018 at 5:45 pm
sizal0234 - Tuesday, November 20, 2018 5:30 PMThank you..server has 4 processors, 64gb ram, approx 25gb 7 user dbs, 5 drives abcdef.
What OS? 32 or 64?
Can you run this, and post the results?
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply