November 24, 2013 at 4:06 am
Hi
I have a table with a column "id" and it's type is uniqueidentifier and this field is clustered Index,the fragmentation on this table is high and the counter reads and cpu is also high .
I want to set the default newsequentialid() for this column.
Can I do this change and is it Ok ? What about the old data in id column?Does The new data have conflict with the old?
November 24, 2013 at 6:05 am
It's the same data type. No idea if it'll even work. If you specify a default and the app explicitly inserts a value, the default is ignored.
Better option would be to leave that column as-is, change the primary key to nonclustered and put the clustered index somewhere else.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
November 24, 2013 at 6:24 am
Why should it ignore the default?You mean again it inserts a random data not bigger one?
My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.
how can I put the clustered index somewhere else?
As I searched I read these links for bad performance with uniqueidentifier as a clustered Index:
http://msdn.microsoft.com/en-us/library/ms189786.aspx
http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx
November 24, 2013 at 12:08 pm
mah_j (11/24/2013)
Why should it ignore the default?You mean again it inserts a random data not bigger one?
If an insert explicitly specifies a value for a column that has a default, then the explicitly specified value will be inserted not the default.
If you're replacing a default of NEWID() with a default of NEWSEQUENTIALID(), your plan will probably work. If the value for the uniqueidentifier is set in the procedure/application that inserts, then adding a default will do nothing.
My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.
how can I put the clustered index somewhere else?
Well, you didn't say that in your initial post and I wouldn't have guessed it was a two column table
As I searched I read these links for bad performance with uniqueidentifier as a clustered Index:
http://msdn.microsoft.com/en-us/library/ms189786.aspx
http://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx%5B/quote%5D
Yes, I agree, uniqueidentifiers are terrible choices for a clustered index.
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
November 24, 2013 at 1:15 pm
mah_j (11/24/2013)
My table just has 2 fields : id that is uniqueidentifier and a nvarchar() it is about 1G and has 525400 rows.how can I put the clustered index somewhere else?
Add an identity column and set it as the clustered index. You can leave the uniqueidentifier as the primary key, if you want, but make it nonclustered.
November 24, 2013 at 11:37 pm
GilaMonster (11/24/2013)
If an insert explicitly specifies a value for a column that has a default, then the explicitly specified value will be inserted not the default.
If you're replacing a default of NEWID() with a default of NEWSEQUENTIALID(), your plan will probably work. If the value for the uniqueidentifier is set in the procedure/application that inserts, then adding a default will do nothing.
Now this field does not have a default of NEWID(),and the value for this column is generated automatically,not in the application,so as I understood from your reply, if I set the default to NEWSEQUENTIALID(),the value that is generated will be bigger and unique In this case.
November 24, 2013 at 11:42 pm
Robert Davis (11/24/2013)
Add an identity column and set it as the clustered index. You can leave the uniqueidentifier as the primary key, if you want, but make it nonclustered.
thanks Yes this is another way to get rid of this problem,but it requires changing the the structure of the table,I would do this as a last solution.
November 25, 2013 at 1:19 am
mah_j (11/24/2013)
Now this field does not have a default of NEWID(),and the value for this column is generated automatically,not in the application,so as I understood from your reply, if I set the default to NEWSEQUENTIALID(),the value that is generated will be bigger and unique In this case.
No, I said exactly the opposite.
If the column DID have a default of NewID and you just replaced that existing default with NewSequentialID, then this plan would probably work.
Since the column does not have a default of NewID, the uniqueidentifier will be generated either in the app or in the insert statement. As such, the default will be ignored and the uniqueidentifier inserted will NOT be larger and you will still have all your performance problems
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply