Compatibility Level vs Database Version

I recently had a colleague, Billy Bob,  stop by my desk and with a question about the compatibility level of a database.  Essentially, he was interested in upgrading a database’s compatibility level but at the same time have the ability to down grade it if issues were found.  Billy Bob had a concern that if we had to downgrade, this action would impact the database internal structure or version.

I know that this is an old topic since I found a post from 5 years ago by Jonathan Kehayias (B|T) that talks about the exact same thing, but I think that it’s worth repeating. Paul Randal (B|T) also has one here.

Compatibility Level

The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version.  This functionality isn’t all encompassing as only certain aspects (i.e. certain syntax) of the database would pertain to this setting.  For more details as to what is or isn’t affected, check out Books Online.

We know from various online sources that these are the following compatibility levels:

SQL Server 6.0 – 60
SQL Server 6.5 – 65
SQL Server 7.0 – 70
SQL Server 2000 – 80
SQL Server 2005 – 90
SQL Server 2008 – 100
SQL Server 2012 – 110
SQL Server 2014 – 120

It’s also worth noting that the compatibility level is only available for 3 previous (including the one that you are on) versions from the current version of SQL Server that you are working with.  Here is a listing of the available compatibility levels:

Compatibility Levels
Version Current Compatibility Level Oldest Available Level
SQL 2014 120 100
SQL 2012 110 90
SQL 2008 100 80
SQL 2005 90 70
SQL 2000 80 70
*NOTE: I do not list anything prior to SQL Server 2000 as I don’t have a way to validate what (if any) compatibility levels are available.  Also note that SQL Server 2008 R2 while a newer version still maintains the same compatibility levels as SQL Server 2008.

You can see what compatibility level a database is at by using the UI or via code.

Via UI:

  1. Right click the database
  2. Select Properties.
  3. Go to the Options tab

The following dialog window will show the compatibility for that particular database.  In the example below, you can see that the compatibility mode for the database DBVault is currently 2012 or 110.

compatibility_1

 

 

 

 

Via Code:

For SQL 2005 & newer:

SELECT name, compatibility_level from sys.databases WHERE name = 'DatabaseNameHere'

For SQL 2000:

SELECT name, cmptlevel from sysdatabases WHERE name = 'DatabaseNameHere'

If you want to see the compatibility for all databases of the instance, just leave off the WHERE clause.

Database Version

The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of.  If you migrate a database from an older version to a newer version, the database version value will be increased to reflect the version number of the new server’s model database.  The database version does not equal the compatibility level and should be considered as a completely different attribute of the database.

When you create a database, the database version is “stamped” with the same version as the Model database.  It’s worth noting that if the Model database was originally created on a different server edition and then subsequently upgraded, you potentially could end up with slightly different numbers than what you might expect.  As you upgrade the database to new SQL Server edition (you can’t go backward) the version of the database increases. This is done automatically regardless of what method you use to upgrade the database to the new version of SQL Server.

Here are some database versions numbers for each version of SQL Server:

Database Version Levels
SQL Server Version Database Version
SQL 2014 782
SQL 2012 706
SQL 2008 R2 661
SQL 2008 655
SQL 2005 611
SQL 2000 539

I did find this article that has a more complete listing of database version values.

It is worth noting that service pack releases will most likely increase the database version of all the attached databases.  There are a couple of ways to determine what database version a database is currently at. There is not, however, any way to do this through a UI that I’m aware of.  Here are 3 ways in code that I know how to get this information.

-- using DBCC PAGE to look at the boot page (9) of the database
DBCC TRACEON (3604)
DBCC PAGE('DatabaseName',1,9,3)
DBCC TRACEOFF (3604)
GO
--using DBCC DBINFO
DBCC TRACEON (3604)
DBCC DBINFO
DBCC TRACEOFF (3604)
GO

-- useing database property
SELECT DatabaseProperty('DatabaseNameHere','version')
GO

You’ll note that for each DBCC command we have to turn on trace flag 3604 so that the output of the DBCC command is sent to the SSMS window rather than the default location, the SQL Server log.

Ironically, if you are still on SQL 2000, you can see this information with a simple query:

-- useing database property
SELECT name, version from master.dbo.sysdatabases
GO

If you use the DBINFO/PAGE option, you will need to look for the hi-lighted values in the output:

dbVerionExample

 

In the above example, you can see that I’m looking at the AdventureWorks database, the database version is currently set at ’706′ and it was created with version ’705′.  This tells me that the database was initially created on a SQL Server 2012 instance (possibly a CTP version) and then subsequently upgraded when restored to my test SQL Server 2012 SP2 instance.

Bringing It All Together

Now that we understand the difference between the compatibility level and the database version, let’s bring it all back to our initial story.   If I recall correctly, the database in question started off as a SQL Server 2000 database which is a compatibility of ’80′ and an internal version of ’539′.   As mentioned, through the course of the years we have been able to upgrade it to SQL Server 2008 and currently in the process of getting it moved to SQL Server 2012.

Since we know that the database was migrated to SQL Server 2008, the database version was increased to match the model database of that instance, so it was “stamped” with ’655′.  The problem is that while the database version was increased, the compatibility mode was never upgraded (it slipped through the cracks as a migration step) to reflect SQL Server 2008 and remained at the SQL 2000 level of ’80′.

Since we also know that the database version is related to the version of SQL Server the database is sitting on, we know that we can adjust the compatibility level accordingly without affecting the database version.   Thankfully we have a good testing environment and were able to easily adjust the compatibility level to reflect SQL 2008 level of 100 and verify that nothing was impacted.  Once testing was complete, we made the change in production knowing that we were not going to affect any subsequent applications.

Did I mention that we tested?  Even with this change, you will want to test your applications to ensure that all of your code continues to work as you expected.

As a side note, we now have steps and/or scripts in place, so that when a database migration occurs upgrading the compatibility level to a higher level is accomplished.

Enjoy!

Posted in Internals | Tagged , | Leave a comment

Upcoming Speaking Engagements

I think that speaking engagement comes in waves, just like everything else.  Over the course of the summer I’ve had very little speaking opportunities and now within the span of a couple of months I’ll be speaking on a number of occasions.

As one who is addicted to teaching & speaking in general, this is a good thing!

Here are some upcoming speaking engagements!    Also, if you would like for me to do a presentation, either remote or onsite, please feel free to reach out to me! I’d love to present to your organization!

August 2014

pass_logoOn Thursday August 28th, 2014 I’ll be doing a remote presentation labeled “Only You Can Prevent Database Fires!“.  This presentation hi-lights some of the things that we as DBA’s we can do to prevent database fires.  A little fire prevention and/or awareness will go a long way, even in the technology world.

September 2014

24HOPOn Tuesday September 9, 2014 at 7PM CST, my good friend Chris Shaw (B|T) and I will be presenting during the next segment of 24 Hours of PASS (24HOP).  This segment will hi-light some of the sessions and speakers that will be presenting at the annual PASS Summit in November.  Chris and I will be going head to head during our session “Real World SQL 2014 Migration Path Decisions“.  The 24HOP offers 24 hours of free training so it’s a great (and cheap!) way to get training!

sqlsat300_webI’ll be heading down to Kansas City, Missouri for the 300th SQL Saturday that is being held on Saturday September 13, 2014.  I’m always honored to have been selected to speak, especially as this marks the 300th SQL Saturday.  I’ll be speaking about Social Network and how it can improve your life!  Much like the 24HOP event, SQL Saturday’s is another great way to get free training.  If you can make it, I highly recommend these events.

November 2014

PASS Summit 2014 Banner_200x200

To round out the fall, my good friend Chris Shaw and I will be doing a pre-con at the annual SQL PASS Summit 2014.  This event will be held in Seattle, Washington and it’s another event that I highly recommend.   Like the 24HOP session, Chris and I will be going head to head discussion “Real World End to End Performance Solutions“.  While these events (the Summit & Precons) are not free, they are worth the investment.  See you in Seattle!

It’s going to be a busy schedule this fall but I’m really looking forward to it!

 

Posted in PASS, Presenting, SQL Saturday | Tagged , , , , , | Leave a comment

Argenis Without Borders

You never know what might come out of a twitter conversation these days.  Several weeks ago, I was involved in a twitter conversation with Argenis Fernadez and Kirsten Benzel. During this conversation, Kirsten brought up the notion of purchasing a unicorn hoodie, bringing it to the 2014 SQL PASS Summit later this year and getting Argenis to wear it.

argenis_hoodie_3I mentioned that we could probably raise money for a charity if we could get Argenis to agree to some terms.  Of course, being the awesome guy that he is, he quickly agreed.

Just like that a challenge is born!  If we raise $5,000 for Doctors Without Borders, our very own Argenis will wear the magical hoodie during one of this sessions at the Summit.

Update: To donate, please click on the link above or you can use the image located on the right hand menu labeled “Make a Donation”.  Either location will take you to the right spot!

Of course that’s not the end of the story.  It gets better.  Our original goal was $5,000, thinking that it would be completely awesome if we could raise that much by November.  We hit that mark in less than a month.  So we upped the goal and the anty.  Now we’re talking about “rainbow fluffies” and tattoos in additional to the unicorn hoodie.

Did I mention that Kirsten got others to take on the challenge as well?  You got it!

$10,000 In Donations

If we hit this level, Steve Jones (B|T), Grant Fritchey (B|T), Brent Ozar (B|T), and Adam Machanic (B|T) will wear these fantastic Rainbow Fluffies at some point in time during the SQL PASS Summit.  In addition to the fluffies, Kirsten will run the #SQLLongRun while wearing the aforementioned unicorn hoodie.

Here’s what Grant has said about it:  http://www.scarydba.com/2014/07/23/challenge-accepted

See what Brent wrote: http://ozar.me/2014/07/donate-doctors-without-borders-ill-wear/

$25,000 in Donations

If we hit this level Gareth Swanepoel (B|T), Jason Strate (B|T), Ed Watson (B|T), and Kirsten Benzel (B|T) will get SQL Server tattoos! Yes, you read that right.  TATTOOS!

At the time of this writing, we are currently at $6,802 (68%) of our $10,000 goal so more donations would be appreciated!  Remember that Doctors Without Borders is a 100% tax-exempt so your donation is tax deductible!

argenis_in_the_hoodie

Can you feel the awesomeness?

So donate!!  Come see our good friend Argenis wear this fantastic hoodie in the name of charity!!

For those that have already donated, we can’t thank you enough for your generosity.  Doctors Without Borders is a great cause and we really appreciate your help!

Did I mention that the hoodie has a rainbow tail and wings?

See you at the 2014 SQL PASS Summit!!

Posted in Community, PASS, Summit | Tagged , , | 1 Comment

Don’t Miss Your Vote!

PASS Summit 2014 Banner_200x200On July 2, 1776 in Philadelphia Pennsylvania, the Continental Congress cast a historic unanimous vote to adopt a resolution declaring our nation’s independence from British rule.  On July 4th, 1776 the final wording of the Declaration of Independence was approved and sent to the printer.

Keeping this historic event in mind,  if you are attending the 2014 SQL PASS Summit later this year in Seattle, Washington you could be missing out on performing one of your civic duties; one that was made possible with the actions of the Continental Congress in 1776.  The right to vote.

All across the United States a general election is scheduled to take place on Tuesday, November 4th, 2014.  Given that the PASS Summit 2014 starts on that day, and if you are like myself and do not live in Seattle, you could potentially miss out on getting your voice heard in this years election.  Even if you do live in Seattle, the ability to vote and part take in the all of the Summit’s glory might prove to be difficult.

You do have an option though, and that’s through the use of an absentee ballot.  You should be able to contact the Secretary of State department for the state that you reside to find out what the process is.

vote-counts-button

There are usually deadlines for requesting & submitting an absentee ballot so please make sure that you confirm the dates and mark them on your calendar.  I usually set two reminders in my to-do list, 1) when I need to obtain the ballot by and 2) when I need to submit it by.

