Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Understanding Indexes Expand / Collapse
Author
Message
Posted Monday, February 14, 2011 10:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 3:07 PM
Points: 65, Visits: 331
Comments posted to this topic are about the item Understanding Indexes
Post #1064010
Posted Monday, February 14, 2011 10:50 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1064015
Posted Tuesday, February 15, 2011 3:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:23 AM
Points: 112, Visits: 297
Plus, inserts in to a heap are not necessarily fastest:
http://support.microsoft.com/kb/297861/en-us
Post #1064128
Posted Tuesday, February 15, 2011 3:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:01 AM
Points: 70, Visits: 69
At this point of time, I am expecting more from this Article like Unique Index, Filtered Index etc..

but
Post #1064132
Posted Tuesday, February 15, 2011 5:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:00 AM
Points: 592, Visits: 1,425
# 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
Post #1064184
Posted Tuesday, February 15, 2011 5:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,661, Visits: 28,051
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1064188
Posted Tuesday, February 15, 2011 5:42 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:34 PM
Points: 15,661, Visits: 28,051
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1064190
Posted Tuesday, February 15, 2011 5:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:00 AM
Points: 592, Visits: 1,425
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
Post #1064192
Posted Tuesday, February 15, 2011 6:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #1064199
Posted Tuesday, February 15, 2011 7:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 8:00 AM
Points: 592, Visits: 1,425
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. :) )

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
Post #1064243
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse