March 18, 2009 at 9:42 am
let me ask in this way: if I query sys.dm_db_index_physical_stats and get the same object_id for a heap and an index is an abnormal situation?
Because I will paste some results and show you that I have this situation
I have indexes on the table, I don't know if they were created later,and data was inserted in this time, but afaik this doesn't maters. I understand what you are sayng but I have this situation...
As I said before there is a chance to understand wrong all the information received, but I don't think so...
March 18, 2009 at 9:50 am
shnex (3/18/2009)
let me ask in this way: if I query sys.dm_db_index_physical_stats and get the same object_id for a heap and an index is an abnormal situation?
No.
The object_id is the id of the table that the index is on. Indexes don't have their own object_id. If I have 1 clustered index and 4 nonclustered indexes on a single table, all five index entries will have the same object_id and different index_id's. The cluster will be index_id 1 (because a cluster is always index id 1) and the others will have higher index id's (probable 2,3,4 and 5)
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
March 18, 2009 at 9:56 am
If you are looking to change the code you posted towards the beginning of the thread, selecting only index_id = 1 will have the same result as your code but will ignore the heaps which don't rebuild anyway.
You can't rebuild a heap unless you put a clustered index on it.
You could enhance your code further by rebuilding each individual index, to do this you would use the index_id as Gail has descrbed.
.
March 18, 2009 at 10:10 am
Yes, is normal for indexes, clustered and non-clustered, but if I have a heap and a clustered index for the same object_id.This shouldn't be normal, if I folow what you say, but I have this situation.
And for my script....yes I could change it, but "it works" like this....I will try the way you suggested too.
March 18, 2009 at 10:13 am
shnex (3/18/2009)
I have a heap and a clustered index for the same object_id.
That's not possible. How are you identifying that you have a heap and a cluster for that table?
What's the result of this run for that table?
SELECT object_id, index_id, name from sys.indexes where object_name(object_id) = 'TableInQuestion'
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
March 18, 2009 at 10:27 am
So
the result from sys.dm_db_index_physical_stats :
4824148798901HEAPIN_ROW_DATA1093,36099585062242267,446902654867261683NULLNULLNULLNULLNULLNULLNULLNULL
4824148798901HEAPLOB_DATA100NULLNULL463NULLNULLNULLNULLNULLNULLNULLNULL
48241487989111NONCLUSTERED INDEXIN_ROW_DATA203,404255319148942210,6818181818182235NULLNULLNULLNULLNULLNULLNULLNULL
48241487989251NONCLUSTERED INDEXIN_ROW_DATA201,271186440677971219,6666666666667236NULLNULLNULLNULLNULLNULLNULLNULL
The result from you query :
2414879890NULL
24148798911Pk_Mt_Anag
24148798925ix_pratica_id
March 18, 2009 at 10:49 am
I found the problem, I think
The Pk-indexes are non-clustered,and that's why there are heaps. I don't know if this is ok, or if the person that was here before me had a purpose, but I have again a situation....
Afaik non-clustered indexes are better then the clusterd ,but they rely on the clustered ones. I'm asking again for your advice : what should I do? Should I make the pk index clustered?
I think this is the best solution
Thanks for the replies, I think I was annoying:-D, just a little 🙂
P.S.: Most of my indexes, especially the clustered ones are on guids.I had a topic here some time ago on this subject ,so I'm thinking this might have been a reason for the dba to make the index non-clustered...who knows...
March 18, 2009 at 10:56 am
shnex (3/18/2009)
I found the problem, I thinkThe Pk-indexes are non-clustered,and that's why there are heaps. I don't know if this is ok, or if the person that was here before me had a purpose, but I have again a situation....
Afaik non-clustered indexes are better then the clusterd ,but they rely on the clustered ones. I'm asking again for your advice : what should I do? Should I make the pk index clustered?
I think this is the best solution
Thanks for the replies, I think I was annoying:-D, just a little 🙂
It's usual to use a clustered Primary key. To do this properly create it as a proper primary key using ALTER TABLE rather than just a unique index.
Remember that for a big table this might take a while unless you use Enterprise Edition and use ONLINE = ON
Don't forget to drop your old 'primary key' (index) Pk_Mt_Anag if you replace it with a clustered index with the same columns.
.
March 18, 2009 at 10:57 am
Sorry and for clarity you should always test any schema changes like this before implementing them on a live system!
.
March 18, 2009 at 11:04 am
ok...
But I observe smth on the db...all the big tables don't have a clustered index on PK. Is this a technique, or something? Because I don't think this is a mistake....
As I said before, my pk are 99% uniqueidentifiers, so I think this might be the reason...because the insert and update will be slower...but they have a non-clustered index. Isn't this the same thing?
What do you think?
Again thank you for helping me solving my pb
March 18, 2009 at 11:14 am
You don't need to cluster on the PK. Generally, the best place for the cluster is on the most frequently used access path. More often than not, this is the PK. But nothing is saying it has to be.
Let's say you have a table that has a PK and an FK to a parent table. But the PK is never used for retrieving the data. The data is only ever joined to the parent table and then pulled back. I'd suggest moving the clustered index from the PK to the FK in that situation.
There isn't a perfect, hard and fast rule. There are just guidelines that lead to the right place. The thing to keep in mind is that nonclustered indexes don't store the data. So to get the data they either have to do a row lookup on a heap or a key lookup on a clustered index. Guess which one is faster in the vast majority of situations.
"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 18, 2009 at 11:21 am
A good rule of thumb is that you should always have a clustered index. There are very limited circumstances where this is not such a good idea but they are few and far between.
Without knowing your data model I can't comment on whether the clustered index is missing by design.
Grant has written a good summary about how to decide what to use, but if you are not sure, I would say that clustering your primary key is probably going to be better than not clustering anything at all.
Test it and see...
.
March 18, 2009 at 11:22 am
The guids in the pk are generally used for joins...so I have to change the index in a clustered one...
I just don't know why they are created this way....I don't want to mess up the logic of the db.
Anyway, got the point. Thanks
March 18, 2009 at 1:42 pm
shnex (3/18/2009)
The guids in the pk are generally used for joins...so I have to change the index in a clustered one...
No. Not good. Definitely not good.
Guids are a terrible choice for a clustered index, they fragment exceedingly fast. That's probably why the previous DBA made the pk nonclustered (good move). The table should get a cluster, but in this case, not the pk. Without seeing the schema, can't say where will be better.
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
March 18, 2009 at 2:40 pm
Unless, by chance, you're using the ordered GUID. It still fragments, but not as much.
"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
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply