MCM Video Series: You Should Be Watching These!

Back in November at PASS Microsoft Announced changes to the Microsoft Certified Master program.  Instead of 3 weeks of training and exam’s in Redmond and close to a $20,000 price tag, you can take a Knowledge Exam and a Lab exam at select Prometric Testing Centers for a price of $2,500 ($500 for the Knowledge exam, $2,000 for the Lab – provided that you pass both on your first try).

One of the things that Microsoft did as well was get the good folks over at SQL Skills to tape 70 different videos, some are 2 parts with a lecture and a demo, on a vast array of subjects to give you an idea of the scope of what you will need to know to pass these exams.

So you may be saying “Ball’s, I won’t be going for an MCM.  It would be nice, but I don’t have the time, the money, or [insert perfectly reasonable excuse of choice]. Why should I care about these videos?” Well, Dear Reader the reason you want to watch these MCM videos  is that they will make you better at what you do, even if you have no desire to pursue this Certification.

So let us discuss how these videos will make you better and why Certifications are important.

How These Videos Will Make You Better.

There are millions, billions, probably more than trillions of things I do not know.  The things I know the most about are my kids, comic books, and SQL Server.  Every time I learn something I get a little bit better.   Whether it’s how the best way to make one of my son’s/daughter’s smile, or that Action Comic’s #775 is the best Superman Story Ever , or the physical structure of a Data Record and how it is stored on a Page, learning these things make me better at things that I love to do.

Paul Randal (blog|twitter), the man himself, does an internal Deep Dive in Data Structures, Kimberly Tripp(blog|twitter)  covering Indexing Internals, Brent Ozar (blog|twitter)  on Virtualization, Bob Beauchemin (blog|twitter)  on Security and Encryption, that’s an awesome line up!  And normally you would pay top dollar in order to get this training, and Dear Reader it is available for FREE!

I’m watching this, the guys I’m working with are watching these video’s, and we’ve even had a couple lunch-in’s where we’ll get a conference room a projector and we’ll watch and discuss the topic that are covered.

Learning makes you better, and this is a golden opportunity to better yourself, even if you have no desire to get the MCM Certification chances are there is a video that covers a SQL topic that you are using daily, and it can probably teach you something that will help you do your daily grind in a better less grinding kind of way.

Why Certifications are Important

Personally I’m a big proponent of Certifications; I was in awe of them before I attempted to get one.  I always thought that just to have a Microsoft Certification you had some super reservoir of knowledge.  When I decided to go for my first certification I got the 70-431 book off of Amazon, I studied it cover to cover, made flash cards, did all of the examples, took the exam and…..failed. 

The crushing defeat lasted just long enough for me to leave the testing center and get into the car.  By the time I turned on the car, I knew exactly what I would do.  While it was still fresh in my mind I would take the items that I was weakest on and I’d make a study guide, to cover those topics like crazy.  I didn’t fail by much and I was already itching for another try.  One week later I took the exam again and got a 902, I was stoked!

I knew the material, I was able to use it at work, and I grew as a professional because I had pushed myself.  You can push yourself without going for the certification, but that piece of paper shows that you took the time to pursue something, and I believe that is important. 

I’ve had employers tell me that they did not value Certifications, but never in the interview.  I have had managers where arguing with them about why Certifications are important happened so often, I could have listed it on my resume as a hobby.  But the second a client wanted to know who the DBA on the project would be they were more than happy to point out that I was their “Microsoft Certified” DBA.  Whenever we were interviewing potential DBA candidates they were quick to mention my Certifications when they introduced me as their Sr. DBA’s. 

I’ve heard the criticisms about brain dumps and Certification Boot Camps, but the fact is if you’re not a DBA, if you’re a hiring manager with no DBA experience, you may not know how to sniff out the real ones from the fakes.  However, if you are a DBA and you studied and earned the Certifications then you know what the other person should know.

