April 16, 2015 at 9:15 am
Hello,
I am using SQL Server 2012 SE.
I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours).
Since I am using standard edition partition wont work.
Is there a way to speed up things? Experts I need your valuable inputs.
Please find ddl below:
GO
CREATE TABLE [dbo].[GetPerson](
[GetPersonId] [uniqueidentifier] NOT NULL,
[LinedActivityPersonId] [uniqueidentifier] NOT NULL,
[CTName] [nvarchar](100) NULL,
[SNum] [nvarchar](50) NULL,
[PHPrimary] [nvarchar](50) NULL,
[PHAlt1] [nvarchar](50) NULL,
[PHAlt2] [nvarchar](50) NULL,
[EAdd] [nvarchar](50) NULL,
[ImportedAt] [datetime] NOT NULL,
[LinedActivityId] [uniqueidentifier] NOT NULL,
[Order] [int] NOT NULL,
[PHAssName] [varchar](255) NULL,
[TXAssName] [varchar](255) NULL,
[EMAssName] [varchar](255) NULL,
CONSTRAINT [PK_GetPerson] PRIMARY KEY NONCLUSTERED
(
[GetPersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[GetPersonValue](
[GetPersonValueId] [uniqueidentifier] NOT NULL,
[GetPersonId] [uniqueidentifier] NOT NULL,
[ValueDefId] [uniqueidentifier] NULL,
[ValueDefName] [nvarchar](50) NULL,
[ValueListItemId] [uniqueidentifier] NULL,
[Value] [nvarchar](max) NULL,
CONSTRAINT [PK_GetPersonValue] PRIMARY KEY NONCLUSTERED
(
[GetPersonValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[LinedActivity] Script Date: 4/16/2015 10:30:38 AM ******/
GO
CREATE TABLE [dbo].[LinedActivity](
[LinedActivityId] [uniqueidentifier] NOT NULL,
[AccountTriggerId] [uniqueidentifier] NOT NULL,
[LinedActivityStatusId] [int] NOT NULL,
[QueuedAt] [datetime] NOT NULL,
[LastUpdatedAt] [datetime] NULL,
[IsLiveMode] [bit] NOT NULL,
[PHJobId] [uniqueidentifier] NULL,
[EMJobId] [uniqueidentifier] NULL,
[TXJobId] [uniqueidentifier] NULL,
[NotificationTemplateId] [uniqueidentifier] NULL,
[Size] [int] NOT NULL,
[ResultsExported] [bit] NOT NULL,
[JobCompletedEMSent] [bit] NOT NULL,
[SubStatusId] [int] NULL,
CONSTRAINT [PK_JobQueue] PRIMARY KEY NONCLUSTERED
(
[LinedActivityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
GO
/****** Object: Index [IX_GetPerson_LinedActivityId] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_GetPerson_LinedActivityId] ON [dbo].[GetPerson]
(
[LinedActivityId] ASC
)
INCLUDE ( [GetPersonId],
[LinedActivityPersonId],
[CTName],
[SNum],
[PHPrimary],
[PHAlt1],
[PHAlt2],
[EAdd],
[ImportedAt],
[Order],
[PHAssName],
[TXAssName],
[EMAssName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_GetPerson_LinedActivityPerson] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_GetPerson_LinedActivityPerson] ON [dbo].[GetPerson]
(
[LinedActivityPersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
GO
/****** Object: Index [IX_GetPerson_LinedActivityPersonId_GetPersonId] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_GetPerson_LinedActivityPersonId_GetPersonId] ON [dbo].[GetPerson]
(
[LinedActivityPersonId] ASC,
[GetPersonId] ASC
)
INCLUDE ( [CTName],
[SNum],
[PHPrimary],
[PHAlt1],
[PHAlt2],
[EAdd],
[ImportedAt]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
GO
/****** Object: Index [IX_GetPersonValue_GetPersonId_GetPersonValueId] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_GetPersonValue_GetPersonId_GetPersonValueId] ON [dbo].[GetPersonValue]
(
[GetPersonId] ASC,
[GetPersonValueId] ASC
)
INCLUDE ( [ValueDefId],
[ValueDefName],
[ValueListItemId],
[Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_1] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_1] ON [dbo].[LinedActivity]
(
[LinedActivityStatusId] ASC,
[IsLiveMode] ASC
)
INCLUDE ( [LinedActivityId],
[AccountTriggerId],
[QueuedAt],
[LastUpdatedAt],
[PHJobId],
[EMJobId],
[TXJobId],
[NotificationTemplateId],
[Size],
[ResultsExported],
[JobCompletedEMSent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_2] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_2] ON [dbo].[LinedActivity]
(
[AccountTriggerId] ASC,
[LinedActivityStatusId] ASC,
[ResultsExported] ASC
)
INCLUDE ( [LinedActivityId],
[QueuedAt],
[LastUpdatedAt],
[IsLiveMode],
[PHJobId],
[EMJobId],
[TXJobId],
[NotificationTemplateId],
[Size],
[JobCompletedEMSent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_3] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_3] ON [dbo].[LinedActivity]
(
[LinedActivityStatusId] ASC,
[ResultsExported] ASC
)
INCLUDE ( [LinedActivityId],
[AccountTriggerId],
[QueuedAt],
[LastUpdatedAt],
[IsLiveMode],
[PHJobId],
[EMJobId],
[TXJobId],
[NotificationTemplateId],
[Size],
[JobCompletedEMSent]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_IL_QJSID_ATID_QJID_QAT_LU_PJID_EJID_SJID_NTID_S_New] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_IL_QJSID_ATID_QJID_QAT_LU_PJID_EJID_SJID_NTID_S_New] ON [dbo].[LinedActivity]
(
[IsLiveMode] ASC,
[LinedActivityStatusId] ASC,
[AccountTriggerId] ASC,
[LinedActivityId] ASC,
[QueuedAt] ASC,
[LastUpdatedAt] ASC,
[PHJobId] ASC,
[EMJobId] ASC,
[TXJobId] ASC,
[NotificationTemplateId] ASC,
[Size] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_NotificationTemplateID_TXJOBID] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_NotificationTemplateID_TXJOBID] ON [dbo].[LinedActivity]
(
[NotificationTemplateId] ASC
)
INCLUDE ( [TXJobId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_LinedActivity_LinedActivityID_New] Script Date: 4/16/2015 10:30:38 AM ******/
CREATE NONCLUSTERED INDEX [IX_LinedActivity_LinedActivityID_New] ON [dbo].[LinedActivity]
(
[LinedActivityId] ASC
)
INCLUDE ( [QueuedAt]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GetPerson] ADD DEFAULT ((0)) FOR [Order]
GO
ALTER TABLE [dbo].[LinedActivity] ADD CONSTRAINT [DF_LinedActivity_IsLiveMode] DEFAULT ((0)) FOR [IsLiveMode]
GO
ALTER TABLE [dbo].[LinedActivity] ADD CONSTRAINT [DF_LinedActivity_SubmittedJobID] DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary],(0),(0)),(0))) FOR [PHJobId]
GO
ALTER TABLE [dbo].[LinedActivity] ADD DEFAULT ((0)) FOR [Size]
GO
ALTER TABLE [dbo].[LinedActivity] ADD DEFAULT ((0)) FOR [ResultsExported]
GO
ALTER TABLE [dbo].[LinedActivity] ADD DEFAULT ((0)) FOR [JobCompletedEMSent]
GO
ALTER TABLE [dbo].[GetPerson] WITH CHECK ADD CONSTRAINT [FK_GetPerson_LinedActivity] FOREIGN KEY([LinedActivityId])
REFERENCES [dbo].[LinedActivity] ([LinedActivityId])
GO
ALTER TABLE [dbo].[GetPerson] CHECK CONSTRAINT [FK_GetPerson_LinedActivity]
GO
ALTER TABLE [dbo].[GetPersonValue] WITH CHECK ADD CONSTRAINT [FK_GetPersonValue_GetPerson] FOREIGN KEY([GetPersonId])
REFERENCES [dbo].[GetPerson] ([GetPersonId])
GO
ALTER TABLE [dbo].[GetPersonValue] CHECK CONSTRAINT [FK_GetPersonValue_GetPerson]
GO
Here is my delete statement
select A.GetPersonValueid,B.GetPersonID into temp_table
From GetPersonValue A inner Join GetPerson B
on A.GetPersonid =B.GetPersonID inner join LinedActivity C
on B.LinedActivityId = C.LinedActivityID and C.QueuedAt >Getdate()-400
delete from GetPersonValue where GetPersonValueid in (select GetPersonValueid from temp_table)
delete from GetPerson where GetPersonid in (select GetPersonid from temp_table)
drop table temp_table
ALTER INDEX ALL ON GetPersonValue REBUILD WITH (FILLFACTOR = 80)
ALTER INDEX ALL ON GetPerson REBUILD WITH (FILLFACTOR = 80)
Experts I need your valuable inputs here. Thanks a ton in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 16, 2015 at 10:22 am
You need to delete in batches. I've done that dozens of times without issues on tables with several millions records. But regardless, you table will be fragmented once you're done, no way to avoid that.
Here's an example:
--DELETING LARGE AMOUNTS OF DATA
DECLARE @Done BIT
SET @Done = 0
WHILE @Done = 0
BEGIN
DELETE TOP (20000) -- reduce if log still growing
FROM SomeTable WHERE SomeColumn = SomeValue
IF @@ROWCOUNT = 0
SET @Done = 1
CHECKPOINT -- marks log space reusable in simple recovery
END
Also, keep an eye on your Tlog, it may growth while you do this.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply