Backwards Compatibility Level & Snapshots

Back in April I started a Series on the Backwards Compatibility level.  There are a lot of myths around Compatibility Level, in Books Online, (BOL), you can find plenty of literature that states that Compatibility Level determines the set of rules that the Relation Engine uses when judging syntax, creating Query Trees, and Execution Plans.  However I’ve seen plenty of places where in the forums people will say that you cannot use a feature managed by the Storage Engine, when the database is set to 80 or 90 Compatibility, and that is flat out wrong.

Previously on SQLBalls we established that you can use Transparent Data Encryption and Page & Row Compression with Databases set to 80 and 90 Compatibility Level.  Today we’ll do a quick exercise to show that we can use Snapshots against an 80 Compatible Database.

“But Balls,” You say, “You don’t care about 90 Compatibility and Snapshots?”

Great Question Dear Reader, Database Snapshots were introduced in SQL 2005.  When you look at Compatibility levels, level 90 was introduced in 2005, therefore the only Compatibility level we would need to test out is 80.

UNLIKE HARRY POTTER THESE SNAPSHOTS DON’T MOVE

Okay I needed something to go with the picture, but hang with me.  A Database Snapshot is like a picture.  You take a Picture and you have that moment in time captured.  Database Snapshots are a very similar concept.

Behind the Scenes a Snapshot is Managed by the Buffer Manager inside of the Storage Engine.  When Data is changed in memory a copy of that Data, before it is modified is written out to the Snapshot.  You see initially a Snapshot doesn’t have anything to it.  It isn’t until you change Data that you start populating a Snapshot.  As a matter of fact if you request a page that hasn’t changed, even if you query the snapshot you will be reading from the Datafile of the Database the Snapshot was taken of.

Snapshots will start out with very little actual space used, and their maximum size is the size of the Database the Snapshot was taken of, at the moment the Snapshot was taken. 

Enough Explaining let’s get to some doing!

DEMO

First we will take our database from our SQL 2000 instance and we will restore it.  How do you know it is from 2000, because the Database Internal Version number is incremented when moving from one version to the next at the end of Recovery.  And as you can see from the picture below we start at 539 and we go to 661, or in other words our internal version is going from SQL 2000 to SQL 2008 R2 RTM.

So now we’ve got our database restored.  

I’ve got one table dbo.heap1, and a quick count will show we have 15,000 records.

SELECT

     COUNT(*) AS [dbo.heap1 Count]

FROM

     dbo.heap1

Let’s take our snapshot.

USE master;

GO