In an interview to me a Certification is not a free pass that you know what you’re doing; it is a bulls-eye saying BRING IT ON!  I want tough questions, I want technical questions, I want to make sure that the people in the room to feel confident that they are getting an experienced professional, and if I don’t know the answer to a question then I’m not too proud to ask because I like to learn, and I hope Dear Reader that you like to learn as well. 

So go get learning on what it takes to be a Master, and maybe get motivated to go become one.

Happy Learning!

Brad

Vote For SQL Rally! And Why you should vote for Page and Row Compression.

I received an email on behalf of the Organizers of SQL Rally, from Mr. Kendal Van Dyke (Blog|Twitter), and it felt like a call to action.  It asked that we who have submitted a topic blog about it, post on Twitter, Facebook, and Linked-In.  It had some quotes from Andy Warren (Blog | Twitter), here are Andy’s word.

“"Vote for me" is a start, but why vote for you? Show me why I need to learn the material and why I should pick you to teach it to me. Do you have experience? Credentials? Passion for the topic? Have you been paying your dues in our community and earned the chance to step up? What we’re trying to do here is put more of the marketing work on the speakers. It’s not enough to just submit an idea, you have to help us get people to want to attend, and that starts with convincing enough people that you’ve got the best content out of the options on your track!“

This get’s a big (….check’s that the kids are in bed…) HELL YA from me!  I feel passionate about the topic I’m presenting on, and I would hope so.  I’ve spent quite a bit of time researching it, figuring out how it works, how and when to apply it, and most importantly WHY you would want to. 

So let’s start there why.

Page and Row Compression How, When, and Why

One of the great things about presenting is you get to meet a lot of brilliant people, Bill Ramos (Blog| Twitter) shared with me some insight he had received from the ever brilliant Sunil Agarwal, as to why you should use compression.  It was so good that it is my #1 reason.

1^st Let’s talk space.  Sure hard drives aren’t as expensive as they used to be, unless we are talking Solid State Drives, so one question you may have is why do I care about compressing data and saving (*Cheap*) hard drive space.

Production

Well let’s say you have a Database, and let’s say this is your Production Database Server.  “Well Ball’s”, you say, “I can go out and get a 1 TB hard drive from Office Depot for $100.  Why do I care about compression?” 

We’ll Dear Reader I would say you probably didn’t buy your companies hard drives at Office Depot during black Friday.  You may be using a SAN and the hard drives you put in that SAN may not be the cheapest you could find, probably wanted a bit of Quality, a bit of not super expensive.

I would wager to say that you probably don’t have just one Database Instance at your company.

                        

Production          Staging                 Test                    Dev      

                 

You could have this Database Instance in Multiple Environments.  Don’t forget your offsite Disaster Recovery/COOP (Continuation Of Operations Planning). 

So multiple servers, separate hardware cost, perhaps more than one SAN, the time and effort of SAN Administrators, or outside vendors like EMC, the hard drive space is starting to add up.  And as business look to get a control of or reduce their data footprint Compression can help.

2^nd Reduced Memory Footprint, Compression happens at the Record and Page levels.  

The structures that we know and have become familiar with are physically changed.  When compressed pages are read off of the hard drive they are stored into Memory, the buffer pool, in a compressed state.

This is a very important distinction, so I want to repeat it.  Compressed Records and Pages are still stored in a compressed state in memory.  

So why is this important?  

There are 2 ways to read records in SQL Server, one is off of the Hard Disk and is a Physical Read.  When a Page is read off of the disk it is then loaded into the buffer pool before SQL can use it.  This means that we are able to load more records onto Compressed Pages, which translates into being able to load more data into the buffer pool for SQL to read.  This would normally only be possible by adding more RAM to a server and increasing the amount of Memory that SQL Server can utilize.  Loading pages into the buffer pool allows SQL to get the information quicker is called a Logical Read.

Think of it like the human memory.  

I have a conversation with you 1st thing in the morning about compression, we stop to go grab some coffee, and then we have meetings and work to do, 5 hours later I start to have the same conversation and you have to search your mind to pick up where we left off.  That is a Physical Read, and it took a little more effort.

Now let's try again.

