NON-Clustered Indexes on table but no Clustered Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » NON-Clustered Indexes on table but no...

19 posts, Page 1 of 2

1 2 »»

NON-Clustered Indexes on table but no Clustered Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

Mikey01

Posted Friday, May 01, 2009 5:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 7:23 AM
Points: 178, Visits: 133

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...

Post #708345

Mohit K. Gupta

Posted Friday, May 01, 2009 8:03 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 941, Visits: 1,041

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.

Post #708467

Bob Griffin

Posted Friday, May 01, 2009 8:26 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 1,132, Visits: 663

Where are the performance improvements needed? During selects, updates and/or the table load?

Post #708482

Mikey01

Posted Friday, May 01, 2009 9:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 7:23 AM
Points: 178, Visits: 133

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 :)

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....

Post #708551

Mohit K. Gupta

Posted Friday, May 01, 2009 11:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 941, Visits: 1,041

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 Smile

.

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.

Post #708600

Mikey01

Posted Friday, May 01, 2009 11:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 14, 2013 7:23 AM
Points: 178, Visits: 133

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 :)

Let the weekend begin I'm off home..

Thansk for the help today

Post #708622

Mohit K. Gupta

Posted Friday, May 01, 2009 3:09 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:37 PM
Points: 941, Visits: 1,041

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.

Post #708795

Steve Jones - SSC Editor

Posted Friday, May 01, 2009 3:48 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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

Post #708809

Bob Griffin

Posted Monday, May 04, 2009 10:13 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:26 PM
Points: 1,132, Visits: 663

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....!

Post #709485

GilaMonster

Posted Monday, May 04, 2009 11:01 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 37,635, Visits: 29,886

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 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 #709523

« Prev Topic | Next Topic »

19 posts, Page 1 of 2

1 2 »»

Permissions