January 25, 2019 at 5:38 am
All,
I'm trying to move a clustered index (and therefore the table) to a new file group. It won't let me because it is used as a foreign key in other tables. I tried turning off 'enforce for replication' and 'enforce foreign key constraint' but that doesn't help. Do I have to completely remove the foreign key before moving the clustered index?
Thanks
January 25, 2019 at 6:38 am
Yep. Effectively you're dropping and recreating the table, so everything has to go.
"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
January 25, 2019 at 7:05 am
Thanks for your help.
January 25, 2019 at 7:24 am
Hold on a minute... HOW are you trying to do the move? What code?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 7:29 am
Hello,
Apologises. I should have posted code. It's just SSMS generated code altered to specify the new file group:
USE
[CSSystem]
GO
/****** Object: Index [PK_tabMethod] Script Date: 25/01/2019 14:24:12 ******/
ALTER
TABLE [dbo].[tabMethod] DROP CONSTRAINT [PK_tabMethod] WITH ( ONLINE = OFF )
GO
/****** Object: Index [PK_tabMethod] Script Date: 25/01/2019 14:24:12 ******/
ALTER
TABLE [dbo].[tabMethod] ADD CONSTRAINT [PK_tabMethod] PRIMARY KEY CLUSTERED
(
[uniqueref]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Configuration]
GO
January 25, 2019 at 7:39 am
as1981 - Friday, January 25, 2019 7:29 AMHello,Apologises. I should have posted code. It's just SSMS generated code altered to specify the new file group:
USE
[CSSystem]
GO
/****** Object: Index [PK_tabMethod] Script Date: 25/01/2019 14:24:12 ******/
ALTER TABLE [dbo].[tabMethod] DROP CONSTRAINT [PK_tabMethod] WITH ( ONLINE = OFF )
GO
/****** Object: Index [PK_tabMethod] Script Date: 25/01/2019 14:24:12 ******/
ALTER TABLE [dbo].[tabMethod] ADD CONSTRAINT [PK_tabMethod] PRIMARY KEY CLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Configuration]
GO
That's what I thought. Have you looked into the functionality of CREATE INDEX with the DROP EXISTING ON newfilegroup functionality to do the move? It does mean that you won't end up with the actual constraint but you will end up with a UNIQUE CLUSTERED INDEX that you may not have to rebuild all your FKs for.
Also, if the table is big... be real careful about dropping the constraint. That will cause the table to revert to a HEAP and it will also cause the old table to be available until the new HEAP is committed which, if not already available, will cause probably unwanted free space to be locked into the MDF file at least the size of the original Clustered Table. The CREATE INDEX with DROP EXISTING ON newfilegroup functionality won't cause such a thing.
Shifting gears a bit, I notice you're using a FILL FACTOR of 90 on the Clustered Index. What are the characteristics of the PK column (UniqueRef)? I ask because you could be wasting a shedload of disk space and memory if the fill factor is 90 in a vain attempt to prevent fragmentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 8:15 am
Hello,
Thanks for your help. I will have a look at that functionality.
Thanks for the advice on moving large tables. The largest is only about 5000 rows. I'm moving a set of tables to it's own filegroup as these are normally the ones that need restoring. Restoring the whole database takes quite a while, restoring just these tables only takes a few minutes.
With regards to the fill factor. That's something I've been investigating but not really decided on yet. It's a guid with newid() set as a default value. Most tables get new rows added, there are some updates and very few deletes. I think, with a guid, new records will always get added to the end of the last page and therefore if I use a higher fill factor I'm going to use less space but get more new page generation?
Thanks
January 25, 2019 at 8:39 am
as1981 - Friday, January 25, 2019 8:15 AMWith regards to the fill factor. That's something I've been investigating but not really decided on yet. It's a guid with newid() set as a default value. Most tables get new rows added, there are some updates and very few deletes. I think, with a guid, new records will always get added to the end of the last page and therefore if I use a higher fill factor I'm going to use less space but get more new page generation?
No. If you're using NEWID() as the default, new rows will be added in an evenly distributed fashion throughout the index rather than at the logical end of the index. There are a whole lot of "bad" things about GUIDs, especially random GUIDs produced by NEWID() but, for index maintenance and inserting new rows, it's actually one of the best things there is, which is a highly contrary thought compared to what most people will tell you but I've done the proof with a "different" method of doing things index maintenance wise.
Can you post the CREATE TABLE statement for the table in question and let me know about how many rows per day you add to the table so that I can make a recommendation where you won't even see "good" page splits never mind bad ones when you insert or update rows in this table? In fact, even if you were inserting 10,000 rows per day, we can make it so you can go literally months with absolutely zero fragmentation occurring.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 9:01 am
Hello,
Thanks for your help. The statement for the largest table is here:
USE [CSSystem]
GO
/****** Object: Table [dbo].[tabDemo] Script Date: 25/01/2019 15:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].tabDemo(
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[OrderNo] [varchar](50) NULL,
[TimeAdded] [datetime] NULL,
[LastPaused] [datetime] NULL,
[Status] [varchar](50) NULL,
[InetSiteRef] [uniqueidentifier] NULL,
[UploadInstance] [int] NULL,
[ReadyForUploadTime] [datetime] NULL,
[Uploaded] [int] NULL,
[Reprocess] [int] NULL,
[DoNotUpload] [int] NULL,
[RecipientName] [varchar](125) NULL,
[OrderDate] [datetime] NULL,
[DispatchSignalStatus] [varchar](2000) NULL,
[Notes] [varchar](500) NULL,
[CustNo] [varchar](8) NULL,
[ForeignOrderNo] [varchar](50) NULL,
[SubInstance] [int] NULL,
[AckSignalStatus] [varchar](50) NULL,
[Postcode] [varbinary](500) NULL,
[Email] [varbinary](2000) NULL,
[TelephoneNumber] [varbinary](2000) NULL,
CONSTRAINT [PK_tabDemo] PRIMARY KEY NONCLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_TimeAdded] DEFAULT (getdate()) FOR [TimeAdded]
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_LastPaused] DEFAULT (getdate()) FOR [LastPaused]
GO
ALTER TABLE [dbo].[tabDemo] WITH CHECK ADD CONSTRAINT [FK_tabDemo_tabusrInetSite] FOREIGN KEY([InetSiteRef])
REFERENCES [dbo].[tabusrInetSite] ([uniqueref])
GO
ALTER TABLE [dbo].[tabDemo] CHECK CONSTRAINT [FK_tabDemo_tabusrInetSite]
GO
All I've changed is the table name as it's commercially sensitive. The number of new rows, per day, varies between 50 and 500 depending on time of year. There isn't a performance issue with the system at the moment, my reason for looking at the fill factors is to increase my knowledge and improve the settings before it potentially does cause an issue. Apologises if I gave the impression there is a current issue. If it's of relevance it's split into two partitions based on the timeadded date. Total rowcount is 344732. With the small number of rows added per day I'm probably not getting any new pages, I've not investigated that yet.
Thanks
January 25, 2019 at 9:55 am
as1981 - Friday, January 25, 2019 9:01 AMHello,Thanks for your help. The statement for the largest table is here:
USE [CSSystem]
GO
/****** Object: Table [dbo].[tabDemo] Script Date: 25/01/2019 15:44:22 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].tabDemo(
[uniqueref] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[OrderNo] [varchar](50) NULL,
[TimeAdded] [datetime] NULL,
[LastPaused] [datetime] NULL,
[Status] [varchar](50) NULL,
[InetSiteRef] [uniqueidentifier] NULL,
[UploadInstance] [int] NULL,
[ReadyForUploadTime] [datetime] NULL,
[Uploaded] [int] NULL,
[Reprocess] [int] NULL,
[DoNotUpload] [int] NULL,
[RecipientName] [varchar](125) NULL,
[OrderDate] [datetime] NULL,
[DispatchSignalStatus] [varchar](2000) NULL,
[Notes] [varchar](500) NULL,
[CustNo] [varchar](8) NULL,
[ForeignOrderNo] [varchar](50) NULL,
[SubInstance] [int] NULL,
[AckSignalStatus] [varchar](50) NULL,
[Postcode] [varbinary](500) NULL,
[Email] [varbinary](2000) NULL,
[TelephoneNumber] [varbinary](2000) NULL,
CONSTRAINT [PK_tabDemo] PRIMARY KEY NONCLUSTERED
(
[uniqueref] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_uniqueref] DEFAULT (newid()) FOR [uniqueref]
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_TimeAdded] DEFAULT (getdate()) FOR [TimeAdded]
GO
ALTER TABLE [dbo].[tabDemo] ADD CONSTRAINT [DF_tabDemo_LastPaused] DEFAULT (getdate()) FOR [LastPaused]
GO
ALTER TABLE [dbo].[tabDemo] WITH CHECK ADD CONSTRAINT [FK_tabDemo_tabusrInetSite] FOREIGN KEY([InetSiteRef])
REFERENCES [dbo].[tabusrInetSite] ([uniqueref])
GO
ALTER TABLE [dbo].[tabDemo] CHECK CONSTRAINT [FK_tabDemo_tabusrInetSite]
GO
All I've changed is the table name as it's commercially sensitive. The number of new rows, per day, varies between 50 and 500 depending on time of year. There isn't a performance issue with the system at the moment, my reason for looking at the fill factors is to increase my knowledge and improve the settings before it potentially does cause an issue. Apologises if I gave the impression there is a current issue. If it's of relevance it's split into two partitions based on the timeadded date. Total rowcount is 344732. With the small number of rows added per day I'm probably not getting any new pages, I've not investigated that yet.
Thanks
If you're using REORGANIZE between 10 and 30% logical fragmentation, I can guarantee that you're getting page splits and wasting memory. And, no... you didn't give the impression that you were having performance issues. I just know how to do the "BASF" thing for these types of indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 10:05 am
Hello,
Thanks for your help. Sorry I don't recognise the abbreviation BASF?
My maintenance plan (runs weekly) is currently set to:
reindex if fragmentation is above 15% and page count >1000
rebuild if fragmentation above 30%
Thanks
January 25, 2019 at 11:13 am
as1981 - Friday, January 25, 2019 10:05 AMs Hello,Thanks for your help. Sorry I don't recognise the abbreviation BASF?
My maintenance plan (runs weekly) is currently set to:
reindex if fragmentation is above 15% and page count >1000
rebuild if fragmentation above 30%Thanks
Heh... sorry. The "BASF" reference is to the commercials for the "BASF" chemical company. They used to advertise things like "We don't make the paint... we make the paint better".
And, yes... the index defragmentation pattern you have setup is exactly what I was talking about. It's not the right pattern for GUIDs and in a most interesting Catch-22, actually causes the need for defragmentation in a really vicious, self-perpetuating circle that has all the characteristics of a bad drug habit... the more you use it, the more you need to use it.
I'll be back over the weekend with more info.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2019 at 12:27 pm
Hello,
Thanks for your help. I'd thought before that the amount of fragmentation seemed high but wasn't sure. I'll try and do some research of my own but would also appreciate the info if you get chance to post it.
Thanks
January 25, 2019 at 2:24 pm
as1981 - Friday, January 25, 2019 12:27 PMHello,Thanks for your help. I'd thought before that the amount of fragmentation seemed high but wasn't sure. I'll try and do some research of my own but would also appreciate the info if you get chance to post it.
Thanks
I can give you a preview of the bottom line...
For indexes whose leading column is a random GUID based on NEWID(), never use REORGANIZE and stop waiting for fragmentation to occur. For as wide as your table is, set the FILL FACTOR to 81 (not a misprint... use 81) and when you get > 1.0% logical fragmentation, do a REBUILD. OFFLINE would be much better and faster than ONLINE if you can but online will work..
If you want to do some research on this subject, most folks aren't doing it right and I've proven that. If you're anywhere near Cleveland on Feb 2nd, you should sign up for the SQL Saturday there where I'm giving a 2 hour presentation on the "Black Arts" of Index Maintenance. If not, you can download the presentation from the previous SQL Saturday I presented at with the understanding that this is an evolving project. Look under room 612 at the following URL for my stuff. If you have any questions, please don't hesitate to ask.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 3, 2019 at 1:24 pm
Hello,
Sorry for my slow reply. Thanks for the information.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply