In a heap..

Question

Post reply

In a heap..

terry999

SSCertifiable

Points: 5033
More actions
March 8, 2016 at 6:21 am

#313912

Hi
I have a table (lots of varchar columns > 200,000 rows) with No clustered index.
Its a transactional DB heavy on reads, less so on writes many more inserts than updates.
Never seen a table without one.
I think the PK (a varchar(12) field) is non clustered because records are Never added in PK order.
I can find no good Exisiting field(s) for a clustered index.
To experiment
I added an identity field and created a clustered index on it. The time taken to rebuild was ok.
I've compared times IO stats to do some queries in 3 cases.
1. as a heap
2. with clustered index on new identity field
3. after dropping clustered index
Adding the clustered index on an extra int field reduced the space used by the table.
After adding and dropping clustered the index defrag 50% --> 40%
There is no appreciable difference in query performance, after all I'm not using the clustered index.
Am I missing something? Adding the Clustered index on a field which takes part in no queries doesn't seem give me any performance improvement.
Thanks for looking at my post
Terry
Query I got from other articles to see fragmentation
SELECT
B.name AS TableName, C.name AS IndexName, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE B.name = 'mytable'
ORDER BY A.avg_fragmentation_in_percent desc

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It's not all about performance.

Heaps suffer from forwarded records[/url] and don't deal well with deletes.

If you see no significant difference between clustered index and heap, stick with a clustered index.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Performance gains on a clustered index are mostly going to come when that index is used to retrieve the data. While the approach of putting a cluster on the primary key or on an identity column (sometimes one & the same) are very common, they're not necessarily the best possible choice. Instead, I'd put it on the column (or columns) that are used most frequently to define the data retrieved from the table. Since the clustered index defines data storage, having it be the most common path to the data (frequently the primary key, but certainly not always) works out best.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

terry999 SSCertifiable Points: 5033 More actions · Answer 3

Thank you Gianluca

I checked the forwarded pages about 3% of total pages in the heap (a rebuild would help I think).

There are No deletes on this table.

Looks like I'll leave it as a heap with the occasional rebuild..

terry999 SSCertifiable Points: 5033 More actions · Answer 4

Maybe I should bite the bullet and make the PK (varchar(12)) a clustered index.

Inserts happen in random order with respect to PK values. So it would become fragmented quickly. A re-index doesn't take too long.

The one thing holding me back to TRY this was this PK is used as an FK in many tables, it would be a pain to turn non-clustered -->clustered.

Y.B. SSChampion Points: 11642 More actions · Answer 5

Is it a wide table? If not you could try including all columns on your non-clustered index. This would avoid key lookups and act similar to a clustered index. Be aware that it will of course cost you more disk space. Also make sure you select the most appropriate column(s) for your index key.

SELECT quote FROM brain WHERE original = 1
0 rows returned

terry999 SSCertifiable Points: 5033 More actions · Answer 6

yb751 (3/8/2016)
Is it a wide table?

Positively obese

Y.B. SSChampion Points: 11642 More actions · Answer 7

terry999 (3/8/2016)
yb751 (3/8/2016)
Is it a wide table?
Positively obese

Ok, that made me laugh. :hehe:

That being said I would not recommend it in that case.

SELECT quote FROM brain WHERE original = 1
0 rows returned

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

terry999 (3/8/2016)
Maybe I should bite the bullet and make the PK (varchar(12)) a clustered index.
Inserts happen in random order with respect to PK values. So it would become fragmented quickly. A re-index doesn't take too long.
The one thing holding me back to TRY this was this PK is used as an FK in many tables, it would be a pain to turn non-clustered -->clustered.

If you change your narrow single-column PK into a wide multi-column PK (with less rows per page) to support those FK's, you might accelerate some queries but you will slow down RI activity.

As Grant suggests, look at the queries running against this table and model your clustered index on the results of your assessment.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 100947 More actions · Answer 9

Look at the missing index stats, index usage stats and index operational stats. SQL itself will help you determine the best clustered index, which should be your goal.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.