No Clustered Indexes - General SQL Database Question & Answer - LearnSqlServer.com's SQL Server Forums

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Database Administration » General SQL Database Question & Answer » No Clustered Indexes

No Clustered Indexes

Options

Previous Topic · Next Topic

wdismukes

Posted: Monday, April 28, 2008 3:30:38 PM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

I have inherited a 150+ GB SQL Server 2000 database with NO CLUSTERED INDEXES on any of the tables. The PK on most every table is a uniqueidentifier with a default value of newid(). All of the FKs are uniqueidentifiers. Most of the queries, procs, etc run well enough but the DB can use some tuning. There are enough inserts that I don't want to cluster the Uniqueidentifier PK. I can't make major changes to the DB design. I might be able to add an identity column to some of the larger tables and make this the clustered index. I don't know if this would be advantageous or not. Does anyone have any thoughts on this? Confused

Scott Whigham

Posted: Tuesday, April 29, 2008 7:34:09 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Hey Wayne

Now wait - you say you can't make any design changes; can you verify for me that you can in fact add indexes? Some people would consider that a design change is why I ask.

First, say goodbye to your little 150GB database and hello to your 200-250GB database when you add these indexes Smile

I don't know that I understand the usefulness/need for the identity column. Having the uniqueidentifier as your clustered PK is brutal though so I think that you are right in trying to find another spot to do your clustered index (otherwise you'll wind up with an insane amount of page splits). Natural keys are where I would look to add a clustered index (thus making the PKs non-clustered).

As for the non-clustered indexes, the first thing I would do is to identify all of the columns belonging to FKs. Those are the first candidates for indexes. This script can help identify all FKs not in an index: http://forums.learnsqlserver.com/SqlServerTopic18.aspx

That's the first place to start. I would be hesitant to plan too much beyond that until you know the effects these changes would have. Post back what you decide to do - this sounds interesting Smile

wdismukes

Posted: Tuesday, April 29, 2008 7:46:29 AM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

Thanks for the quick reply, Scott. I can add indexes and am looking at opportunities to create additional, useful indexes based on frequently run queries. Best I can tell, the FKs have indexes. I am still familiarizing myself with the DB. I will explore natural keys as a clustered index for some of the tables. Thanks for the script suggestion. I will keep you posted.

Scott Whigham

Posted: Thursday, May 01, 2008 6:29:41 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Oh, I misread your post and thought you had no non-clustered indexes. The stress of a 39 1/2 week pregnant wife and a 4yo who broke his elbow this week must've gotten to me!

Now that I've re-read your post, I still don't really see the identity column being a solution. Not all tables need a clustered index either so don't feel that you just have to find a way to implement one for every single table. Most tables would benefit from having a clustered index but not all. It might be helpful if you could post some details about 1-2 tables that you are wondering about. Also, have you run the Index Tuning Wizard (since this is SQL 2000) to see what it suggests?

wdismukes

Posted: Thursday, May 08, 2008 11:53:56 AM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

So.... are you a two time dad yet??? Congrats. I hope your son feels better. Remember, this too shall pass. But then so do kidney stones.

Thanks for the additional info. I ran tuning wizard on the "problem" queries. They are indexed properly. In fact, the PKs and FKs are all indexed on the large tables that I checked. I need to study the results of some profiles. I will probably be adding a file group since all of the data is currently stored on the D:\ drive. The log is on a separate drive. My big thrill last week was setting up a backup schedule that now includes differential backups!

wdismukes

Posted: Wednesday, June 04, 2008 9:47:58 AM

Rank: PFY

Joined: 5/3/2007
Posts: 20
Points: -37
Location: Houston, Texas

Just a quick follow up on this topic. As noted previously, the PK on most every major table in this DB is a uniqueidentifier with a default value of newid(). FKs are also uniqueidentifiers. The tables are adequately indexed except that there are no clustered indexes (except on one table). The one table with a clustered index on the PK is the table with the most inserts. This table is also a monster with two image fields. This one table is approximately 80% of the DB. My plan is to remove the PK clustered index from this table and replace it with a non-clustered index. I will test it out over the next couple of weeks to see the results.

Scott Whigham

Posted: Thursday, June 05, 2008 12:32:50 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

wdismukes wrote:

So.... are you a two time dad yet??? Congrats.

Yes I am

Wyatt Scott was born May 14 - he's a cutie Smile

http://www.whigham.org/baby/

Now, back to your situation, the truth is that most tables benefit from having a clustered index; the query optimizer often prefers using a clustered index over other techniques. There are some tables that a CI does not help and yours may be such a table. I think part of your plan should involve adding clustered indexes to the other tables first and then seeing the effects before you remove the CI from this big table. That would be (a) easier to do, and (b) you would immediately know whether it had a positive effect. It will also be easier to roll back!

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.