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.
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 hope you’ll get a chance to stop by and join us!
Thanks Again,
Brad
