Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding Indexes


Understanding Indexes

Author
Message
GreyBeard
GreyBeard
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 331
Comments posted to this topic are about the item Understanding Indexes
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47145 Visits: 44346
Two points I'm going to disagree with

Avoid the creation of an index on a column in a table when the data stored in that column has a large number of repeated data values are stored in the column.

Low density indexes can be useful, especially if they are covering indexes

If the database supports a transaction processing system, then avoid using indexes because the performance of the insertion of new transactions will be degraded.


Absolutely not. An OLTP system needs small numbers of well-chosen, effective indexes. None at all means terrible select, update and delete performance. The inserts may be fast, but I've never seen a write-only database.


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


hallidayd
hallidayd
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 297
Plus, inserts in to a heap are not necessarily fastest:
http://support.microsoft.com/kb/297861/en-us
amitchauhanrana
amitchauhanrana
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 91
At this point of time, I am expecting more from this Article like Unique Index, Filtered Index etc..

but Sad
Kiara
Kiara
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1426
# If the database supports a transaction processing system, then avoid using indexes because the
# performance of the insertion of new transactions will be degraded.


I disagree with this statement. I manage a reasonably good-sized OLTP system. I wouldn't even want to think about trying to manage it without good indexing.

Inserts might be a bit faster without indexes (I'm not going to argue that one too much, but some of my testing shows that inserts into heaps aren't always faster than inserts into indexed tables) - but in a multi-user system with applications and users requesting information from the same tables that those inserts are running against, the performance hits to NOT index those tables would be awful.

-Ki
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17529 Visits: 32252
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17529 Visits: 32252
Kiara (2/15/2011)
# If the database supports a transaction processing system, then avoid using indexes because the
# performance of the insertion of new transactions will be degraded.


I disagree with this statement. I manage a reasonably good-sized OLTP system. I wouldn't even want to think about trying to manage it without good indexing.

Inserts might be a bit faster without indexes (I'm not going to argue that one too much, but some of my testing shows that inserts into heaps aren't always faster than inserts into indexed tables) - but in a multi-user system with applications and users requesting information from the same tables that those inserts are running against, the performance hits to NOT index those tables would be awful.


Yeah, that's probably the most dangerous statement in the article. There are some excellent tests that show that heaps don't perform better for inserts. And they certainly don't for deletes or updates.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Kiara
Kiara
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1426
Yeah, that's probably the most dangerous statement in the article. There are some excellent tests that show that heaps don't perform better for inserts. And they certainly don't for deletes or updates.


I choked on my coffee when I read the OP's statement - enough to actually drop out of lurker mode and say something. :-) Considering the amount of time I spent when I started in this position dealing with index management to speed up database performance in an OLTP database, I was really, really surprised to see that in what appears to be an academic treatise.

-Ki
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47145 Visits: 44346
Kiara (2/15/2011)
Considering the amount of time I spent when I started in this position dealing with index management to speed up database performance in an OLTP database, I was really, really surprised to see that in what appears to be an academic treatise.


The academic nature of the article, along with the presentation as absolute fact were the two things that worried me. I do a lot of writing about indexes and I'm very careful to test anything that I claim and not to make absolute statements.
There's no figures, no indication that any testing was done, and several of the 'facts' are myths that I see repeated again and again.


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


Kiara
Kiara
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 1426
GilaMonster (2/15/2011)

The academic nature of the article, along with the presentation as absolute fact were the two things that worried me. I do a lot of writing about indexes and I'm very careful to test anything that I claim and not to make absolute statements.
There's no figures, no indication that any testing was done, and several of the 'facts' are myths that I see repeated again and again.


Completely agreed. Your posts are based in practice, testing, and experience - and you can back up what you write about. (Yes, I have a couple of them bookmarked. Smile )

I'd love to hear back from the OP on the actual reason that article was written and who the original intended audience was.

(Edited to fix formatting)

-Ki
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