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


Introduction to Indexes: Part 3 – The nonclustered index


Introduction to Indexes: Part 3 – The nonclustered index

Author
Message
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4712 Visits: 2741
deroby (7/6/2012)
Hi Gail, another thanks for the series!

We've always used the 'rule' that every table needs a clustered index and I've never really challenged that. If the PK is lean enough, we use that, otherwise we put an IDENTITY(int) column to the table and take that... a simple no-brainer in most cases.

However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only.
Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??



Well, if your UPDATEs sometimes make, say, VAR* columns larger, then you're likely to see table fragmentation, which is best fixed by normal index maintenance on the clustered index.

I would say that the ability to do index maintenance is another very solid argument for "everything gets a clustered index".
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234872 Visits: 46376
deroby (7/6/2012)

However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only.
Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??


Most likely no. SQL is optimised for tables having clustered indexes. While a RID lookup will read less pages than a key lookup, the lookup is still a horridly slow operation that you want to avoid most of the time.

I agree that if queries attack the table using the clustered index keys directly the lack of a bookmark lookup makes them 'winners'. But in our case we have at least 2 other totally different approaches in accessing the same table and then those need to go via a rather expensive bookmark lookup now... making it a heap would make every approach more or less equally good/bad...


Why does two approaches automatically mean lookups?
Even if it did, making the table a heap would probably make both equally bad, which is probably not the optimisation strategy I would favour.

The only reason NOT to work like this would be that whenever a table-change affects a record to move from one page to another this would affect all the leaf-nodes of every index ...


When rows are moved in a heap (typically when they are updated and grow in size) they leave forwarding pointers behind, the nonclustered indexes aren't updated because that would be expensive.

Forwarding pointers make reads of the heap more expensive than it would be othewise.

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


Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10653 Visits: 5158
Hi Gail

I want to thank you for your effort on indexes.

I'll use this opportunity to ask a question.
Example: There is a Table with one clustered index and some non-clustered. It is connected to more other tables.
Then I create non-clustered index(es) on the table, in order to execute some queries faster, and then i want to drop the non-clustered index(es), because some other actions to that table may change.

Is it a reasonable practice?, won't it change something? Do i need to rebuild the previously existed indexes?, or it is not needed.

Thanks
IgorMi

Igor Micev,
My blog: www.igormicev.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234872 Visits: 46376
I don't understand what you're asking.

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


Divine Flame
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4549 Visits: 2816
IgorMi (7/20/2012)
Hi Gail

I want to thank you for your effort on indexes.

I'll use this opportunity to ask a question.
Example: There is a Table with one clustered index and some non-clustered. It is connected to more other tables.
Then I create non-clustered index(es) on the table, in order to execute some queries faster, and then i want to drop the non-clustered index(es), because some other actions to that table may change.

Is it a reasonable practice?, won't it change something? Do i need to rebuild the previously existed indexes?, or it is not needed.

Thanks
IgorMi


I think you want to know that whether deleting few indexes on a table can make other indexes (on the same table) fragmented?

If this was your question, i think the answer is NO. Deleting any non-clustered index on a table doesn't increase the fragmentation of other indexes.


Sujeet Singh
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10653 Visits: 5158
GilaMonster (7/20/2012)
I don't understand what you're asking.


My question is the following:

there is an existing table with lets say 5 indexes(1 clustered + 4 nonclustered).
The Estimated Execution Plan of SSMS, for a particular stored procedure or query, suggests to add 3 more nonclustered indexes (for the same table), and I add them just for my SP to execute faster.

After that I don't need the last 3 indexes. The questions is: Is it enough just to drop those 3 indexes, or i have to drop them and additionally rebuild the old (the 5 indexes) indexes?

Regards
IgorMI

Igor Micev,
My blog: www.igormicev.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234872 Visits: 46376
Don't do that. Waste of time. It is highly, exceedingly unlikely that it's faster to create all the indexes then run the query then drop the indexes than to just run the query. Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.

Do your analysis, if you need the indexes, create them permanently. If you don't, don't create them.

The only place you typically create and drop indexes frequently is data warehouses. Drop/disable indexes before the data load, recreate/rebuild afterwards.

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


Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10653 Visits: 5158
GilaMonster (7/20/2012)
Don't do that. Waste of time. It is highly, exceedingly unlikely that it's faster to create all the indexes then run the query then drop the indexes than to just run the query. Plus creating and dropping indexes will cause plan recompilation, extensive locks, etc.

Do your analysis, if you need the indexes, create them permanently. If you don't, don't create them.

The only place you typically create and drop indexes frequently is data warehouses. Drop/disable indexes before the data load, recreate/rebuild afterwards.


Thanks for this!

One of my last tasks was to make a SP execute faster. I run the Estimated Execution Plan of SSMS and it suggested creating 3 new indexes. The tables were quite big (about 270 million records). The SP was lasting 'infinite' and after creation of indexes it finished for an 1.5h. But additionally I replaced the passing arguments of the nested functions and SPs (called from the main SP) with local variables and then use them through the code, so my doubt here is whether i reached the goal with the new indexes or with the replacement of the passing arguments of all the nested SPs and FNs. I read somewhere in BOL for the passing arguments to be replaced with local variables when nesting.
I haven't dropped the indexes yet. On one table they increased up to 8 indexes. Because the server is for replications, i decided to do that.
What is your suggestion in this situation? Is it smart to tell the admins to disable those indexes when loading new data? if they never disable them. I'm not sure whether they disable them whenever they load data. The admins are not on my local site, that is why i haven't asked them yet, but I would.

Thanks in advance
IgorMi

Igor Micev,
My blog: www.igormicev.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)SSC Guru (234K reputation)

Group: General Forum Members
Points: 234872 Visits: 46376
Hard to say without detailed analysis.

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


Solimaredad24
Solimaredad24
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 0
Calypso Bay Jacobs Well Land Estate Reviews - Roche Group - Luxury Real Estate located between Brisbane and the Gold Coast in Queensland,

Australia.


calypso bay
calypso bay
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