While I am a huge proponent of the PASS Summit and the SQL Community in general, please do not miss the opportunity to have your voice heard because of it.  Using an absentee ballot will give you piece of mind that while attending the first day of the 2014 Summit, your voice will be heard.

Vote.  Make a difference.

Posted in Community, PASS, Philosophical, Summit | Tagged , , , | Leave a comment

PASS Summit 2014 – All Day Pre-Con

sqlsat271_web It all started at SQL Saturday #271 in beautiful Albuquerque, New Mexico.  My good friend Chris Shaw and I were sitting outside the event catching up on things as we usually do. Our topic of discussion turned to one of presenting and comparing notes of our most recent speaking engagements.  After a few minutes of chatting, we both realized that relating the topic of choice to real world examples was turning out to be a great way to further ingrain the technology to the attendees.  What better way to learn about things than to speak about real world scenarios?

As our ramblings continued, we stumbled on the notion that having two data professionals, both with a wide range of skills and backgrounds, present in a “rumble” type of fashion could potentially be a fun and entertaining session.

PASS Summit 2014 Banner_200x200

As simple as that, a pre-con abstract is born!

So, come, join us for our full day pre-con at the PASS Summit 2014 in beautiful Seattle and see who is the victor!  There might even be a trophy! I can assure you that it’s going to be a day filled with technology and real world solutions brought to you by two guys who have a lot of passion for Microsoft SQL Server and the PASS Community!

In case you wanted to see the official Abstract we have included it here. We hope to see you there!! To register check out this link.

Real World End-to-End Performance Solutions

Do you ever wonder how other people approach real world business objectives from start to finish? The success of a project can be hampered from decisions that are made before a single row resides in a database, and yet often there is no one single best solution, because different people will approach a problem from different angles and there are countless variables along the path that are critical to success. If you make a mistake at the beginning, costly redesigns may haunt you for years to come.

In this powerful yet entertaining full-day pre-conference session, we will start with two different database professionals with two different skill sets. The speakers both have very different ways of approaching a project (and are both set in their ways!), but both have the same measurable goal. Whose process runs faster? Join this session and see the results.

In other words . . . “Let’s get ready to Rumble!”

What may appear as the end of the session is really only the beginning as variables and limitations are added and various questions are answered: What happens to our project when we are forced to use GUIDs (Globally Unique Identifiers), or when we push our solution to the cloud? Does the introduction of new hardware improve the overall outcome of our test? Do we see faster performance with SQL Server 2014 over SQL Server 2012? Join us and find out!

The Combantants

chrisshawChris Shaw:  In the red corner representing Colorado Springs, standing tall at 5 foot 6 nothing but attitude. Armed with a staggering 17 years of experience for a right hand and striking fast wit for the knock out on the left. Your 5 year SQL Server MVP, Pass Regional Mentor, and Colorado Springs User Group leader… Chris Shaw.

You can find Chris on Twitter at @SQLShaw

jmorehouse_preconJohn Morehouse: In the blue corner, backed by corn huskers and fire fighters, a true hero dedicated to destroying poor database performance and the sharing of database knowledge. Lighting fast reflexes and in depth knowledge is unleashed to deliver lethal blows. The towering, the intimidating and the inspirational to so many… John Morehouse.

You can find John on Twitter at @SqlRUs

So go, register, sign up and come see Chris and I battle it out to the bitter end.  Fun & learning will be had by all!

Posted in PASS, Presenting | Tagged , , , | Leave a comment

SQL Saturday #307 – Iowa City, IA

sqlsat307_webAlthough I will admit that I’m late on getting this blog post out, it’s that time again.

I am honored to have been selected to speak at SQL Saturday #307 in Iowa City, Iowa this year.  So, later today I’ll be making the 4 hour drive over to see the awesome folks in Iowa.  It looks like to be a great day for a scenic drive and I’m looking forward to some quiet time on the road.

So if you have nothing better to do on Saturday, June 7th, 2014, come on out and get your learn on.  The schedule for this event is quite impressive and there will be a TON of things to learn.

I triple dog dare you to come out (name that movie), introduce yourself to me, and get your learn on!

10 Things Every DBA Should Know!

This session is one that I’ve given before and very much enjoy giving it.  There are a ton of things that DBA’s need to know, but in this session I go over some of the things that I’ve seen in the trenches.  We’ll talk about things such as SELECT *, data types, Auto Shrink and a couple of other things.  I don’t want to ruin the whole thing for you.  So, if you are new the DBA realm or just want to come watch me present, please stop by.  Bring your energy and your questions and we’ll get it done!

I’ll be in the Room 2520B –  1:00PM.  It’s a session right after lunch so I reserve the right to throw things at you if you fall asleep from lunch! I’m a former lineman so I can’t throw worth a darn so you might be safe.  Your neighbor sitting next to you on the other hand might be in trouble.

Free Training

Remember that this event is a FREE day of awesome SQL Server training and professional development.  Check out the schedule and register.  In today’s economy, how can you afford to pass up FREE training?!?!

If you make it out my session, make sure to come up and introduce yourself as I’d love to meet you! I know I can look like a mean DBA but I promise that I’m not.  =)

See you in Iowa City!

Posted in Presenting, SQL Saturday | Tagged , , | Leave a comment

Query Optimizer Wins Again

In a previous post, I mentioned that I had to use dynamic SQL to handle the switching of the context of the query in order to accomplish what I wanted.  I had also mentioned that I would be putting out another blog post regarding that dynamic SQL and here we are!

As a recap, in that post I was attempting to dynamically calculate the server memory requirements based on the physical memory installed on the machine.  I’ll also mention again that Microsoft changed the structure of the DMV that was used between SQL Server 2012 and SQL Server 2008 R2 and lower.  In order to run the query on all servers, I had to determine a way to dynamically handle the appropriate structure for the various versions of SQL Server we have.

This is how I ended up doing it:

-- Setup the dynamic SQL
-- We need the physical memory values in GB since that's the scale we are working with.
IF @serverVersion > 10
	SET @sql = '(SELECT @memOut = (physical_memory_KB/1024/1024) FROM sys.dm_os_sys_info)'
ELSE
	SET @sql = '(select @memOut = (physical_memory_in_bytes/1024/1024/1024) FROM sys.dm_os_sys_info)'

-- Get the amount of physical memory on the box
EXEC sp_executesql @sql, @paramdefs, @memOut = @mem OUTPUT

This is the way that I actually tried several times to handle it:

-- We need the physical memory values in GB since that's the scale we are working with.
IF @serverVersion > 10
	SELECT @memOut = (physical_memory_KB/1024/1024) FROM sys.dm_os_sys_info
ELSE
	select @memOut = (physical_memory_in_bytes/1024/1024/1024) FROM sys.dm_os_sys_info

Of course, I know now that this won’t work at all.  I’ll be honest and say that it took me more than a few minutes to figure out why my logic wasn’t right and why SQL Server kept yelling at me.

The Truth Shall Set You Free

query_optimizer

Query Optimizer

Doh!  Of course, it was the query optimizer! The first thing that the optimizer does is to validate (or parse) the syntax of the code.

Knowing that this is the first thing the optimizer will do, looking at one of the branches of the ‘IF’ statement in the wrong (bottom) example, the columns were valid.  For the other branch,  it wasn’t because that column didn’t exist for that particular table depending on what version of SQL Server the code was examining.

When it couldn’t find the column, the optimizer had to bow out simply because it couldn’t verify that the column existed.  This forced me to put the syntax of the query into a dynamic statement simply because the dynamic sql isn’t parsed until the query itself is called through the sp_executesql command.

When using dynamic sql, the query optimizer just treats the building of the syntax just as regular text and happily continues along it’s merry way.

Once the dynamic sql syntax was in place, the query optimizer was then able to validate the query and things all fell into place.

Some References

SQL Guru  has a great post on how the optimizer works.  I highly recommend reading it if you’re not familiar with it.  Heck, even if you are familiar with it, read it anyway as a refresher.

Another SQL Server Guru, Paul White, also has a deep dive into the query optimizer and you can find the first post in his series here.

Another Lesson Learned

This little experience, while probably trivial,  was also a great reminder that we don’t always know or remember everything about SQL Server.  SQL Server is such a massive platform that you won’t know or remember everything about it.  You will at times forget even some of the simplest of things and that’s ok when that happens.  We are human after all.

Summary

The next time that you are trying to get some code to compile and it won’t, take a moment see if the query optimizer can do it’s job.  If the query optimizer can’t parse and bind your code, then it won’t even get off the ground.

Enjoy!

Posted in Internals, T-SQL | Tagged , , | Leave a comment

SQL Saturday #297 – Colorado Springs, CO

sqlsat297_webYes, folks it’s that time of year again.  I’ll be heading to Colorado Springs, CO this upcoming weekend to join the ranks of speakers as one of the chosen ones.   As usual, I’m honored to be speaking at their event.  They always have an outstanding list of speakers and to be among them is truly an honor. 

Colorado_LandscapeI very much enjoy traveling to Colorado.  The scenery is breath taking and the people there are incredible.  If you have never had the opportunity to visit, I would highly recommend it.  

This year the Colorado Springs group is doing something a little different.  They are hosting a food drive in order to help the local community and I for one will be stopping at a local grocery store to load up on canned food before the event.  I think that this is a fantastic way to give back to the community. 

Microsoft MVP Chris Shaw did a blog post about the food drive here.    

SQL Server Databaseology 201 – The Study of Database Internals

This is probably one of my favorite presentations as I get to talk about the internal structures of a Microsoft SQL Server database.  I’m not talking about tables, views, or procedures but rather things such as how each row is structured, how does SQL Server keep track of your data and various ways to go diving into the depths of the internals.  This presentation is packed with demo’s and fantastic #Sharknado references!  We’ll also discuss how your data types matter and that despite what you’ve been told, disk isn’t always cheap.

If you haven’t checked out the schedule yet, you should.  There are some fantastic speakers going to this event.  Speakers like Mike Fal (B|T), Chris Shaw (B|T),  Jason Kassay (T), Jason “Cannonball” Horner (B|T), Steve Jones (B|T), Meagan Longoria (B|T), Thomas LaRock (B|T), Keith Tate (B|T), Glenn Berry (B|T), Jason Strate (B|T) and many others.  I assure you that this is a schedule you don’t want to miss!

Free Training

SQL Saturday’s is a great venue for FREE training and I highly encourage you to attend if you can.  It’s one Saturday out of the year for this particular event and I promise that it’ll be worth your time.  If you haven’t yet experienced the awesomeness that is SQL Saturday, you are missing out and now’s the time to change it!

If you are in my session or even just wandering about, please feel free to come up and introduce yourself!  I enjoy meeting new people and I promise I won’t bite!

Still here?  What are you waiting for?  Go.  Now.  Register.   Take the leap and move forward in your learning & career! DO IT!!

Posted in Community, Presenting, SQL Saturday | Tagged , , | 3 Comments

SQL Saturday #287 – Madison, WI

sqlsat287_webThis upcoming weekend will mark a first for me.  I get to speak at SQL Saturday 287 in Madison, WI.  This will be my first time speaking at this particular event and I’m very much looking forward to it.

I’m also bringing my family along for the ride as my wife has relatives in the area so we are making a vacation out of the trip.  Nothing like a mini-vacation with some #sqlawesomesauce mixed in!  Whoo Hoo!!

So if you have nothing better to do on Saturday, March 28, 2014, come on out and get your learn on.  The schedule for this event is quite impressive and there will be a TON of things to learn.  Come, join us and learn something.  As my wise father always told me, you should never stop learning.

Oh yeah, I’m speaking!

Social Networking Is Alive!

