SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding Indexes


Understanding Indexes

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86975 Visits: 45267
Jeff Moden (2/15/2011)
Since I'm not an expert on indexes that you good folks are, can you confirm that the bolded statements in the above quote are, depending on whether it's a covering index or not, incorrect... or not? I realize that data is stored in the index but that data is actually used if it's a covering index.


A nonclustered index does not just contain an address of the actual row. It contains the index key columns, any include columns defined and either the clustered index key or the RID, depending whether the base is a heap or a cluster. The clustered index key/RID can be considered the 'address' of the row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


zebulonpi
zebulonpi
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 34
Charles Kincaid (2/15/2011)Still the "blowtorch to the backside" was about the cleanest way that I could think of to state how this feels.


As far as FEELING goes, I'll give you that one. :-D

Still, one of the fastest ways to learn is to cover yourself with those heat-resistant tiles from the Space Shuttle and walk blithely into something like this. If you can stand the heat in the kitchen, you learn to cook VERY quickly.
Peter Trast
Peter Trast
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 655
zebulonpi (2/15/2011)
Charles Kincaid (2/15/2011)Still the "blowtorch to the backside" was about the cleanest way that I could think of to state how this feels.


As far as FEELING goes, I'll give you that one. :-D

Still, one of the fastest ways to learn is to cover yourself with those heat-resistant tiles from the Space Shuttle and walk blithely into something like this. If you can stand the heat in the kitchen, you learn to cook VERY quickly.


Sometimes I am even nervous about participating in the dialog Smile

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85577 Visits: 41082
Grant Fritchey (2/15/2011)
Jeff Moden (2/15/2011)
Grant Fritchey (2/15/2011)
Define a clustered index on the column that appears most frequently in the WHERE clause of SELECT
statements.


But not in JOIN criteria? No, no way. The basic rule for clusters should be the most frequently used access path to the data. This may be primary keys, this may be foreign keys, or it might be simply search criteria, but I wouldn't suggest limiting it to WHERE clauses.


Again, I'm not the "index Ninja" here, but I've also found that a table that suffers huge numbers of inserts can make very good use of a Clustered Index on an auto-numbering column such as an IDENTITY column or, perhaps, a date column to keep page splits to a reasonable level whether or not that column is the most frequently used in WHERE or JOIN criteria.

Have any of you good folks experienced the same or is there some other practice that folks use on a high insertion rate table?


I'm not an "index Niinja" either. I leave that to Gail, but in my experience and with my understanding, you're 100% right on here. Again, I like my own definition of "most frequently used access path" because that can apply to inserts as well as updates, deletes or selects. It doesn't matter. Since the cluster holds the data, you need to take advantage of that fact in whatever way is most advantageous to the system you're working on.


Thanks, Grant.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
zebulonpi
zebulonpi
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 34
Peter Trast (2/15/2011)Sometimes I am even nervous about participating in the dialog Smile


Try physically working with quite a few knowledgable SQL people, using a 'not like' against a 650 million record table in your 11 table joined query, and then asking said knowledgable people why the server was so slow today...

At least in the forums they can't physically choke you... :-D
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85577 Visits: 41082
GilaMonster (2/15/2011)
Jeff Moden (2/15/2011)
Since I'm not an expert on indexes that you good folks are, can you confirm that the bolded statements in the above quote are, depending on whether it's a covering index or not, incorrect... or not? I realize that data is stored in the index but that data is actually used if it's a covering index.


A nonclustered index does not just contain an address of the actual row. It contains the index key columns, any include columns defined and either the clustered index key or the RID, depending whether the base is a heap or a cluster. The clustered index key/RID can be considered the 'address' of the row.


Thanks, Gail. I guess it was just the way it was worded in the article. I thought I knew better (according to the possibly incorrect interpretation on my part) but needed to confirm it with someone that knew for sure. As usual, you difinitely came through on the clarification. :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
samuel_david_piper
samuel_david_piper
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
There are two points in the article that I wanted to respond to:

Avoid creating an index in response to the poor performance of a single query.


This needs to be fleshed out more - indexes are created primarily in response to poor query performance. The question is, which queries should be indexed? If a given query is ad hoc and never used by a calling application then there is no immediate benefit to indexing it, regardless of its performance. If the same query was then called hundreds of times a minute by different users then we should create indexes to improve the performance of that query.

Index optimization is a complex topic, and ultimately I don't think it can be reduced to blanket statements such as this one.

When queries against a table do not include a WHERE clause there is no benefit to using a
nonclustered index.


That's incorrect. For example, JOIN or ORDER BY conditions can benefit from a nonclustered index. Imagine a table of contacts where the most frequent query was:

SELECT ContactID, FirstName, LastName FROM Contact ORDER BY LastName ASC, FirstName ASC

If you only have a clustered index on the IDENTITY column then SQL Server will have to scan and sort the results for these two columns every time the query is executed. However if you define a nonclustered index on LastName ASC, FirstName ASC, then SQL Server will be able to read just that index to satisfy the entire query, without needing to do any sorting as this is already done whenever the index is updated.

This will scale particularly well as the table size increases as there is no increasing cost for sorting as there is when only a clustered index is available. There is also now support for seeking values in these columns more efficiently as well.

As others have said, any kind of data access path can potentially benefit from a nonclustered index, and the best way to know what those paths are is to look at the execution plan.

Also for a beginner article, there really needs to be a discussion of how to actually create an index in the first place, either via Management Studio or T-SQL.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search