Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ron Johnson » Understanding Indexes
Add to briefcase
27 posts, Page 1 of 3
123»»»

Understanding Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GreyBeard
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: Monday, March 11, 2013 8:52 AM
Points: 65, Visits: 321
Comments posted to this topic are about the item Understanding Indexes
Post #1064010
GilaMonster
Posted Monday, February 14, 2011 10:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020
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
hallidayd
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: Sunday, March 03, 2013 2:50 AM
Points: 112, Visits: 294
Plus, inserts in to a heap are not necessarily fastest:
http://support.microsoft.com/kb/297861/en-us
Post #1064128
amitchauhanrana
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: Monday, June 11, 2012 5:12 AM
Points: 63, Visits: 59
At this point of time, I am expecting more from this Article like Unique Index, Filtered Index etc..

but
Post #1064132
Kiara
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: Wednesday, May 01, 2013 6:14 AM
Points: 592, Visits: 1,423
# 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
Grant Fritchey
Posted Tuesday, February 15, 2011 5:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187
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
Grant Fritchey
Posted Tuesday, February 15, 2011 5:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 13,383, Visits: 25,187
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
Kiara
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: Wednesday, May 01, 2013 6:14 AM
Points: 592, Visits: 1,423
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
GilaMonster
Posted Tuesday, February 15, 2011 6:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020
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
Kiara
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: Wednesday, May 01, 2013 6:14 AM
Points: 592, Visits: 1,423
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
27 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.