I have a conversation with you 1st thing in the morning about compression, we stop to go grab some coffee, and then come back 5 minutes later and keep having the conversation.  The conversation is fresh in your mind, you don't have to search around for it, it is still in your buffer pool.  

By applying compression properly we just expanded SQL Server's Memory without having to change our physical hardware at all.

3^rd Trading out CPU Cycles for Physical and Logical Reads.  

In SQL Server you can have hardware bottlenecks of CPU, Memory, IO, and Network.  Most SQL Servers that I have seen in the Public Sector tend to have very little CPU overhead, in the private sector it can be a bit more mixed.  But in either setting it is not uncommon to find SQL Servers that have a low usage of their CPU’s.

Just a moment ago I wrote about how the internal structure of records and pages are changed by compression.  You read about how they are still compressed in memory, so you might wonder how it is that SQL accounts for all these different types of pages and records?  The simple answer is it doesn’t.  When the storage engine passes Data Pages from the buffer pool to the relational engine the pages are translated back to their original format.  The additional effort of reading compressed records, and de-compressing them for the relation engine accounts for some of the CPU overhead that Compression adds to a Server.

This is a balancing act and SQL Server accounts for it by shifting some of the load from our Memory and Physical Disks to our CPU’s, which in the majority of cases can handle it.  

DEMO

So Let’s do a little demo tease to give you an idea of how this can help you.  I’m using the AdventureWorks2008R2 Database for this example.  Here is a quick little script

SET STATISTICS IO ON

SET STATISTICS TIME ON

select

    *

from Sales.SalesOrderHeader soh

left join Sales.SalesOrderDetail sod

on soh.SalesOrderID= sod.SalesOrderID

Now we will execute this script twice and get the second set of statistics, so that way we are only looking at the Logical IO’s required for the query.

We give this a run and we see:

It took 1240 logical reads from table Sales.SalesOrderDetail, 686 Logcial Reads for table Sales.SalesOrderHeader, and a CPU time of 1014 ms to return 121,317 rows of data in 5105 ms.

Now we will apply Page Compression to the Sales.SalesOrderDetail table and look at the reduction of logical reads for that table, as well as any increase in CPU time.

/*

Rebuild our table with

Page Compression

*/

USE [AdventureWorks2008R2]

ALTER TABLE [Sales].[SalesOrderDetail] REBUILD

WITH (DATA_COMPRESSION = PAGE);

/*

Set Statistics On

and re-execute our

query

*/

SET STATISTICS IO ON

SET STATISTICS TIME ON

select

    *

from Sales.SalesOrderHeader soh

left join Sales.SalesOrderDetail sod

on soh.SalesOrderID= sod.SalesOrderID

Now Let’s take a look at our results.

And we see our Logical Reads for table Sales.SalesOrderDetail were reduced to 598 from 1240, A Reduction of almost 52%!  We didn’t change Sales.SalesOrderHeader and the Logical Reads remained at 686, our CPU time went from 1014 to 1716 ms, and our elapse time dropped from 5105 to 5079 ms.

“We’re What You Call Professionals”

Unlike the Myth Busters I do want you to try this at home, and probably at work as well.  I’ve busted my hump to research, learn, and apply this topic, set up demo’s showing how you can find your biggest tables, what the allocation unit make-up of the tables and indexes are (*this is important because only In-Row-Data compresses).  How to use DMV’s to track your Scan and Update usage per object, and how scan’s and updates could affect the type of compression you want to use.

So the order I want you to go in is:

1. Gather Baselines-You need to know what your hardware performance as well as your query performance is before you apply compression!

           
     2. Look at your table’s, which tables have the largest amount of records?


    3. Look at their internal structure, are they mainly comprised of In-Row-Data?


    4. What is their pattern of usage, are they updated frequently, is the majority of the usage scans because they are historical data?


    5. Only once you have found the candidates, and determined the type of compression you want to examine use sp_estimate_data_compression_savings

