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
Wednesday, October 24, 2012
Tuesday, October 23, 2012
PASS DBA Virtual Chapter Trimming Indexes, Getting Your Database in Shape
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
Thursday, October 18, 2012
DBA Study Guide
 |
| http://www.flickr.com/photos/caledonia09/4999119065/ |
Today’s market for DBA’s is quite good. If you are looking there are jobs out
there. A lot of the time after weeks or
months of interviews when the “ideal” candidate has not been found you tend to
lower the requirements. It’s the Animal
House “We need the dues” moment.
Only for a business it is we need a butt in the seat. You start asking the questions can we find
someone with the right attitude, someone who can learn, someone who might not
be at the level we want, but we can work with.
Often you can find a diamond in the rough and grow that person into the
experience level you wanted.
In the Consultant biz it’s a bit different. You can do that for Jr or Mid level jobs, but
Sr level positions require you to really know your stuff. You cannot expect a person to know
everything, and one person’s Sr is another person’s Jr. Not to mention there is a wide area of DBA
expertise to be considered. But we have
to draw a line in the sand, and Knowledge is very important.
Can you answer some of the following questions:
- What is a heap?
- What is a Clustered Index, a Non-Clustered Index, and what are the differences between the two?
- What is a Page Split?
- What is a Forwarding Pointer?
- Why do they matter?
If you cannot then I wanted to toss out some learning
resources that cover a wide breath of area.
This is similar to the Microsoft Certification exams where they say know
how to Baseline a server, couple different ways to skin that cat, so I know there are a LOT of different things to each very general area.
.jpg)
(*Note no
actual cats were skinned in the process of writing this blog).
This is just a collection of books that I’ve
read over the years. Some go in depth in
particular areas, some are general and cover many. My buddy Mike Davis (@MikeDavisSQL | Blog)
wrote a similar list for BI folks if you are interested in that click here to read more.
But I wanted to toss them out so if you are looking for a
good book you can find one. Just looking
to grow in a particular area? Then these
will help you as well.
Internals:
If you are looking for a book on Internals you cannot go wrong with
Kalen Delaney(@SQLQueen | Blog). The 2012
Internals book is due out in November, and I can’t wait to read it. This book has many wonderful contributors and
is well worth the money even though a new one is on the way out. I cannot recommend this book enough.
Internals/Extended
Events/Troubleshooting: Christian Bolton (@ChristianBolton |
Blog) put together an All-Star team for this book (a 2012 edition is due out
soon as well). It not only covers
internals but tools to diagnose them from some of the Premier experts in the
field. I put this neck and neck with any
book. If you work with SQL Server
2008/R2 you should own a copy.
Query Tuning:
Grant Fritchey (@GFritchey | Blog) is a damn nice guy. I don’t understand why people think he’s a
Scary DBA, (Grant thanks for the advice on the Katana collection and sharpening
swords in front of the daughter’s boyfriend before dates, priceless). I just
don’t understand the scary thing at all.
Regardless of his disposition Grant is the guy that wrote the book on
Query Tuning and Execution Plans. He is
a master in this field and the only people I would regard higher are the people
Grant would recommend.
Clustering:
Alan Hirt(@SQLHA | Blog) is to clustering what Grant Fritchey is to
Query Tuning. I’ve attended Alan’s
pre-con’s, read his books, and watched his generous and free advice via
#SQLHelp. If you are working in
clustering you should have Alan’s book it will point out best practices and
save you head ache’s (I’m looking at you government SOC’s Image when setting up
a 2008 Cluster).
Replication: I wanted to recommend a replication
book however, I haven’t purchased this one. My friend and co-worker Chad Churwell (@ChadChurchwell | Blog) is one of the smartest replication guys I've ever met and he recommends it. I’m making the recommendation because of Chad and I have done more replication as a Consultant that I did as a DBA. I’ve set it up, I’ve fixed it, I’ve learned
how to find out when it’s broken, what broke it, and why. I’d also bet I’m not alone. I’ve only read the free pre-view of the book
and chapter wise it summarizes everything I’m looking for an Expert in Replication
(other than experience).
Mirroring:
I would put Robert Davis(@SQLSoilder | Blog) in the realm of Mirroring
what Grant and Alan are to their respective books. Robert has blogged incredibly useful and real
world information about mirroring. AND
YES I understand that Always On Availability Groups are the way to go. However, not everybody is on SQL 2012, and a
solid understanding of Mirroring allows you to better understand all the
goodness that is Always On Availability Groups.
Hardware and Virtualization:
When it comes to hardware you don’t get much better than Glenn
Berry(@GlennAlanBerry | Blog). From his free
Assessment Scripts on SQL Server Performance (Glenn's is here) is essential when you go onto a new server for the
first time and try to holistically figure out what is going on right and
wrong. The first chapter alone taught me
more about CPU’s and which to choose than years of experience had. I was able to use this knowledge
immediately.
Performance Indexing: Jason Strate (@StrateSQL | Blog) and
Ted Krueger (@Onpnt | Blog) are incredibly smart guys. SQL MVP’s, years of experience, and deep
knowledge all combine to give you an answer to the age old question ‘What
should I index and Why?’. Indexing is a
core thing that DBA’s should know about.
Adding, removing, finding good ones, and identifying bad ones are
important. Not to mention the answer to
all of my previous questions are in this book.
SQL Server 2012/ SQL Azure/Powershell:
I work with some pretty smart guys.
SQL MVP’s, Consultants, and their friends are just as smart. These two books are a collabertaive effort
between brilliant people Adam Jorgensen
(@AJBigData | Blog), Brian Knight (@BrianKnight | Blog), Jorge Segarra (@SQLChicken |
Blog), Patrick Leblanc (@PatrickDBA | Blog), Aaron Nelson (@SQLVariant | Blog), Julie
Smith (@JulieChix | Blog)…And MORE (sorry for the people I left out)! If you are looking for information on SQL
2012 and how to use it go to the Bible and their other book on Professional
Administration.
WRAP IT UP!
A lot of books I know and no I don’t expect you to read all
of them before an interview, but there are a lot of common theme’s in the world
of SQL Server. A good expert should be
EXCITED about what they learn about.
They should be able to pick something tell me what they know, and I’d
like them to do it in a way that I’m excited about it by the time they finish.
I love going to SQL Saturday’s, PASS Events, and Conferences
because they make me excited to learn.
And I really love to learn. Find
something that you are passionate about, and learn it really good. That kind of learning and passion is
infectious and is exactly what makes all of the authors I’ve mentioned such
great SQL Server professionals.
Hopefully, whether you’re looking for a job or not, it will help you
find something that you love to learn about.
Thanks,
Brad
Subscribe to:
Posts (Atom)