MagicPASS This Week!

http://www.flickr.com/photos/dgwphotography/4313173011/lightbox/

The MagicPASS SQL Server User Group, SSUG in Celebration Fl, is meeting this Wednesday July 20^th  and Dear Reader do we have a treat in store for you!  MVP & MCM 2008 Maciej Pilecki will be with us.   This is a wonderful opportunity to come out and meet some friends, or catch up with old ones in the SQL Community.    Also come see a top notch speaker, and up your SQL Learning!  

Normally you would only see someone like this at a Confrence, or if you are paying for some training.  Don't miss a chance to visit with one of the 87, currently, MCM's outside of Microsoft.  Here’s our speaker’s Bio:

Maciej Pilecki

Maciej Pilecki (Twitter: @DrHouseOfSQL) is a SQL Server Most Valuable Professional (MVP), Microsoft Certified Master in SQL Server 2008, Microsoft Certified Trainer and Senior Consultant with Project Botticelli. He has many years of international experience in Microsoft SQL Server database development, optimization and troubleshooting. He spends most of his time travelling around the world either advising his many customers on how to improve performance and reliability of their databases, or sharing the lessons learned with conference and user group audiences worldwide, where he is a popular and highly rated speaker. His favourite topics are the internals of the SQL Server database engine, including: SQLOS, Storage Engine and Query Optimizer and Performance Tuning

 

Maciej what is the topic you will be presenting on?

SQL Server Execution Plans - From Compilation To Caching To Reuse

Summary: Execution plan reuse is one of the most important aspects of building well-performing and scalable SQL Server solutions. But it’s also the one that is often overlooked during the design phase and becomes very difficult to troubleshoot later. In this session we will discuss in details the following aspects of SQL Server execution plans: compilation, re-compilation, parameterization, caching, reuse and aging. You will leave this session with full understanding of how to improve your server’s performance by increasing execution plan reuse (or how to avoid reuse in case this is necessary).

If you are interested in coming this Wednesday Here is a link to the MagicPASS website, and or to register CLICK HERE to go to the eventbrite page.  We’ll be starting the meeting at 5:00 pm, but the main event doesn’t begin until 6:30 pm.

“But Balls”, you say “I can’t make it at 5.”

No Worries Dear Reader, most people cannot.  We’ve got the room for the entire time and our fearless leader Kendal Van Dyke (@SQLDBA | Blog) will be showing a PASS presentation from Summit 2010 for those who can.  A lot of people will be arriving up and till 6:30, so don’t let the time stop you.

“But Balls”, you say “I’ll have to grab some dinner, I don’t know the area….”

Not to Worry Dear Reader, Dinner will be provided so make sure to RSVP so we have an accurate count of who to buy food for.  The meeting will be on the main Campus of Stetson University in beautiful Celebration FL, Here is the address for your GPS  Stetson University Center 800 Celebration Ave Celebration, FL 34747 or CLICK HERE for the Google Map.

The Meeting will cost you only your time and the gas to get here.  A Free dinner, a show, and a Microsoft Certified Master, you can't beat that kind of deal!

 I hope to see you at the meeting!

Thanks,

Brad

2011 Half a Year in Review

I'm looking back and looking forward at the same time.  We're about halfway up the mountain, a lot has been accomplished and more is on the horizon.  And as long as we stay on track, the view at the top is going to be spectacular.

7.       MCITP DBA SQL 2008

a.        Still sitting at the same certification levels.  It doesn’t cost a lot of money, but I don’t want to spend the money until I’ve achieved the biggest goal on my list.  Still should have this taken care of before the end of the year.  But I ranked these backwards from least to most important.

6.       MCITP DBA Developer SQL 2008

a.     I’ve got the book, I’m reading and studying for the first one.  But this is in the same boat as number 1, the house, the dog, then the certifications.

5.       13 T-SQL Tuesdays

a.     I’ve missed 3 this year so the best I can do is 10 at this point.  I’ve gotten so busy with projects in and out of work that the second Tuesday of the month just keeps sneaking up on me.  I’ve got a reminder set in my calendar so I can start again next month.  This was a exercise to get me blogging more, to get into the practice of it, and it sure has helped! I know at this point it doesn't look good 2 put off, 1 not  going to happen, but never fear Dear Reader the best is still ahead!

4.       40 Blog Posts

a.     DONE!  And this was the goal for the YEAR!  I have a feeling we are just getting started.  We’ve got some great things cooked up for SQL Saturday 85, and the PASS Summit is yet to even happen.  And Thank You Dear Reader for stopping by!

3.       6 Presentations to the SQL Community

a.     DONE!  SQL Saturday 62 (January), MAGICPass (February), OPASS (March), PASS DBA Virtual Chapter (March), SQL Saturday 74 (April), SQL RALLY!!!! (May), I took June & July off, but planned for this year August 9^th OPASS, SQL Saturday 79 (Submitted, August 13^th),  Pragmatic Works Webinar (August 23^rd), SQL Saturday 85 Orlando (September 24^th Submitted), PASS Summit 2011!!! (October), SQL Saturday 86 Tampa BI Edition (November 5^th Submitted).  Six presentations already and if I’m accepted at all of the SQL Saturdays I submitted to I’ll end up with 12.  WOW, better than I could have imagined

2.  MCM Prep

a. This is a constant and never ending effort to learn.  There is so much to learn about SQL.  Every year that I’ve spent as a DBA I look back on the previous year and think, “Wow I can’t believe I didn’t know that”.   If you ever make it to my desk you will see that for the last 3 jobs I’ve got a stack of flash cards.  I constantly keep blank ones there and work up questions.  I could get multiple flash card from the MCM video’s that SQL Skills did for Microsoft, another from a great question of the day on SQL Server Central, another from a PASS Virtual Chapter Webinar.  My list of flash cards is always growing, and my Prep is only beginning.  I don’t know that this will truly ever be Done, but I’ve benefited greatly just from taking a stab at it.

1.   A House and a Dog

a. This by far is the most important goal.  To have a home and a yard for the kids, and not to move them all over the place any more.  That was the whole point of the move to Florida, to put down roots and stay in one place.  So to that end we found a House!  We’re going through the process and should be closing before the End of July (Crossing toes, fingers, and any other body part that will cross).  We’ve found a great Lab rescue group that we have worked with, had our home visit, and gotten approved.  The only thing we are waiting on for the Dog is to have a house that we can all spread out in.

I had said in a previous Blog that if getting a House and a Dog was the only thing that we accomplished this year it would be a win.  This house is about much more than setting a goal or marking a check box from a list.  I fumble at the words to even describe how important this is, but I know it in my heart.  I feel it in my bones.  This is the biggest and the best one of the year!

So Two done, one that won’t happen, and everything else is in flight.  Not bad.  Hopefully the next update on goals will be soon!

Thanks,

Brad

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