Everything you ever wanted to know about an index

Question

Post reply

Everything you ever wanted to know about an index

Adam Bean

One Orange Chip

Points: 26631
More actions
August 27, 2008 at 7:44 am

#154667

Hey all,
Been working on a new procedure and I'd like some feedback/review on it.
- Am I calculating the index size properly?
- Am I calculating the key size properly?
- Have a better way to populate the create index statements?
- Am I properly figuring out all the index create options?
- Any additional fields I should add?
Thanks all
(why can't we upload .sql files? renaming to .txt messes up the formatting ...)

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

This isn't really the place for this, and we don't want .SQLs up there for safety.

Adam, really you should submit this as a script, and there are comment sections for those.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Steve Jones - Editor (8/27/2008)
This isn't really the place for this, and we don't want .SQLs up there for safety.
Adam, really you should submit this as a script, and there are comment sections for those.

Understandable. I don't want to submit something until it's completely done though ... I've always found this board to be a great area for feedback and discussion and figured it was my best bet.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

More than anything I'd like to make sure I'm figuring out the sizes properly ...

Thanks all

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Adam,

I added one included column under one index in person.addresstype table in adventureworks table thn ut SP returned two rows for that index. Is that right?

Shldn't it report only one?

Just asking.

MJ

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

MANU (8/28/2008)
Adam,
I added one included column under one index in person.addresstype table in adventureworks table thn ut SP returned two rows for that index. Is that right?
Shldn't it report only one?
Just asking.
MJ

No, it shouldn't ... and until I added sys.columns and sys.index_columns, I had no problems with duplicate records; thus putting the DISTINCT in the select. I am hoping to figure out what I am doing wrong with my join to resolve this, but regardless after I added the DISTINCT, I have yet to see any duplicate records. Are you sure it's pulling back two records for the same index?

To anyone else, I definitely could use a hand trying to figure out my join and why I'm duplicating ... the only reason I pull those tables is to find the key size.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

Verify the same from attachment. There are two rows for IndexId=3.

MJ

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Yeah ... I'm doing something wrong with my join for the key size ... notice how your two rows, those are the only values that are different.

I'll work on it. Thanks for bringing this to my attention.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 8

Ok, uploaded a new version on first post ... I do believe all the dupes have been fixed.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 9

When I modified one index and included one more column in it under person.addresstype table your SP returned only one row

EXEC [IndexInfo] 'adventureworks'

See attached.

MJ

Adam Bean One Orange Chip Points: 26631 More actions · Answer 10

MANU (9/3/2008)
When I modified one index and included one more column in it under person.addresstype table your SP returned only one row
EXEC [IndexInfo] 'adventureworks'
See attached.
MJ

Well, that's still only one index ... what is the issue?

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 11

It is returning only one row for that person.addresstype table that too for clustered index. It is not returning any information about other two non-clustered indexes on it(previously it was returning that).

Also, I only ran the SP with Database name(EXEC [IndexInfo] 'adventureworks') as input and it returned just one row. Where are rows for other indexes present on other tables if I amrunning it for complete adventure works database.

Correct me if i am wrong somewhere.

MJ

Adam Bean One Orange Chip Points: 26631 More actions · Answer 12

hmmm, that is very odd ... for every database I've run it for, I have the correct amount of returns ... I have not used the adventureworks database, but I'll pull down a copy shortly and take a look.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

MANU, you are right ... this does not appear to work with the adventureworks database. Very very odd as I do not see a problem with any other database I've run it against, and the adventureworks database most definitely has plenty of indexes.

Not exactly sure what the problem is yet, but I'll figure it out.

Thanks for bringing this to my attention.

--- So ... now I'm noticing that my proc doesn't work on the same database, in different environments. All SQL2005, all the same database options ... same tables, same indexes, but some servers don't return any results. This is driving me nuts ... not sure what is causing it.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 14

Fixed the problem. sys.dm_db_index_usage_stats doesn't get populated with the index information until the index is accessed in some fashion. Changed from an inner join to a left outer join.

Re uploaded new script to original post.

Thanks for the find!