December 6, 2013 at 4:26 am
ChiragNS (10/16/2008)
Would Create Index WITH Drop_Existing work for moving non-clustered indexes.
Yes it does.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 6, 2013 at 6:10 am
SecondAry.
December 6, 2013 at 6:15 am
The very last comment in the article caught my eye -
Please remember to shrink the database to make the space available to the Operating System.
If you aren't aware of the problems this action will cause, then don't shrink anything until you have checked it out, at the very least look at these references:
http://www.sqlservercentral.com/articles/SHRINKFILE/71414/
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Vitali Lisau (10/16/2008)
Clustered indexes causes page splits, and columns from it are included into any other index, so it adds overhead.
I'm sorry but this comment is misleading, very misleading.
(1) Clustered indexes causes page splits. Yes, you get Page Splits with a Clustered Index, but Clustered Indexes do not cause Page Splits. Page splits are caused by Insert or Update activity in a table with a Clustered index, or even switching to Snapshot isolation. In the case of a Heap, you get forwarded records instead, which are worse for performance.
(2) Clustered Index Columns are added to all secondary indexes. True. There is storage and IOPs overhead involved with adding the columns to secondary indexes, but the performance benefits of a Clustered Index far outweigh the overhead. This does mean that you have to design your indexes, rather than just using a 'this looks good' approach. A wide Clustered Index is not normally a good choice, design your indexes with care, and keep the Clustered index as narrow as possible.
December 6, 2013 at 6:20 am
jayant-479472 (10/16/2008)
I think my question is little confusing, let me make it more clearIf we have a table with cluster index then by simply recreating the clustered index on different file group will move the table also on that file group
now I have a table with no index (for simplicity) and I would like to move that table to a newly created filegroup, how would I do that?
Yayant,
this is nonsence - the clustered index IS the table.
If you don't have a clustered index you have a HEAP.
Get familiar with the concepts of tables here:
http://technet.microsoft.com/en-us/library/ms190457.aspx
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 6, 2013 at 6:32 am
vvkp@hotmail.com (10/24/2008)
I have a table (670 million rows) with 200gb on 272 GB drive with clustered index (PK) and another nonclustered index .I am trying to move the table to another drive which has capcity of 300gb.
I created another Tmp_tablename on the 300gb drive and started inserting rows from original table.
But the problem is I am running out of space on logfile drive ( logfile drive size 400gb and contains only one logfile on that drive) and the database is in simple mode.
The Problem here is that you are using fully logged Operation
Put the databaes into simple recovery OR bulk_logged and insert into the table with TABLOCK Option to use a bulk load Operation
ALTER DATABASE myDB SET RECOVERY SIMPLE;
GO
CREATE TABLE dbo.mycopyoftable
(Definition)
GO
INSERT INTO dbo.mycopyoftable WITH (TABLOCK)
(c1, c2, c3, ..)
SELECT c1, c2, c3, .. FROM dbo.myoriginaltable;
GO
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
December 6, 2013 at 7:29 am
TheSQLGuru (10/16/2008)
I think several things were missing from the article:1) You should cover the need to handle foreign keys to the PK
2) You should cover a best practice of dropping the NC indexes prior to dropping the C index, then recreating same after the move.
3) You should DEFINITELY not recommend shrinking a database!!! The fragmentation caused by this is horrendous, and the database will simply fragment more (and at the OS file level) as it grows back up.
+1
If you're having to reclaim the space from the drive and have no other alternative, then you should add a step in your article to rebuild indexes on the PRIMARY filegroup to get things back in decent shape. A word to the wise on fragmenting the drive/disk array...defragging the drives/storage array can take hours if not DAYS depending on your RAID configuration and size.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2013 at 7:31 am
jayant-479472 (10/16/2008)
how would we move a table without clustered index to a different filegroup ?
To move a heap you would need to create a clustered index on the new filegroup then drop the clustered index.
Please note that log(m) mathematically proves that a clustered index is quicker with any table over 14 rows.
December 6, 2013 at 7:44 am
Hi All,
I was wondering if there are other good reasons to move a large table to another filegroup other than just drive space. Does this help in performance as well ? Should I have a few large tables on their own drive set.
Thanks Chris
December 6, 2013 at 7:47 am
December 6, 2013 at 8:51 am
One reason could be backing up a perticular file group. SQL Server now alows you to backup a filegroup
December 6, 2013 at 9:33 am
In the article, your suggestion is to drop the PK and then re-add it.
1. I think that will be tedious with foreign keys on the table, because we will have to drop all of the foreign keys and re-add them as well.
2. I think that will have a lot of overhead because it could cause all of the nonclustered indexes to be rebuilt twice (once when you drop the PK, and again when you re-add the PK).
If we instead used CREATE INDEX WITH (DROP EXISTING)
1. Foreign keys don't need to be dropped/re-added.
2. "The nonclustered indexes are rebuilt once, and then only if the index definition has changed."
Please let me know if my suggestion is not accurate. I'm not an expert.
December 6, 2013 at 10:28 am
I have another reason to move tables to another filegroup:
If you have a very fragmented database, then rebuilding/reorganizing the indexes may not reduce fragmentation very much (because the free space in the database is probably also fragmented).
--Imagine a movie theater where nobody is sitting next to their friends/family/dates, and the only available seats are not next to each other. It's not going to help if you ask one party at a time to stand up and re-arrange themselves (because they still won't be able to sit together).
A more effective approach is to create a fresh new filegroup with fresh new files (maybe even on a freshly formatted drive). Then drop/create each index/heap onto the new filegroup (maybe even one at a time so that each index/heap is totally contiguous).
--Imagine opening a new theater nextdoor to the fragmented theater. Then we ask one party at a time to move from the fragmented theater into the new theater, and we ask them to only reserve as many seats as they expect to need until the end of the movie (fillfactor).
After all of the tables/parties have moved to the new disk/theater, they should have minimal fragmentation (if not zero fragmentation). Also, this will have effectively shrunk the database to the minimum size it needs for the tables/indexes/fillfactor.
December 6, 2013 at 10:40 am
I've used create index with drop_existing, it works fine to move a table which already has a clustered index. And, you don't have to drop it and re-create the clustered index. It is also great for moving around non-clustered indexes. I shifted 500GB off a disc onto another 2 recently, using a combination of the clustered (table) and non-clustered index moves. In fact we mainly moved a whole lot of non-clustered indexes first, without taking anything offline. If the non-clustered index definition (apart from the filegroup) is the same it just scans the old non-clustered index (after a few brief locks on the meta-data), i.e. not the whole table, till it's complete then drop the old version, so it is pretty efficient.
As for heap tables, you could add a clutered index, then use the above and finally drop the clustered index when the job is done.
TBH if you were working to free up space on one disc by moving data to another, where you had a mix of table sizes and lots of indexes. I'd just move all the non-clustered indexes first to the new filegroup, to create an index-only filegroup. That way you could move typically about 10-30% of the used disc space, with relatively little impact.
This DBA says - "It depends".
December 6, 2013 at 10:43 am
Cem Uney (10/17/2008)
What happens if there is a ForeignKey which refers this column in another table.Will there be any problem?
When moving a table to a new filegroup by dropping the clustered index, it is only a problem if the table is clustered on a Primary key. In that case, you must drop all dependent foreign key constraints, perform the reclustering, and re-add all FK constraints.
This is one reason not to cluster on a natural primary key, for example, if your PK is Order number and line number for an order detail table, this can be problematic.
The best practice is to cluster your table on an IDENTITY column that is on an Int or bigint. This identity does not have to be a natural primary key, and is best if the identity column is of no use other than an ordinal row number. If you cluster on such a column, then it does not matter if the PK is nonclustered; when you drop and recreate the clustered index, you will move the data in the table to the new file group and the nonclustered PK will be rebuilt behind the scenes anyway.
Here is an example:
USE [master]
GO
ALTER DATABASE [tinker] ADD FILEGROUP [Primary_2]
GO
ALTER DATABASE [tinker] ADD FILE ( NAME = N'Tinker2', FILENAME = N'(snip)tinker_primary_2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Primary_2]
GO
use tinker
go
create table foobar(
foobar_npk_1 int not null
,foobar_npk_2 int not null
,foobar_data varchar(100)not null
,foobar_id int identity(1,1) not null
,constraint PK_Foobar PRIMARY KEY NONCLUSTERED(foobar_npk_1,foobar_npk_2) on [PRIMARY]
) on [PRIMARY]
go
create unique clustered index IXC_FOOBAR_ID on FOOBAR(FOOBAR_ID) on [PRIMARY];
go
to move the table to Primary_2:
use tinker
go
drop index IXC_FOOBAR_ID on FOOBAR;
go
create unique clustered index IXC_FOOBAR_ID on FOOBAR(FOOBAR_ID) on [PRIMARY_2];
go
The table is now on PRIMARY_2.
December 6, 2013 at 10:49 am
Alexander Karmanov (10/16/2008)
(snip)There are few more moments one should consider, especially if you plan to free and remove the original filegroup:
1. BLOB data is not moved this way, it resides in the original location. That means for the tables with BLOBs one has to create new table in the another filegroup(possibly specifying different filegroup for the BLOB data); copy the data over; drop original table and rename the new table.
This is correct, you have to recreate the table with the TEXT FILEGROUP specified to the FG to which you wish to move the BLOB data.
If log space is a problem, then move the table in chunks. I did this with a 560GB (yes you read that correctly) table full of document images, moving 5000 rows in a chunk, deleting from original table, and repeating. If you are in simple recovery mode, you should be able to do so without bloating the transaction logs.
Thanks
John.
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply