| Author |
Topic |
|
VictorDBA
Starting Member
8 Posts |
 Posted - 2008-11-17 : 12:11:45
|
 Need Help!!!I currently have a SQL Server 2000 DB, which we are planing to upgrade to SQL Server 2005. The main issue is that all the tables within this database have Uniqueidentifiers (GUIDs) as the Primary Key and on top of that they are Clustered on GUIDs. Whats the best way out of this NIGHTMARE!! Can someone provide me with design solutions??Also I have one single table with 600 million rows, also clustered on GUID, I wish to move this table into a different database. What is the best way to move this huge table, about 50GB?Any suggestions/ solutions are welcome........ |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-17 : 13:07:14
|
| map all of them to identities. but that's provided your app doesn't need guid PK's for something..._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-17 : 13:12:33
|
It looks to me you are working for a power company in Malmö, Sweden  And using a software from a company in Gothenburg.The first issue is to remove clustering on GUID, use non-clustered index.Or, change NEWID() function in database to the new SEQUENTUALID() function.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-17 : 14:09:18
|
| You could add an int or bigint identity column to each table and make it the primary key clustered. This will prevent having randomly distributed rows in the tables and should reduce the index fragmentation on the primary key index. Make the GUID primary key columns unique constraints and all of your current foreign keys on the GUID primary key columns should still be OK.You could also replace all the GUID columns with integer identity columns, but you would probably have much more reprogramming to do.You don’t have to ask your questions in large type; we can read them anyway.CODO ERGO SUM |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-20 : 06:29:37
|
| Are you doing updates in the db? If not then simply leave it and try to migrate your data in PK order.The safest option is just to remove the clustered option on the index as Peso suggests. There is nothing to say that the PK or any other index has to be clustered. (I always find this obsession of having a clustered index by SQL Server gurus amusing). Same with rebuilding indexes. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-20 : 12:35:28
|
quote:
Originally posted by LoztInSpace
Are you doing updates in the db? If not then simply leave it and try to migrate your data in PK order.The safest option is just to remove the clustered option on the index as Peso suggests. There is nothing to say that the PK or any other index has to be clustered. (I always find this obsession of having a clustered index by SQL Server gurus amusing). Same with rebuilding indexes.
You can have serious performance problems if you do not have a clustered index on table, especially when you have a lot of inserts and deletes. When you don’t have a clustered index on a table, you have no way to recover unused space from the heap without completely reloading the table. I found a heap table that was over 4 GB in size but only had 100 rows, so table scans caused serious performance problems.In addition, indexes on a heap table will consume more disk space, and will take more resources to maintain.There are enough well known problems associated with tables without a clustered index that it is almost always a serious physical design error to not have a clustered index. CODO ERGO SUM |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-20 : 12:41:12
|
| I agree with MVJ. There will be lots of page splits and unordering pages causing internal and external fragmentation which will be impossible to maintain . Lots of pages have to be swapped back and forth to find data. LotsInspace,You should always have clustered index in table. |
|
|
|
VictorDBA
Starting Member
8 Posts |
Posted - 2008-11-21 : 14:45:27
|
| Newbie here <-- I apologize to everyone for my large fonts in previous post and delayed response to your posts!!Ok, since I have the apologies out of the way and taking all of your suggestions into considerations, here's my big plan:1) I plan on installing SQL 2000 on the Test Server and restore the most recent full backup of the production databases.2) Remove all clustered indexes from GUIDs column and create clustered index on some other column within that table(like Date column).3) GUIDs will be left as the Primary key and I might create Non-clustered index on the GUIDs column.4) Then plan on runing the upgrade advisor and eventually move the databses to SQL 2005.Does this sound like a good plan?? One more piece of info to consider:-- Currently we have 4 large DB in SQL 2000 production env.-- Developers have written a script to divide these SQL 2000 DB's in upto 350 smaller SQL 2005 databases.-- About 300 of these DB's will be read only, so plan to put them on seperate filegroups-- The rest of new DB's will be on read-write filegroupsAny suggestions here?? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-11-21 : 14:50:35
|
quote:
Originally posted by VictorDBA...GUIDs will be left as the Primary key and I might create Non-clustered index on the GUIDs column....
There is no reason to do that, because when you create a Primary Key constraint, it automatically creates an index on the PK.quote:
Originally posted by VictorDBA...-- Developers have written a script to divide these SQL 2000 DB's in upto 350 smaller SQL 2005 databases.-- About 300 of these DB's will be read only, so plan to put them on seperate filegroups...
I can't really comment on this without knowing the reason behind it, but it doesn't sound like a good plan to split 1 database into 350 databases, unless the original was a seriously flawed design. quote:
Originally posted by VictorDBA...1) I plan on installing SQL 2000 on the Test Server and restore the most recent full backup of the production databases.4) Then plan on runing the upgrade advisor and eventually move the databses to SQL 2005...
You should should do the upgrade to SQL Server 2005 in one step instead of two. It will be much less work.CODO ERGO SUM |
|
|
|
VictorDBA
Starting Member
8 Posts |
Posted - 2008-11-21 : 15:18:18
|
| Hello Michael,What do you mean by doing the upgrade in one step, please elaborate?Well to be honest, I just joined into this project, so I didn't have much to say with the designing. I think they want to split the DB's by the Clients they service. So each new DB will hold data for a particular client. So their reasoning is to detach the DB after each client contract is over and move it to archiving - I think that is a good Idea. So eventually after I am done with archiving, I should not have more than 30 - 40 small Db's in production. |
|
|
|
|
Eliminate GUIDs as Primary Key/Clustered Indexes