SocialMediaIsAlive!Social media is every where these days and if you’re not on the train, you could be missing out.  This session is about how you can use social media to improve not only your professional life, but your personal one as well.  This session isn’t geared to specifically geeks (totally non-technical), but to anybody who wishes to improve their online presence through the use of social networking.  It’s a fun session and I hope that the attendee’s enjoy it!!

HINT: I’ll show you how to get free SQL Server help from one of the popular social networks.  How would you like to tell your management that you solved a real world enterprise level issue with the help of social media?  For free?

Better yet, if someone in the audience would have a SQL question that they want answered, we’ll see if we can get the #sqlfamily to help out.  I’m guessing that we will!

You might even see reference to a popular YouTube video.

Free Training

Can you really pass up on free training?  Ok, even with lunch it’s only $10!  So if you’re still on the fence on attending, get off the fence, register, come on out and learn something.  Meet some new people.  Grab some swag and a fantastic lunch.  I promise you that the speakers won’t bite.  Register now!!

Get your learn on!  I triple dog dare you!

Why are you still here?  Go.  Register.  Do it now! See you in Madison!

Posted in Community, Presenting, SQL Saturday | Tagged , , | 4 Comments

Configuration Validation – Max Memory

We are in the midst of rolling out SQL Server 2012 to our environment and, like most shops, we customize our SQL Server instances based on our environment.  Since we don’t just let things go straight out of the box, we have to verify things are done in the manner which matches our deployment process.  We don’t deploy new servers often so the need to fully automate this process isn’t here yet.

What we  want to do, is to have a way to perform an easy validation of an install to ensure that any settings weren’t missed.  So I started to put together a script that I could easily run on a server to validate certain settings were in place.  This script checks for things like certain trace flags, what is the max memory set to, how many data files does TempDB have, etc etc. For this post, we’re going to focus on the max memory setting.

Max Memory

Setting the max memory (and even the minimum memory) for a SQL Server instance is a very important thing to address when installing a SQL Server.  It’s a hog and it will gladly take over all of the memory if you let it, thus starving the operating system or any other applications that you might have installed.

One of the tools that we use when configuring, and very well I might add, is information from a blog post by Jonathan Kehayias (B|T), “How Much Memory Does My SQL Server Actually Need“. This is a great post as it fits our environment pretty well.  We have dedicated SQL Servers and are they aren’t virtualized so it’s a great place to start.

Essentially, Jonathan recommends this as a starting point:

  • Reserve 1GB of RAM for the Operating System
  • Reserve 1GB of RAM for each 4GB of RAM installed from 4-16GB
  • Reserve 1GB for every 8GB of RAM installed above 16GB of RAM

For eample, for a server with 192GB of total physical memory installed:

  • 1GB = Initial OS Reserve
  • 4GB = If you have more than 16GB of physical memory, otherwise (Total / 4)
  • 22GB = Total amount of memory minus 16GB divided by 8 for anything greater than 16GB of physical memory.  If you don’t have 16+ GB of memory, this is Zero.
  • 27GB = 22GB + 4GB + 1GB (total amount to reserve for the OS).
  • 165GB  = 192GB – 27GB.  This the total amount to give to SQL Server
    • NOTE: To set this value, you would take 165*1024 = 168,960 (in MB)
configvalidation_maxmemory_1

Setting Max Memory

Armed with this information a simple spreadsheet was born to handle the math.  This worked great! Of course, until we wanted to script things out. Once that milestone was reached, it was obvious that the real need was to dynamically handle this in a script.  So, I started looking into how to accomplish this.

The Script

To start with I had to find out where SQL Server was keeping the total amount of physical memory that the server had.  I’ll be honest and say that I had to look for it.  I thought that I might have to read the registry, but thankfully I found a DMV, sys.dm_os_sys_info, that gives you that information.  You can read more about this particular DMV here:

If you look at the information from TechNet, you’ll see that the ‘physcial_memory_KB‘ is the column that we’re interested in as it represents the amount of physical memory that is available.  However, one thing of note is that the column name was changed from SQL 2008 R2 to SQL 2012.  In the older versions (2005 – 2008 R2) the column name is ‘physical_memory_in_bytes‘.

