March 8, 2016 at 6:21 am
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
March 8, 2016 at 7:07 am
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
March 8, 2016 at 7:10 am
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
March 8, 2016 at 7:17 am
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..
March 8, 2016 at 7:21 am
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.
March 8, 2016 at 7:45 am
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.
March 8, 2016 at 7:59 am
yb751 (3/8/2016)
Is it a wide table?
Positively obese
March 8, 2016 at 8:17 am
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.
March 8, 2016 at 8:30 am
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.
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
March 8, 2016 at 11:32 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply