PASS DBA Virtual Chapter Deck and Demo's Live

Hello Dear Reader, I wanted to say a quick Thank You to the PASS DBA Virtual Chapter for having me  today.  My Deck and Demo's are up on the Resource Page.  Thank You to the 150 people who took the time to attend as well!  We couldn't do this without you.

Thanks Again,

Brad

PASS DBA Virtual Chapter Trimming Indexes, Getting Your Database in Shape

Hello Dear Reader!  Tomorrow at 12 noon eastern I’ll be presenting for the PASS DBA Virtual chapter, click here to sign up for the meeting.

If you aren’t a member of/or familiar with PASS it is the ProfessionalAssociation for SQL Server.  PASS put’s together great things for us throughout the year like SQL Saturdays, which are put together by local PASS User Groups (click here to find the one in your area) groups that meet FREE monthly and have presentations on different SQL topics, The PASS Summit (Largest SQL Server conference in the world!), 24 Hours of PASS (free 12 spans of great training), and the PASS Virtual Chapters.  Virtual Chapters range 16 different subjects and 3 different languages, soon to be four different languages!

Joining PASS doesn’t cost you a dime, and I don’t get a penny for it, but it opens the door to a large amount of free technical content and training.  If you are not familiar I’d encourage you to click on the above links and become familiar with PASS today!

“So Balls”, you say, “What is this presentation you’re doing?”

Glad you asked Dear Reader!  I’m presenting for the DBA Virtual Chapter, 1 of the 16, and my subject is Trimming Indexes, Getting Your Database in Shape.

I’LL TAKE THE #2 SUPER SIZED

Here’s the abstract and then we’ll talk a little more:

“Indexes are a wonderful thing. We should be using them, and we should be maintaining them. But over time our production databases start to look a little pudgy around the mid-section. Maybe they are a little bloated with Unused Indexes, maybe they have Duplicate Indexes, and possibly even Reverse Indexes. The first step to fixing these problems it so see if you have them and if you do the second is to set about fixing them. You could be costing yourself CPU cycles, I/Op's, and space and never even know it.”

If you’ve been a DBA for a while you will inevitably inherit a system where you find indexes being used in less than optimal ways.  A lot of this is created by turn over in a company, going with all of the suggestions from DTA (Database Tuning Advisor), or having too many cooks in the kitchen.

It is possible to get things like Reverse Indexes, Duplicate Indexes, and unused Indexes.  You may be asking,  “What do those terms mean?  What secret ninja SQL Language are you speaking?  I know Clustered and Non-Clustered, but what-in-the-sam-hell is a REVERSE index!?”

It’s alright Dear Reader, no new secret terms.  A Duplicate index is just an Index where the physical structure exists more than once on a table.  Take the following Table:

CREATE TABLE Students(

          studentID int identity(1,1) primary key clustered

          ,ssn char(9)

          ,firstName varchar(50)

          ,middleInitial char(1)

          ,lastName varchar(100)

          ,gender char(1)

          )

If we created a Non-Clustered Index on the SSN column and we called it nclx_Students_SSN, and then someone else made a Non-Clustered Index on the SSN column and called it nclx_Students_SSN2 we would have a duplicated index.

“But Balls”, you say, “I would never do that!”

Of course not, you wouldn’t ever do that on purpose.   As code gets migrated from Dev to Production perhaps the Developer or Jr DBA adds an index that they didn’t realize you already has in place.  Or maybe you get a query plan with a “Missing Index hint” in Dev, only that index had been created as an urgent Production change, and never got implemented in Dev.  Migration comes around and as long as the names are different, WHAMO, you have two Non-Clustered Indexes on your SSN Column.

This example might not seem that bad, but imagine a 50 row table with a duplicate Non-Clustered Index on 5 rows, 10 rows, or 15 rows.  That’s a lot of extra data having to be persisted to disk and maintained.

"I wish my abs..I mean... databases where in shape"

Using the previous table let’s know make a Reverse Index.  We’ll create a Covering Non-Clustered index for a stored procedure that requires the SSN, FirstName, and LastName fields.  Some farther down the road you’ve left that company and a new developer is writing a different block of code and a different stored procedure and they create their Non-Clustered Index on LastName, FirstName, and SSN.

Then you have Unused Indexes.  These are the indexes that it seemed like a good idea to build, but nobody is using them.  In some places you have code that gets retired, but we still need the database structures,  in the Data Modeling phase Indexes were designed that were not used, or Database Tuning Advisor recommended it and it just wasn’t used.

Finding these are important because we are maintaining them, but the slackers do not contribute to our query performance. 

WRAP IT UP

So our goal for the hour will be to discuss Indexes make sure that we have a good foundation in them and what they store so we can understand why these 3 types of indexes are bad, and then use some scripts and DMV’s to identify them.

I'm also doing this presentation in a much longer format for SQL Live 360 in December of this year, as well as a couple more presentations.  Click on this link to check out Live 360!

I hope you’ll get a chance to stop by and join us!

Thanks Again,

Brad

Thank You PASS DBA Virtual Chapter

Yesterday I was able to do my first webcast, a presentation for the PASS DBA Virtual Chapter.   A Big Thank You to Idera for being the Webcast Sponsor!  Go check them out they are a great SQL Community Member, and a Sponsor of user groups and SQL Saturday Events.  I want to Thank everyone who was able to take time away from work, or during, to attend.  I would also like to thank Sharon & PASS for having me present.  Last but not least I would like to say a big Thank You to Mike Clark for being my Presenter, he made the experience super easy, and was a pleasure to work with!  Mike You rock!

So Dear Reader, the topic at hand was Compression.  I did my presentation that will be part of the up-coming SQL Rally, May 11th - May 13th, in Beautiful Orlando FL.  We have an amazing line-up of SQL Proffesionals throughout the industry that will be on hand.   And for the cost of less than $500 (if you sign up now and that is INCLUDING the Pre-Con)!

DECK & SCRIPTS

My Deck & Scripts were the same that I've used before but I wanted to post them for download, in case anyone would like to use compression.  I want them to have all the tools they need to get started.

Get my Slide Deck Here.

And all the Demo's Here.

QUESTIONS
A Question was asked after the presentation, forgive me if I butcher it, But the main idea was as follows.

 "I've compressed all of the tables in my database, my CPU is below 40%, are there any KNOWN issues with this?  And what do you recommend?"

There is always a case that will be the exception of the rule.  I would not recommend compression your entire database, just like I would not recommend compressing the entire contents of your C Drive.  Sure you'll save some space, but your performance will go down hill because of the overhead.

When you look through the Demo's there is an order that I like to suggest people go through.

1. Look at the Size of your Tables.
2. Look at the Makup of your Tables Allocation Unit's (Only IN_ROW_DATA Compresses)
3. Look at your Scan & Update pattern usage, this will help determine the type of Compression you should use.
4. GET A BASELINE!!! Look at your Table and/or Indexes Size, I/O's, CPU, and Runtime Before.  Then Look at them After.  Do a comparison.   If anything has changed for the negative, then perhaps you shouldn't be using Compression on that Table and/or Index.

It is possible that someone out there has a system that could benefit from every table being compressed, It's not outside of the realm of possibilities.  However, I would wager that would be a very Rare Scenario.

Compression is like Indexes, used properly it is a beautiful thing, but too much can tank your performance.

Thank's Again to everyone who could make the Presentation, I hope you are motivated to go to SQL RALLY!

Thanks,

Brad