Delete millions of Records

Select count(1) from DailyMilkCollection 

How to improve performance?

Check Dependency 

-----Trigger & FK
Take backup of Index creation script.

Steps:

Apply partition 

Drop PK clustered Index- 1 or more index
Create cluster index --Datetime
Truncate partition 
Drop cluster index --Datetime

Create  PK clustered Index- 1 or more  index

Use Case - ErrorLog table

CREATE PARTITION FUNCTION Fn_Part (datetime)

AS RANGE RIGHT FOR VALUES ('20210101','20210201','20210301','20210401','20210501','20210601','20210701','20210801','20210901','20211001','20211101','20211201','20220101','20220201','20220301','20220401','20220501','20220601','20220701','20220801','20220901','20221001','20221101','20221201','20230101','20230201','20230301','20230401','20230501','20230601','20230701','20230801','20230901','20231001','20231101','20231201')

GO

CREATE PARTITION SCHEME Sch_Part 

AS PARTITION Fn_Part ALL TO ([PRIMARY]) 

GO

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName, 

    p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue

FROM sys.tables AS t  

JOIN sys.indexes AS i  

    ON t.object_id = i.object_id  

JOIN sys.partitions AS p  

    ON i.object_id = p.object_id AND i.index_id = p.index_id   

JOIN  sys.partition_schemes AS s   

    ON i.data_space_id = s.data_space_id  

JOIN sys.partition_functions AS f   

    ON s.function_id = f.function_id  

LEFT JOIN sys.partition_range_values AS r   

    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  

LEFT JOIN sys.partition_range_values AS rv

    ON f.function_id = rv.function_id

    AND p.partition_number = rv.boundary_id     

LEFT JOIN sys.partition_range_values AS rv2

    ON f.function_id = rv2.function_id

    AND p.partition_number - 1= rv2.boundary_id

WHERE 

    t.name = 'DailyMilkCollection' AND i.type <= 1 

ORDER BY t.name, p.partition_number;

Alter Table DailyMilkCollection

DROP Constraint PK_DailyMilkCollection_TransId 

GO

CREATE CLUSTERED INDEX ClusteredPart_DailyMilkCollection_Date ON dbo.DailyMilkCollection(Date_Time)

  WITH (FillFactor =80, SORT_IN_TEMPDB=ON)

  ON Sch_Part(Date_Time)

GO

Truncate Table DailyMilkCollection WITH (PARTITIONS(1));

Truncate Table DailyMilkCollection WITH (PARTITIONS(2));

Truncate Table DailyMilkCollection WITH (PARTITIONS(3));

DROP Index ClusteredPart_DailyMilkCollection_Date On dbo.DailyMilkCollection

GO

ALTER TABLE dbo.DailyMilkCollection

ADD CONSTRAINT PK_DailyMilkCollection_TransId PRIMARY KEY (TransId)

   WITH (FillFactor =80, SORT_IN_TEMPDB=ON) 

   ON [PRIMARY]

GO

Drop PARTITION SCHEME Sch_Part

Drop PARTITION FUNCTION Fn_Part