Partition of existing table with huge data in SQL

Question

I have a table which contains 2 cr records in table. I am trying to do its partition based on Month and Year.

I tried it with creating a filegroups of this tables but in my scenario that table is using on many places in pre coding part. Is there any way so I can partition this table and use it in BI reports so pre programming doesn't impact.

For edition : - I have the following pretty basic query but it takes 10 mins to run.

Here is the execution plan - https://www.brentozar.com/pastetheplan/?id=B1dy0ZQ6d

Can anyone see a way of improving it? Let me know if some sample data/table structures would be useful.

E2E_TBL_LIQUIDITY_TRACKING_CFY_JUNE has 899556 records LQTFYOpeningStock has 934878 records E2E_TBL_CPL_SALES_MR_008 has 131491 records E2E_TBL_MATERIAL_MASTER has 4695 records LocationNameView has 477 records E2e_Tbl_Customer_Master has 20390 records E2e_Tbl_Lob_Master has 5 records

Below are indexes : -

CREATE NONCLUSTERED INDEX [Index1LQt] ON [dbo].[E2E_TBL_LIQUIDITY_TRACKING_CFY_JUNE]
(
    [Territory_Code] ASC
)
INCLUDE (   [Customer_Code],
    [Product_Code],[LOB_Code],[Distributor_Stock],[Dealers_Stock],[L3_Price],[L1_Price],[L2_Price]) 
    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


CREATE NONCLUSTERED INDEX [Index2LQt] ON [dbo].[LQTFYOpeningStock]
(
    [Customer_Code] ASC,
    [Product_Code] ASC,
    [Territory_Code] ASC,
    [LOB_Code] ASC
)
INCLUDE (   [StockValueL1],
    [StockValueL2],
    [StockValueL3]) 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

CREATE NONCLUSTERED INDEX [Index3LQt] ON [dbo].[LQTFYOpeningStock]
(
    [Territory_Code] ASC
)
INCLUDE (   [Customer_Code],
    [Product_Code],
    [LOB_Code],
    [StockValueL1],
    [StockValueL2],
    [StockValueL3]) 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].[E2E_TBL_CPL_SALES_MR_008] ADD  CONSTRAINT [PK_MR008] PRIMARY KEY CLUSTERED 
(
    [Territory_Code] ASC,
    [Customer_Code] ASC,
    [Product] ASC,
    [SKU] ASC,
    [LOB] 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) ON [PRIMARY]
GO

Answer 1

You can partition without having multiple filegroups. Ideally (for performance reasons) each partition should be on a different filegroup on a different drive. But partitioning will work just fine (typically slower if the partitions are large_ on a single filegroup. Its easy to later move partitions between filegroups.