*Keep in mind the over head!  The way sp_estimate_data_compression_savings works is that it takes a 5% sample of the table you are scanning and moves it to the TempDB, performs the actual compression operation and “estimates” the overall compression savings based on that 5% sample, as well as displaying the sample’s compression rate.

This means that if you try to compress a 100 GB table you need to have 5 GB worth of TempDB data space that can be used by this operation.  If you have a maintenance window, this is the sort of heavy operation you would want to do in off hours, and be sure that your TempDB can handle the space it will need.

SQL Rally, Go VOTE!

There are a lot of great sessions that have been submitted for SQL Rally.  The event is a great deal for some really great training, the Pre-Con’s are top notch professionals that you will be seeing and/or have seen at the PASS Sumit.  There are an absolute plethora of topics that are being presented, and you, the SQL community, get to vote for what you want to see.  Go make it great!  Click Here to VOTE, and Click Here to see the sessions you are voting on, and Click Here for the SQL RALLY Website.

Thanks,

Brad

SQL Saturday 62: Slide Deck & Presentation

Hello Dear Reader,
      Since SQL Saturday 62, I've been a little busy with a sick baby girl, and didn't have the time to get my Slide Deck online until now.  Click here to view the slide deck.  I want to thank you for your interest.
      The first presentation went well, I got some really good feedback from the some of the DBA's in the audience I'm looking forward to re-working a couple things.  I wanted to list my thoughts.

Presenting

I was a little nervous leading up to the presentation, as a kid I was always involved in drama, plays, and musicals.  I went to a Magnet High School for the performing arts in Atlanta, GA and my concentration was in Drama.  We moved from Atlanta to Asheville, NC and I continued to concentrate on my acting but I also joined the football team, wrestling team, and played soccer (indoor and out-door) as well.

Needless to say between acting and sports I'm used to being up in front of an audience, and to be honest I kind of like it.  So you would think that all of that would prepare me to go in front of the SQL Community and present. 

(This picture would be more accurate if the Hulk was renamed the Demo)

Demo SMASHED!!!!

I rehearsed my Demo's quite a few times.  The last thing I wanted to do was be caught unprepared, but somehow that is the one thing that happened. 

I tried to do a little too much in my demo's, my subject being compression I wanted to create a set of tables that were sizable, with around 15000 rows, and then apply compression.

Re-creating the tables was taking around 38 seconds when I was testing everything out over the last several days.  On the day of when I went to load up my table I knew there was an issue when I went to run the first insert and after 1 minute and 45 seconds it was still running.

So I did what any actor would do in the case where their props fail them, I improvised.  While I wasn't able to load the tables to the full extent that I wanted I was still able to insert data, and run the compression scripts against them.  What I wanted to convey I was still able to.  All of the info in my Slide Deck was still there, all of the knowledge I have on the subject was still there, I took a deep breath and just kept moving forward.

Always Have a Backup Plan

Since the failure of the Demo's my mind went into troubleshooting mode, you can't really stop your talk to throw up Perfmon or start analyzing your wait stats in the middle of the Presentation.  Well you could, but since the talk was on Compression and not wait stats it didn't seem like the right thing to do.  

But when I got home the first thing I did was reboot my laptop and try again.  This time I could let the demo run, needless to say 38 seconds had turned into 5 minutes and 50 seconds.  YOWZZA, I'm glad I stopped and kept speaking.  Looking at my wait stats I was ready to do some analyzing when by happy accident my battery fell out while the laptop was unplugged, after that reboot everything went back to normal.  But even at normal I don't want to get caught unprepared.

So I started building my backup plan.  There are a bunch of different ways I can present to get my point across, I would like to say I would isolate and fix the issue my laptop was having, but it is working swimmingly right now (YAY and BOOO all at the same time).

I was a Boy Scout at one point in life and I do remember the motto of "always be prepared", and it has not left me.  So I'm looking forward to the next round of presentations, because I will have worked up how I can get my demo's done at least 3 different ways so I don't get blindsided again.

Not sure when the next Page & Row Compression presentation will be but I'm already looking forward to it.

Thanks,

Brad