I have a logs table for my application which is almost 3TB data. I want to partition this table yearly. Below is the scripts for Table, View, and indexes script:
GO
CREATE TABLE [dbo].[AuditLogs](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[BankId] [nvarchar](2) NULL,
[Message] [nvarchar](max) NULL,
[Level] [nvarchar](100) NULL,
[TimeStamp] [datetimeoffset](7) NOT NULL,
[FunctionName] [nvarchar](400) NULL,
[CorrelationId] [uniqueidentifier] NULL,
CONSTRAINT [PK_AuditLogs] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: View [dbo].[View_RequestTime] Script Date: 22/10/2023 2:56:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_RequestTime]
AS
select [FunctionName],BankId,[TimeStamp],[ElapsedTime] from(
select
ROW_NUMBER() over(partition by CorrelationId order by l.[TimeStamp]) AS Row#,
l.[FunctionName],
l.BankId,
l.[TimeStamp],
l.CorrelationId,
DATEDIFF(millisecond,l.[TimeStamp],lead(l.[TimeStamp]) over(partition by CorrelationId order by l.[TimeStamp])) ElapsedTime
from AuditLogs l where l.CorrelationId is not null
)lo where Row#=1
GO
/****** Object: View [dbo].[View_1] Script Date: 22/10/2023 2:56:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [NonClusteredIndex-20220821-124100] Script Date: 22/10/2023 2:56:01 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20220821-124100] ON [dbo].[AuditLogs]
(
[Level] ASC,
[TimeStamp] ASC,
[FunctionName] ASC,
[CorrelationId] ASC,
[BankId] 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, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object: Index [NonClusteredIndex-20231011-105720] Script Date: 22/10/2023 2:56:01 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20231011-105720] ON [dbo].[AuditLogs]
(
[CorrelationId] ASC
)
INCLUDE([BankId],[TimeStamp],[FunctionName]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
My concern is how to create a partition function that works on yearly data partitioning. Column for datetime is [TimeStamp] and I have Id as primary key(clustered index), so how I will create clustered index that works with partition? Any help and suggestion is highly appreciated, I have read this answer before posting this link.
Query Execution Plan: Here is the execution plan link
Below is the query that I ran on the view with very small data compared to actual data. It takes more than 2 minutes to execute.
SELECT [v].[BankId], [v].[FunctionName],
AVG(CAST([v].[ElapsedTime] AS float)) AS [TotalSucceedAvgTime], CAST(COUNT(CASE
WHEN [v].[ElapsedTime] IS NOT NULL THEN 1
END) AS float) AS [TotalSucceedReq],
CAST(COUNT(CASE WHEN [v].[ElapsedTime] IS NULL THEN 1
END) AS float) AS [TotalFailedReq],
(CAST(COUNT(CASE
WHEN [v].[ElapsedTime] IS NOT NULL THEN 1
END) AS float) / CAST(COUNT() AS float)) 100.0E0 AS [SuccessRate], (CAST(COUNT(CASE
WHEN [v].[ElapsedTime] IS NULL THEN 1
END) AS float) / CAST(COUNT() AS float)) 100.0E0 AS [FailedRate]
FROM [View_RequestTime] AS [v]
WHERE (([v].[BankId] = 1)) AND ((CONVERT(date, [v].[TimeStamp]) >= '2022-05-05 00:00:00') AND (CONVERT(date, [v].[TimeStamp]) <= '2024-11-11 00:00:00'))
GROUP BY [v].[FunctionName], [v].[BankId]
