March 11, 2013 at 12:28 am
Hi
I want to change the order of two fields in Pk of a table,I need to drop it and recreate
it,but it takes a lot of time in my test server,how can I speed it up?
Can I use MaxDop = Number of processors in main server?
My table is about 400G.
March 11, 2013 at 3:13 am
Creating a clustered index does take a lot of time, you're recreating the table.
Drop all nonclustered indexes before you start, put them back once you're done, that'll save a bit of time.
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 11, 2013 at 3:22 am
You can also create new clustered index with DROP_EXISTING option. I guess it's equivalent to dropping and re-creating non-clustered indexes manually. However, if index you're going to drop is participating in FK-relationships, you will need to deal with them too...
March 11, 2013 at 3:24 am
Eugene Elutin (3/11/2013)
You can also create new clustered index with DROP_EXISTING option, except it will happen in one transaction, so it may not safe too much of time. I guess it's equivalent to dropping and re-creating non-clustered indexes manually. However, if index you're going to drop is participating in FK-relationships, you will need to deal with them too...
March 11, 2013 at 5:06 am
mah_j (3/11/2013)
Can I use MaxDop = Number of processors in main server?
yes you can try is it but first at test server and see how uch benefit you are getting ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 11, 2013 at 5:44 am
mah_j (3/11/2013)
Can I use MaxDop = Number of processors in main server?
Unless the server maxdop setting has been set to something other than the default (0), that won't have any effect. It specifies the max number of processors to be used, not the actual number that must be used.
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 11, 2013 at 6:35 am
The server maxdop setting is set to 0.
I am testing Create it at test server with MaxDop and SORT_IN_TEMPDB = ON,and also as Gila said frist I will drop all the nonclustered indexes (Is it because of rebuilding all of nonclustered indexes ? )
March 11, 2013 at 7:12 am
If server maxdop is 0 then it's pointless specifying maxdop on the index unless you want to reduce the number of processors it is allowed to use.
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 12, 2013 at 1:17 am
So with server maxdop =0, the CREATE INDEX operation is being processed in parallel certainly even without MaxDop option?
I want it to process the creation in parallel.
March 12, 2013 at 3:03 am
With server maxdop 0, SQL has the option of using up to all of the processors for any operation (including the index rebuild). If you specify maxdop = processor count on the index operation, then SQL has the option of using up to all of the processors for that index rebuild (ie, no difference from if you didn't specify it)
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 12, 2013 at 10:54 pm
I tried without Non_Clustered and this time it took less time.
Is there any way to have more less down time for example some thing like redefinition in Oracle that I can have a table with new structure and have it synced with the old one?
March 12, 2013 at 11:07 pm
mah_j (3/12/2013)
I tried without Non_Clustered and this time it took less time.Is there any way to have more less down time for example some thing like redefinition in Oracle that I can have a table with new structure and have it synced with the old one?
YOU can do :
1) create another table with same defintion but new required PK .
2) with the help of batch process upload the data in new table from old table.
3) rename the old table (keep for future reference) and reaname the New table with the table's name.
4) now new table will be used as Main table.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 12, 2013 at 11:24 pm
What do you mean by "batch process"?My table is about 400G .I used bulk copy for having a test table on another server and it took me a lot of time for both bcp out and bcp in.
Also I want to have minimum down time on the server .
March 13, 2013 at 4:55 am
If this is a Primary Key, then it is by definition a unique key. You need to consider your reasons for re-ordering the columns in this key, as this work will make no difference to the functionality provided by the table.
When you are deciding column order, it is best to make the column that has the greatest range of values (also known as having the highest cardinality) as the first column in the index. This applies to any index formed of more than one column.
The reason for this is that SQL server works out how effective the index will be at filtering the results by first looking at the distribution statistics for the first column of the index. If this has a low number of unique values then SQL Server may decide the index is not worth using.
If you want results to be sorted in a different order to the primary key, then create another index with the columns in the order you need. However be aware that if the first column in that index has a low cardinality it may never get used by SQL Server.
If you are concerned about the order of columns in your result sets, then best practice is to specify a list of columns in the desired sequence in the query. If you are relying on SELECT * functionality (bad for all sorts of reasons) then at least do your SELECT * from a view where the columns are in the sequence you want.
Having considered the impact of all of this, are you cetain you need to change the order of the columns in your Primary Key?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 13, 2013 at 5:33 am
We have a lot of time out in insert and select statement ,I checked the reason of this problem,I restored a test db and checked it,this table has 2 non clustered indexes that are in Desc order and it causes fragmentation.one of these indexes is like the PK but vice versa (feild2 Desc,feild1 Desc).The PK is (feild1 Asc int,feild2 Asc datetime).I have another index (field2 Desc),I think there is extra indexes that they are pointless.I ran the query and saw that if I change the PK to (feild2 Asc,feild1 Asc) I can drop these two indexes and omit Desc ordering(fragmentation) and the queries will use pk in their plans and the logical reads and cpu time will be decrease.also in the plans of the other queries that have key lookup the cost of it will be decrease.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply