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


NON-Clustered Indexes on table but no Clustered Indexes


NON-Clustered Indexes on table but no Clustered Indexes

Author
Message
Mikey01
Mikey01
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 204
I've been asked to analyse a process that uses alot of temporary tables to identify any potential index improvements. These tables are relatively small the largest maybe reaching 10000 records, but majority never get past 1000 records...

I took all the stored procs and canabailised them so the Temp tables were now Permanent tables ......

I then ran the proccess and started analysing the results from the Index DMV's.....

It was at this time i started looking at existing index's on tables created within this process and i found that none of the tables had Clustered index's but many had non-clustered index's, sometimes multiple Clustered Index's ....

Does anyone have an opinion on this i.e. when would this be an acceptable approach to implement?

Also whats the underlying cost of using a non-clustered index when no clustered index exists, since non-clustered indexes are built on the clustered index, how does this relate to when we use a heap?

I shoud point out the tables are loaded once then updated many times and queired many times before being dropped...
Mohit K. Gupta
Mohit K. Gupta
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 Visits: 1089
On a small table you will not notice much improvement; but general recommendation is you should have cluster indexes on every table.

It was at this time i started looking at existing index's on tables created within this process and i found that none of the tables had Clustered index's but many had non-clustered index's, sometimes multiple Clustered Index's ....


A table can only have ONE clustered index it cannot have multiple clustered indexes because cluster indexes dictate how the rows are sorted on the physical level.

If you are just using non-clustered indexes and heap; it will first cause a RID Lookup anytime you access anything in that table via a Index Seek. That is when an index seek operation completes it will have to go back to main heap to get the data. In addition when you are inserting, deleting, and updating records in the table the heap can become fragmented and there is no way to defrag it because of how heap are stored. So the access speed, updates, and deletes can take long time.

These temporary tables you changed to permanent tables; do these all get destroyed and recreated every time?

Also becareful about using the Index DMV they are only good as of last time you restarted your SQL Serer. Also they can lead you wrong if your statistics are out of date.

Thanks.
Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
Where are the performance improvements needed? During selects, updates and/or the table load?
Mikey01
Mikey01
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 204
Firstly these Perm tables are created where before the temp tables were created within the process purely so i could conduct my analysis i.e capture the stats..... I should point out the process creates over 150 tables in total.... and is probably spread across a couple 100,000 lines of SQL & 250 stored procs

That means every time before i run the process i must drop the tables...

Thanks for the heads up in relation to the DMV stats being reset .. i've used these before so i'm aware of the stats being reset under certain scenarios...

In terms of the performance improvement i'm looking for, "See if there is any improvement to be made from new or modifying indexes" was my brief Smile

I have since found out why only Non-Clustered indexes were created i spoke with some of the current Dev team they said thev'e never modified the Index's on the temp tables... then one Dev asked me what type of index is created by the CREATE INDEX statement and then the penny dropped ... the original developer used the CREATE INDEX statement which creates NON_Clustered indexes by default....
Mohit K. Gupta
Mohit K. Gupta
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 Visits: 1089
Not surpirsed Mike, I find most people don't even know what a cluster index is. I remeber getting an answer from someone at one time "we are not running clusteres, so we can't use cluster index!" ... I was hmm okay!

I walked away :-).

But anyhow, that sounds like one hell of a process to deal with 150 temp tables and such. I would strongly recommend adding clustered indexes to the chart; but also I would recommend to control the number of non-clustered indexes. Because these table mostly likely be changed frequently you don't want to burden the system with having to update too many non-clustered indexes specially for batch imports like SELECT * INTO #Temp...

Thanks.
Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
Mikey01
Mikey01
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 204
My Perf testing is advancing along i have now removed all indexs and would you believe the DMV's are only suggesting a fraction of the indexes ......

This process is limited to proccess a certain amount of units at a time so i know that if i go to the max allowed amount of through put and the DMV's are still saying these existing indexes won't be of use then i can be pretty confident to remove them...

Of course the base line test will be the final confirmantion of this task but all in all its not been a bad days work Smile

Let the weekend begin I'm off home..


Thansk for the help today
Mohit K. Gupta
Mohit K. Gupta
SSC Eights!
SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)SSC Eights! (970 reputation)

Group: General Forum Members
Points: 970 Visits: 1089
One note about the DMV for Indexes ...

I was talking to Microsoft Tier-3 Support guy about SQL Server; he suggested using the Dash Board there is a 90%+ improvement there will definetly be improvement. He said to take care for values below 80... but for above 90 you should consider creating the index.

SELECT   migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) AS improvement_measure
,'CREATE INDEX [missing_index_'
+ CONVERT (varchar, mig.index_group_handle) + '_'
+ CONVERT (varchar, mid.index_handle) + '_'
+ LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON '
+ mid.statement + ' (' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')'
+ ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
,migs.*
,mid.database_id
,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks
+ migs.user_scans ) DESC



I got that code from below link ... Thanks.

Ref: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth


How to ask for help .. Read Best Practices here.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36184 Visits: 18751
Interesting debate here, anyone want to turn some testing into an article?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
I've put together a very basic article for a beginner audience on setting up the performance dashboard that I created as I set it up in my environment. I've submitted to articles@sqlservercentral.com.....Look forward to your feedback....!
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: 47297 Visits: 44392
Mohit (5/1/2009)
I was talking to Microsoft Tier-3 Support guy about SQL Server; he suggested using the Dash Board there is a 90%+ improvement there will definetly be improvement. He said to take care for values below 80... but for above 90 you should consider creating the index.


Few things to note though.

The logic that goes into an index being suggested in the missing index DMV is a subset of what DTA does.
The recommendations are done individually for queries (as they are optimised) and not for an entire workload
Missing indexes will never suggest clustered vs nonclustered.
If will never suggest modifying an existing index or dropping an existing one.
It won't check for similar indexes in the DMV already or similar ones that already exist.

Say you have an existing index on 2 columns - (a,b) however you have two queries (heavily used) that either filter on a, b and c or filter on a and b and select c.
Missing indexes DMV may very well suggest 2 new indexes, one on (a,b,c) and one on a,b include (c). If you create both of those, you've got 2 redundant indexes.


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


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