0

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

Q 1 - Having the clustered index on 2 columns (even though I partition by 1 column), does this cause any issue?

Q 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 -

Edit

By the way I ran a script against the DB before partitioning and after with Stats turned on - There are still alot of Logical reads and scans

script -

Select
    TOP 1 * 
From VehicleMonitoringLog ORDER BY dtUtcDateTime

Select
    TOP 1 * 
From VehicleMonitoringLog ORDER BY dtUtcDateTime DESC

BEFORE

(1 row affected)
Table 'VehicleMonitoringLog'. Scan count 7, logical reads 21230, physical reads 0, read-ahead reads 12722, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)
Table 'VehicleMonitoringLog'. Scan count 1, logical reads 28967, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

AFTER

(1 row affected)
Table 'VehicleMonitoringLog'. Scan count 1, logical reads 27958, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)
Table 'VehicleMonitoringLog'. Scan count 2, logical reads 16631, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

QUESTION 3

Another thing I don't understand is that - because most of data in the Database is from 2017, the file for Before_10_2017 should grow in size, but it does NOT -

The Primary file is about 1 GB and the other two are only 5 MB (same size they where created) - shouldn't they grow in size because data is being moved from PRIMARY to them?

enter image description here

  • It doesn't look like you created your clustered index aligned with the partitioning scheme. It also doesn't look like either query has a where clause that would result in partition elimination, so why would you do any less work? – Erik Darling Jan 17 '18 at 14:52
  • @sp_BlitzErik in my test environment i created a clustered index just on the dtUtcDateTime - but when I executed the reports/scripts that we use normally it took ages - we must always filter data from this table by Asset and DateTime e.g. Select TOP (1) * from VehicleMonitoringLog where iAssetId =1 ORDER BY dtUtcDateTime DESC – Dawood Awan Jan 17 '18 at 15:00
  • that is why I created the index on AssetId and UtcDateTime – Dawood Awan Jan 17 '18 at 15:01
  • There is no mention of the partition scheme in your clustered index creation. Your table still is not partitioned – sepupic Jan 18 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.