I’ve implemented Table Partitioning for one of our Tables which contains loads and loads of data. About 10-20K records are saved every 2-3 minutes. User will want access to the latest 3 months data mostly.
So I have created the following partitions
i – Data Before 10th-2017 –
ii – Data from: 01-10-2017 till 31-12-2017 23:59:59.997
iii – Then the latest data I have not done anything with it yet – I considered it to be in the
PRIMARY (mdf) file – then after three months we can move data to another Partition i.e. (01-01-2018 till 31-03-2018 23:59:59.997)
This is what I’ve done
Step 1 – Create the FileGroups
Alter Database TBCompany_1 Add Filegroup [TBCompany_1_Before_10_2017] -- naming convention in mind [*_before_month_year] (contains before: 10-01-2017) Go Alter Database TBCompany_1 Add Filegroup [TBCompany_1_2017_10_12] -- naming convention in mind [*_year_startMonth_endMonth] (contains data: 01-10-2017 - 31-12-2017 23:59:59.997) (last three months data) Go
Step 2 – CREATING THE NDF – FILES
Alter Database TBCompany_1 Add FILE ( NAME = N'TBCompany_1_Before_10_2017', FILENAME= N'E:\MSSQL\DATA\TBCompany_1_Before_10_2017.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [TBCompany_1_Before_10_2017] Alter Database TBCompany_1 Add FILE ( NAME = N'TBCompany_1_2017_10_12', FILENAME = N'E:\MSSQL\DATA\TBCompany_1_2017_10_12.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO Filegroup [TBCompany_1_2017_10_12]
Step 3 – Partition function
Create Partition Function FN_Partition_MonLog (DATETIME) as Range LEFT For Values (N'2017-09-30T23:59:59.997', N'2017-12-31T23:59:59.997') GO
Step 4 – Partition Scheme
CREATE PARTITION SCHEME [SCH_Partition_MonLog] AS PARTITION [FN_Partition_MonLog] TO ([TBCompany_1_Before_10_2017], [TBCompany_1_2017_10_12], [PRIMARY]) -- according to the function, rest of the data will be in the Primary drive (latest data) GO
Step 5 – I already have a clustered index on AssetId, dtUtcDateTime columns in the database – I dropped and re-created it.
BEGIN TRANSACTION DROP INDEX [VehicleMonitoringLog_Asset_dtmUTCDateTime_ClusteredIndex] ON [VehicleMonitoringLog]; CREATE CLUSTERED INDEX [VehicleMonitoringLog_Asset_dtmUTCDateTime_ClusteredIndex] ON [dbo].[VehicleMonitoringLog]([iAssetId] ASC, [dtUTCDateTime] ASC); GO COMMIT TRANSACTION
Now I have 2 questions
1 – Having the clustered index on 2 columns (even though I partition by 1 column), does this cause any issue?
2 – for the latest data I am keeping it in the
PRIMARY (mdf) – does this cause a performance issue, should I move that to another filegroup e.g. TBCompany_1_2018_01_03.ndf (from 1st January till 31st March).
Using MS SQL Server 2012 –
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!