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 «««678910

Introduction to Indexes: Part 3 – The nonclustered index Expand / Collapse
Author
Message
Posted Monday, July 23, 2012 6:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,639, Visits: 15,306
Nice series Gail.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1334156
Posted Saturday, June 8, 2013 4:00 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
This was so interesting :o went through it along with the simple-talk tracing articles & just finished (slow reader -_). I'm gonna read through your other articles tomorrow (or as far through as I can get) unless someone here has a good TV show to recommend a few questions, in the article you said:

GilaMonster (7/6/2012)
If SQL considers the index (or the subset of the index keys that the query would be seeking on) insufficiently selective then it is very likely that the index will be ignored and the query executed as a clustered index (table) scan.

It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column, with the other columns in the index making it selective enough to be used.


In these "scenarios" the result would then be a non-clustered full scan right?

GilaMonster (7/6/2012)
Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique)


So it will only allow you to seek on a clustering key if the key is unique?

GilaMonster (7/6/2012)
Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.


I guess the locks are only relevent for the creating rather than the dropping right? (since I guess SQL wouldn't attempt to make new queries use an index while its in the process of being dropped)

You also talked about the fillfactor & page splits in the thread and how 80% would cause more I/O reads as a result of there being more pages. How do you personally approach index fill factors? I guess the cost of excessive page splits & the desire to avoid them would outweigh the desire to minimise the number of pages (by settings 100%)?

Seems 2.5 months is inadequate study time for MSSQL...still learning about the fundamentals of indexes T_T



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1461310
Posted Sunday, June 9, 2013 3:58 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 @ 10:38 AM
Points: 42,806, Visits: 35,926
Dird (6/8/2013)
This was so interesting :o went through it along with the simple-talk tracing articles & just finished (slow reader -_). I'm gonna read through your other articles tomorrow (or as far through as I can get) unless someone here has a good TV show to recommend a few questions, in the article you said:

GilaMonster (7/6/2012)
If SQL considers the index (or the subset of the index keys that the query would be seeking on) insufficiently selective then it is very likely that the index will be ignored and the query executed as a clustered index (table) scan.

It is important to note that this does not just apply to the leading column. There are scenarios where a very unselective column can be used as the leading column, with the other columns in the index making it selective enough to be used.


In these "scenarios" the result would then be a non-clustered full scan right?


Scenarios when the leading column plus other columns make the index selective enough to use? No, that'll be an index seek.

GilaMonster (7/6/2012)
Sure, the clustering key will be there (though whether or not it can be used for seeks depends on a property of the index, specifically unique)


So it will only allow you to seek on a clustering key if the key is unique?


No. Seeks on the clustered index itself don't require the cluster to be unique. You can get a seek on a clustered index even if there's only one value in the entire table.

The part you're quoting pertains to a very specific situation with multiple columns in index.

GilaMonster (7/6/2012)
Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.


I guess the locks are only relevent for the creating rather than the dropping right? (since I guess SQL wouldn't attempt to make new queries use an index while its in the process of being dropped)


Dropping an index takes a schema modification lock, like any changes to database structures.

How do you personally approach index fill factors?


Evaluate on a per-index basis.



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

Add to briefcase «««678910

Permissions Expand / Collapse