CREATE DATABASE snapShotTest_Snapshot ON

    ( NAME = snapShotTest_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.BRADSQL2008R2\MSSQL\DATA\snapShotCompatLvl.ss')

AS SNAPSHOT OF snapShotTest ;

GO

Then we’ll delete 10,000 records from our database.

 DELETE FROM dbo.heap1 WHERE myid BETWEEN 1 AND 10000

Now let’s do a select count from our table, and we’ll see we only have 5,000 records left.

SELECT

     COUNT(*) AS [dbo.heap1 Count]

FROM

     dbo.heap1

Now let’s do the same count against our Snapshot.

USE snapShotTest_Snapshot

go

SELECT

     COUNT(*) AS [dbo.heap1 Snapshot Count]

FROM

     dbo.heap1

And we will see that our Count is still 15,000.  We dropped the records and our Snapshot still reflects the number of records we had before we dropped it.

Let’s confirm that our database is still in 80 Compatibility Level.

And it is.  So just to summarize you can use Transparent Data Encryption, Page & Row Data Compression, and Snapshots with a database in 80 Compatibility level.  Tune in next time Dear Reader when we wrap up the Series.

Thanks,

Brad

Buck Woody, Florida, and SQL Saturday 85

What do Buck Woody (Blog|@BuckWoody) and Florida have in common?  Quite a bit actually, and on September 23^rd , at the Lake Mary Marriot, we will add to that list.  Buck Woody, Mr. “The Cloud”, will be presenting on SQL Server Performance Tuning Using Application Path Analysis and it will be AWESOME!

“So Ball’s”, you say “Who is this Buck Woody fellow?”

Buck along with this Brent Ozar (Blog | @BrentO ) fellow had the number 2 ranked session at the PASS Summit last year, You’re Not Attractive But Your Presentations Can Be.  He was a featured presenter on the Quest Virtual Training for SQL Server Series, he has published 5 different SQL Server Books, had over 400 SQL Articles published, he was the President of the Tampa Bay SQL Server User Group for 5 years, a Microsoft MVP, and if you are on Twitter you see that Buck Woody and the Cloud are often mentioned in the same breath. 

Buck has a deep knowledge that comes from being where we are in the real world as a consumer of software products.  He joined Microsoft in 2006 a year after receiving his MVP award for SQL Server.  He brings the same “Real World” DBA sensibilities with a Dash of Microsoft Gusto that you would expect in one of the top names in the industry.

And by name alone he is a candidate to join Jack Corbett (Blog | @unclebiguns) in our future law firm of Biguns & Balls as a full partner.

So now let’s talks some details.

DETAILS

SQL Server Performance Tuning Using Application Path Analysis

There are a lot of resources, products and features you can use to tune the performance of your SQL Server system. Many assume you’re familiar with 400-level concepts, others don’t consider the whole stack of the client, the network, the operating system, platform and the database server. Buck Woody, Microsoft’s real-world DBA, will explain a simple, repeatable process you can follow to tune your entire application – from the client to the server. All of the tools we’ll cover are included with Windows and SQL Server:

·         Using Windows System Monitoring Tools

·         The SQL Profiler and Server Trace

·         Database Engine Tuning Advisor

·         Management Data Warehouse / Data Collector

·         Performance and Configuration Reports

·         The new Activity Monitor

·         Dynamic Management Views and System Views

·         Query Plans

·         Extended Events

·         Resource Governor

In this pre-conference session you’ll cover not only the process, but also review a real-world evaluation. You’ll take home a system and a spreadsheet you can use to monitor and tune your applications, in a simple, easy-to-understand session.

WHAT ELSE IS INCLUDED

For this full day of training, Coffee & tea during the morning and afternoon, with lunch included as well.  All of this Dear Reader for a very Reasonable $99. 

So you may be asking how do I sign up?  Very simple go over to http://sqlsaturday85precon.eventbrite.com/ all of the information is there.  Hope to see you at the event!  ( And Buck Does Too!)

CALL FOR PRE-CON SPEAKERS

As Awesome as Buck is, there is only so much of him that can go around.  So we wanted to offer 2 Pre-cons for SQL Saturday 85 this year.  I’ll be blogging about this more soon but right now head over to my friend Shawn McGehee’s  (Blog | @SQLShawn) post Pre-Con Speaker Wanted: Apply Within to get some more details ASAP about how you can come and be a Pre-Con Speaker as well!

Thanks,

Brad

I Can Be A Presenter And So Can You!

Last year in October, I went to my first SQL Saturday.  It was my first ever live event that I had attended.  I had heard of PASS I had wanted to attend PASS, but I’d never been.  I had not been to a SSUG, SQL Server User Group for the uninitiated.  I wanted to but the nearest was 1 hour out of the way on a 2 hour one way commute when I lived in DC.  I had watched and viewed the 1^st 24 hours of PASS, which was fantastic, but no live events.  SQL Saturday 49 in Orlando was my first and it was fantastic!

I met a lot of great people that day.  Noel Mckiney (@NoelMcKinney | Blog) we had lunch on the lawn and talked for quite a while at the after event, Patrick Leblanc (@patrickdba| Blog), he was at the same table as me and Noel at the after event he was easy to talk to and we had fun just talking about food and New Orleans, and my friend, and ½ of my Future Law Firm Biguns & Balls,  Jack Corbett (@unclebiguns | Blog).

When I went to the after event I knew no one.  Everyone I had listed I had just met that day.  I walked up to Jack and started talking, I don’t know what it was about him but he just seemed like the kind of guy I would want to hang out with.  It is awkward going to a place where you know no one, but the SQL Community has a way of welcoming you in.  We all have a lot in common, once we get talking the conversations have a way of flowing.

I left that evening and I decided that come next year I wanted to present.  As the days wore on I didn’t want to wait until next year.  I looked at what all the presenters were doing and decided I wanted to get myself ready, so I started a blog, hopped on Twitter, created my Linkedin Page, and started working on abstracts for presentations. 

The first SQL Saturday would be Tampa Numero 62.  I submitted 3 abstracts and one was selected.  I was excited beyond belief!  So over the next 2 months I worked on my presentation, presented it internally to all of the DBA’s I work with, got their feedback, tweaked it and went to 62.  There I met up with Kendal Van Dyke (@SQLDBA | Blog ), Andy Warren (@SQLAndy | Blog), and Jack.  We had lunch together and they were talking about SQL Rally, user groups, and the great food they have at Tampa every year.

Kendal had just started MAGICPass, and is the President of the SSUG I now call home, and I asked if they had openings for Speakers, they did and that led to a presentation to MAGICPass in February.  I talked with Jack as well, since he was the President of OPASS, the Orlando SSUG, and that led to a March Presentation for OPASS.  Later that month a speaker had to bow out from presenting to the PASS DBA Virtual Chapter.  Jack had been approached about presenting, and he very graciously suggested me.  It was a great presentation, my first remote, and I loved it!

In April I presented at SQL Saturday 74 in Jacksonville, and had a great SQL Road Trip with Kendal, Dan Taylor (@DBABulldog | Blog), and Karen Lopez (@datachick | Blog).  It was a great SQL Road Trip, and a good chance to talk about the SQL Community and what we can do to make it better.  Also played my first game of SQL Alphabet during the road trip.

I had emailed Jack back in December about the upcoming SQL Rally and he had encouraged me to submit, I owe him a lot for that encouragement.  And in May I made it to my first ever SQL Conference, the Inaugural SQL Rally in Orlando FL.   SQL Rally was my first ever conference for multiple days.  And it was great, I got my first dose of SQL Karaoke, and met a lot of people in person that I had previously only tweeted with.

Fresh off of SQL Rally I thought, why not submit to PASS.  To be honest Dear reader I didn’t expect to get in.  I was realistic about it being the first year that I’m doing this.  And I was floored, the community voted and I got in!  And this year I will be presenting a DEEP DIVE on Compression at PASS!

One year ago today I was still 4 months away from starting a blog, getting on twitter, and attending my 1^st ever SQL Saturday.  So Dear Reader, if you are in the same spot then SUBMIT!  Work up and abstract, email me if you need help.  

There is nothing that I have done, that you cannot achieve.  The hardest part of a journey is often the first step.   So what are you waiting for?   Take the first step click Here to go to the SQL Saturday Page, find one near you and start today! Everyone that you see speaking at events started somewhere, and we would love to see you there.

Thanks,

Brad