Along with that, the number of bytes that the number represents is also different.

In 2012 it’s ‘physcial_memory_KB’.  

In 2008 R2 (and lower) it’s ‘physical_memory_in_bytes’

I had to account for not only the name difference but also the different context (KB versus Bytes) in the script as we have a mixture of instances running and I thought that it would handy to be to run this across the Enterprise.

Once I knew where the information was kept, it was relatively easy to do the math that I needed and spit back the results.  Since we have a mixture of SQL Server versions, I used ‘SERVERPROPERTY‘ to determine what version of SQL Server the script was running against, thus allowing me to dynamically adjust for the differences in the DMV mentioned above.

You’ll also probably notice that I am using the sp_executesql command to dynamically handle the DMV differences between SQL Server versions.  This will be another blog post.

I tested the script on SQL 2005, 2008 and 2012.  Anything older than that, you’re on your own since 2000 didn’t have the DMV’s at our disposal.

DECLARE @mem NVARCHAR(10)
DECLARE @memOut NVARCHAR(10)
DECLARE @totalOSReserve INT
DECLARE @4_16 INT
DECLARE @8Above16 INT
DECLARE @serverVersion INT
DECLARE @sql NVARCHAR(MAX)
DECLARE @paramdefs NVARCHAR(500)
-- Put this into a parameter so it's configurable
DECLARE @osMem INT

-- Done this way to handle older versions of SQL Server
SET @osMem = 1 -- leave 1GB for the OS
SET @8Above16 = 0 -- leave it 0 by default if you don't have more than 8GB of memory
SET @serverVersion = LEFT(CAST(SERVERPROPERTY('productversion') AS VARCHAR(100)),CHARINDEX('.',CAST(SERVERPROPERTY('productversion') AS VARCHAR(100)),1)-1)
SET @paramdefs = N'@memOut INT OUTPUT'

-- Setup the dynamic SQL
-- We need the physical memory values in GB since that's the scale we are working with.
IF @serverVersion > 10
	SET @sql = '(SELECT @memOut = (physical_memory_KB/1024/1024) FROM sys.dm_os_sys_info)'
ELSE
	SET @sql = '(select @memOut = (physical_memory_in_bytes/1024/1024/1024) FROM sys.dm_os_sys_info)'

-- Get the amount of physical memory on the box
EXEC sp_executesql @sql, @paramdefs, @memOut = @mem OUTPUT

-- Start the Math
IF @mem > 16
	BEGIN
		SET @4_16 = 4
		SET @8Above16 = (@mem-16)/8
	END
 ELSE
	BEGIN
		SET @4_16 = @mem/4
	END

-- Total amount of memory reserved for the OS
SET @totalOSReserve = @osMem + @4_16 + @8Above16
SET @mem = (@mem-@totalOSReserve)*1024

-- Use sys.configurations to find the current value
SELECT (@mem/1024)+@totalOSReserve AS 'Total Physical Memory'
	, @totalOSReserve AS 'Total OS Reserve'
	, @mem AS 'Expected SQL Server Memory'
	, value_in_use AS 'Current Configured Value'
FROM sys.configurations
WHERE name = 'max server memory (MB)'
GO

Running this query against our Central Management Server was a great way to validate that all of our installs (both new & old) were set correctly.

Any server that isn’t set correctly can be dynamically adjusted to the appropriate value.  Keep in mind if you are increasing the amount of memory for SQL Server to utilize, you won’t need to reboot.  If you are lowering it and it already has more than the limit in use, you’ll have to work in a reboot to release it back to the OS.  Once you reboot the server then it’ll be capped at the new upper limit.

Of course, it you need to do this in ProductionI highly recommend that you do all of the appropriate testing required by your business and/or policies.

Resources & Permissions

Click on the individual links to see the Microsoft reference material.  I’ve also listed the permissions that you’ll need to have in order to run this:

Summary

There are a lot of things that we check for in our validation script but this portion was an interesting piece to build and I think will turn out to be pretty handy!

Enjoy!

Posted in Administration, T-SQL | Tagged , , | 5 Comments