0

I need help with portioning existing table in the Database. I'm using MSSQL server 2017 and I have table called ACD stores data about calls received and it contains hundred of thousands of records and it is expected to have millions later by the end of the year. I'm trying to partition the table based on StartDateTime column which has DateTime data type in which I will have 12 files for each month but that doesn't work. below is the steps I followed but with no luck:

  1. I created 12 file groups and named each one with month name(Jan, Feb,..., Dec)(below is examples): 
    ALTER DATABASE IPCCDB ADD FileGroup January

Go

ALTER DATABASE IPCCDB ADD FileGroup Febuary

Go
  2. I added data file to each file group. 
    ALTER DATABASE [IPCCDB]

ADD FILE



(

NAME = [JanCalls],

FILENAME = 'D:\SQLServer2017Media\MSSQLSERVER\MSSQL14.MSSQLSERVER\MSSQL\DATA\IPCCDB.1ndf',

SIZE = 3072 KB,



MAXSIZE = UNLIMITED,



FILEGROWTH = 1024 KB

) TO FILEGROUP [January];
  3. I created partition function to map the rows of a partitioned table(ACD table) into partitions based on the values of a partitioning column(StartDateTime). 
    CREATE PARTITION FUNCTION [PartitionCallsgByMonth] (datetime)

AS RANGE RIGHT FOR VALUES ('20220201', '20220301', '20220401','20220501', '20220601', '20220701', '20220801', '20220901', '20221001', '20221101', '20221201');
  4. I created partition scheme to map the partitions of a partitioned table to filegroups. 
    CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION [PartitionCallsgByMonth]
TO (January, February, March, April, May, June, July, August, September, October, November, December);
  5. I loaded the records in the table into temp table.
  6. I truncated ACD table and drop the clustered index IX_ACD.
  7. I loaded the data again to ACD table with the INSERT INTO SELECT FROM statement. Eventually when I check how many records inserted to each data file in each file group i see that all the data was inserted to the first data file only.
PartitionNumber PartitionFilegroup  NumberOfRows
1      January    767681

below is sample record from ACD table

SessionID   SessionSeqNum   ResourceID  RingTime    TalkTime    HoldTime    CSQID   StartDateTime   EndDateTime
325532  0   1033    12  474 0   5   2022-01-22 15:12:32 2022-01-22 15:20:38
Thom A
  • 88,727
  • 11
  • 45
  • 75
Moe Kronz
  • 31
  • 5
  • `AS RANGE RIGHT FOR VALUES ('20220201'` ? Does that not match the first partition always? – Luuk Mar 20 '22 at 15:35
  • correct but if i also use '20220101', the execution fails for some reason. – Moe Kronz Mar 20 '22 at 15:57
  • 2
    "Doesn't work", "no luck", "SQL Server doesn't like it," and "fails for some reason" are really hard for someone else to diagnose. Could you include the actual statement that fails and the actual error message you get from SQL Server? – Aaron Bertrand Mar 20 '22 at 16:33
  • 1
    (Also it's odd that you would name your filegroups by the month. What happens when the year flips over? You'll put January 2023 data onto the same filegroup as January 2022?) – Aaron Bertrand Mar 20 '22 at 16:34
  • What is the output for: `SELECT MONTH([datetime]), count(*) FROM ACD GROUP BY MONTH([datetime]) WHERE YEAR([datetime])=2022` Might I guess that only records in January exist? – Luuk Mar 20 '22 at 16:51
  • Range Right means that the first partition will hold all the data preceding the boundary. In this case 2022-02-01. Do you have any data from Feb/March 2022 ? – Amir Pelled Mar 20 '22 at 17:19
  • 1
    Personally, I wouldn't bother with user-defined filegroups at all unless you have specialized needs, since as fast/slow storage for older/new data or different RTO SLAs for old/new data. – Dan Guzman Mar 20 '22 at 19:25
  • luuk, here is the output after: 1 314904>> for Jan 2 208253>> For Feb 3 164524>> for March – Moe Kronz Mar 20 '22 at 21:54
  • guys if my approach is wrong or not practical you can skip it but i would be grateful if someone can suggest another way. my point is that i want to segregate calls records for each month in a different data files. i included sample from ACD table at the end of the problem description above, can someone rely on it to suggest another approach?? – Moe Kronz Mar 20 '22 at 21:57
  • "i want to segregate calls records for each month in a different data files". why? what will users of your system see as the benefit to doing that? – Ben Thul Mar 21 '22 at 04:01

1 Answers1

0

I truncated ACD table and drop the clustered index IX_ACD.

After that you need to recreate the clustered index on the target partition scheme. It looks like you created the clustered index on the January filegroup instead of on MyPartitionScheme.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67