I have recently converted our one large table to use table partitioning in SQL Server 2012.

It's a table that stores logs based on a time. So I created a partition function to split the table on my [time] column which is of Type DATETIME2(2). My partion scheme then maps each day to a new .ndf file in its own filegroup.

Now I need some help with adding the indexes for this table, and I am slightly confused between the whole, Clustered and non-clustered and Aligned and Non-aligned indexes. So I thought I would come seek assistance here as I believe the indexing plays a large role in the table partitioning performance.

So my reason for asking.

When doing a sql query on data that would fall into a single partition, I noticed in Resource Monitor that while running this query I could see all the .ndf files being loaded and then read from. It appears as if sql was goign through every partition to execute my query. From my knowledge of table partitioning, it should only need to access the partition / file that the data is in?

This lead me to think that my Indexing is not correct.

Here is the summary of my [Logs] Table Structure:

Table Design

So now As you can see, I have set The time and the logID as a compound PK. All the other columns are irrelevant for this question.

So currently I have the following two Indexes on the table:

CREATE NONCLUSTERED INDEX [NCILogIdLogs] ON [dbo].[Logs]
(
    [logId] 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)
GO


ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
(
    [logId] ASC,
    [time] 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)
GO

Could some one please explain the idea behind indexing the different partitions.

My understanding:

You need a index that spans ALL partitions, on the column that partitions the table. This allows sql to jump straight to partition which the query is in.

you then need an index that resides inside each partition, this allows faster searches within the partition?

Am I totally confused? Or am I on the right track?

[EDIT]

Partition scheme:

CREATE PARTITION SCHEME [PS_dbo_Date_ByDay] AS PARTITION [PF_dbo_Date_ByDay] TO ([PRIMARY], [PRIMARY], [FG_2014_09_02], [FG_2014_09_03], [FG_2014_09_04], [FG_2014_09_05], [FG_2014_09_06], [FG_2014_09_07], [FG_2014_09_08], [FG_2014_09_09], [FG_2014_09_10], [FG_2014_09_11], [FG_2014_09_12], [FG_2014_09_13], [FG_2014_09_14], [FG_2014_09_15], [FG_2014_09_16], [FG_2014_09_17], [FG_2014_09_18], [FG_2014_09_19], [FG_2014_09_20], [FG_2014_09_21], [FG_2014_09_22], [FG_2014_09_23], [FG_2014_09_24], [FG_2014_09_25], [FG_2014_09_26], [FG_2014_09_27], [FG_2014_09_28], [FG_2014_09_29], [FG_2014_09_30], [FG_2014_10_01], [FG_2014_10_02], [FG_2014_10_03], [FG_2014_10_04], [FG_2014_10_05], [FG_2014_10_06], [FG_2014_10_07], [FG_2014_10_08], [FG_2014_10_09], [FG_2014_10_10], [FG_2014_10_11], [FG_2014_10_12], [FG_2014_10_13], [FG_2014_10_14], [FG_2014_10_15], [FG_2014_10_16], [FG_2014_10_17], [FG_2014_10_18], [FG_2014_10_19], [FG_2014_10_20], [FG_2014_10_21], [FG_2014_10_22], [FG_2014_10_23], [FG_2014_10_24], [FG_2014_10_25], [FG_2014_10_26], [FG_2014_10_27], [FG_2014_10_28], [FG_2014_10_29], [FG_2014_10_30], [FG_2014_10_31], [FG_2014_11_01], [FG_2014_11_02], [FG_2014_11_03], [FG_2014_11_04], [FG_2014_11_05], [FG_2014_11_06], [FG_2014_11_07], [FG_2014_11_08], [FG_2014_11_09], [FG_2014_11_10], [FG_2014_11_11], [FG_2014_11_12], [FG_2014_11_13], [FG_2014_11_14], [FG_2014_11_15], [FG_2014_11_16], [FG_2014_11_17], [FG_2014_11_18], [FG_2014_11_19], [FG_2014_11_20], [FG_2014_11_21], [FG_2014_11_22], [FG_2014_11_23], [FG_2014_11_24], [FG_2014_11_25], [FG_2014_11_26], [FG_2014_11_27], [FG_2014_11_28], [FG_2014_11_29], [FG_2014_11_30], [FG_2014_12_01], [FG_2014_12_02], [FG_2014_12_03], [FG_2014_12_04], [FG_2014_12_05], [FG_2014_12_06], [FG_2014_12_07], [FG_2014_12_08], [FG_2014_12_09], [FG_2014_12_10], [FG_2014_12_11], [FG_2014_12_12], [FG_2014_12_13], [FG_2014_12_14], [FG_2014_12_15], [FG_2014_12_16], [FG_2014_12_17], [FG_2014_12_18], [FG_2014_12_19], [FG_2014_12_20], [FG_2014_12_21], [FG_2014_12_22], [FG_2014_12_23], [FG_2014_12_24], [FG_2014_12_25], [FG_2014_12_26], [FG_2014_12_27], [FG_2014_12_28], [FG_2014_12_29], [FG_2014_12_30], [FG_2014_12_31])
GO

And the partition Function:

CREATE PARTITION FUNCTION [PF_dbo_Date_ByDay](datetime2(2)) AS RANGE RIGHT FOR VALUES (N'2014-09-01T00:00:00.000', N'2014-09-02T00:00:00.000', N'2014-09-03T00:00:00.000', N'2014-09-04T00:00:00.000', N'2014-09-05T00:00:00.000', N'2014-09-06T00:00:00.000', N'2014-09-07T00:00:00.000', N'2014-09-08T00:00:00.000', N'2014-09-09T00:00:00.000', N'2014-09-10T00:00:00.000', N'2014-09-11T00:00:00.000', N'2014-09-12T00:00:00.000', N'2014-09-13T00:00:00.000', N'2014-09-14T00:00:00.000', N'2014-09-15T00:00:00.000', N'2014-09-16T00:00:00.000', N'2014-09-17T00:00:00.000', N'2014-09-18T00:00:00.000', N'2014-09-19T00:00:00.000', N'2014-09-20T00:00:00.000', N'2014-09-21T00:00:00.000', N'2014-09-22T00:00:00.000', N'2014-09-23T00:00:00.000', N'2014-09-24T00:00:00.000', N'2014-09-25T00:00:00.000', N'2014-09-26T00:00:00.000', N'2014-09-27T00:00:00.000', N'2014-09-28T00:00:00.000', N'2014-09-29T00:00:00.000', N'2014-09-30T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-10-02T00:00:00.000', N'2014-10-03T00:00:00.000', N'2014-10-04T00:00:00.000', N'2014-10-05T00:00:00.000', N'2014-10-06T00:00:00.000', N'2014-10-07T00:00:00.000', N'2014-10-08T00:00:00.000', N'2014-10-09T00:00:00.000', N'2014-10-10T00:00:00.000', N'2014-10-11T00:00:00.000', N'2014-10-12T00:00:00.000', N'2014-10-13T00:00:00.000', N'2014-10-14T00:00:00.000', N'2014-10-15T00:00:00.000', N'2014-10-16T00:00:00.000', N'2014-10-17T00:00:00.000', N'2014-10-18T00:00:00.000', N'2014-10-19T00:00:00.000', N'2014-10-20T00:00:00.000', N'2014-10-21T00:00:00.000', N'2014-10-22T00:00:00.000', N'2014-10-23T00:00:00.000', N'2014-10-24T00:00:00.000', N'2014-10-25T00:00:00.000', N'2014-10-26T00:00:00.000', N'2014-10-27T00:00:00.000', N'2014-10-28T00:00:00.000', N'2014-10-29T00:00:00.000', N'2014-10-30T00:00:00.000', N'2014-10-31T00:00:00.000', N'2014-11-01T00:00:00.000', N'2014-11-02T00:00:00.000', N'2014-11-03T00:00:00.000', N'2014-11-04T00:00:00.000', N'2014-11-05T00:00:00.000', N'2014-11-06T00:00:00.000', N'2014-11-07T00:00:00.000', N'2014-11-08T00:00:00.000', N'2014-11-09T00:00:00.000', N'2014-11-10T00:00:00.000', N'2014-11-11T00:00:00.000', N'2014-11-12T00:00:00.000', N'2014-11-13T00:00:00.000', N'2014-11-14T00:00:00.000', N'2014-11-15T00:00:00.000', N'2014-11-16T00:00:00.000', N'2014-11-17T00:00:00.000', N'2014-11-18T00:00:00.000', N'2014-11-19T00:00:00.000', N'2014-11-20T00:00:00.000', N'2014-11-21T00:00:00.000', N'2014-11-22T00:00:00.000', N'2014-11-23T00:00:00.000', N'2014-11-24T00:00:00.000', N'2014-11-25T00:00:00.000', N'2014-11-26T00:00:00.000', N'2014-11-27T00:00:00.000', N'2014-11-28T00:00:00.000', N'2014-11-29T00:00:00.000', N'2014-11-30T00:00:00.000', N'2014-12-01T00:00:00.000', N'2014-12-02T00:00:00.000', N'2014-12-03T00:00:00.000', N'2014-12-04T00:00:00.000', N'2014-12-05T00:00:00.000', N'2014-12-06T00:00:00.000', N'2014-12-07T00:00:00.000', N'2014-12-08T00:00:00.000', N'2014-12-09T00:00:00.000', N'2014-12-10T00:00:00.000', N'2014-12-11T00:00:00.000', N'2014-12-12T00:00:00.000', N'2014-12-13T00:00:00.000', N'2014-12-14T00:00:00.000', N'2014-12-15T00:00:00.000', N'2014-12-16T00:00:00.000', N'2014-12-17T00:00:00.000', N'2014-12-18T00:00:00.000', N'2014-12-19T00:00:00.000', N'2014-12-20T00:00:00.000', N'2014-12-21T00:00:00.000', N'2014-12-22T00:00:00.000', N'2014-12-23T00:00:00.000', N'2014-12-24T00:00:00.000', N'2014-12-25T00:00:00.000', N'2014-12-26T00:00:00.000', N'2014-12-27T00:00:00.000', N'2014-12-28T00:00:00.000', N'2014-12-29T00:00:00.000', N'2014-12-30T00:00:00.000', N'2014-12-31T00:00:00.000')
GO
  • 1
    I don't see a reference to a partition scheme in your DDL. Are you sure the table is partitioned? Also, the actual execution plan would be the best way to tell what's going on. – usr Sep 17 '14 at 9:59
  • 1
    Your DDL script for the clustered index needs to specify the partition scheme and the column to partition on, otherwise the table will not be partitioned. Otherwise your table will be on the default filegroup. – dbafromthecold Sep 17 '14 at 11:23
  • I see, so my current index is incorrect. I hate to be a nag but how exactly do I specify that? – Zapnologica Sep 17 '14 at 12:19
  • 1
    The clustered index is the data in the table (at the leaf level). Could you specify the column that you want to partition the table on please? – dbafromthecold Sep 17 '14 at 12:56
  • 1
    OK, I'll drop in an answer now. – dbafromthecold Sep 17 '14 at 13:59

In order for the table to be partitioned, your clustered index should be created as:-

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
(
    [logId] ASC,
    [time] 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 [PS_dbo_Date_ByDay](Time)

You can then view the different partitions by running:-

SELECT 
    t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, 
    i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue, p.rows
FROM 
    sys.tables AS t
INNER JOIN
    sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN
    sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
    sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN 
    sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number
WHERE 
    t.name = 'Logs'
AND 
    i.type <= 1
ORDER BY p.partition_number;

The table is essentially carved up into different sections this will allow queries to reference the different sections without blocking each other (try inserting records into two different partitions at the same time, then try it on a non-partitioned table).

This will be the same for an ALIGNED index. Create it on the paritition scheme (the same as the clustered index) and it will also be carved up (the SELECT above will show you).

However you can create a NON-ALIGNED index, just don't add the ON PS_dbo_Date_ByDay, use PRIMARY or another filegroup.

Hope this helps.

EDIT:- Your primary key contains a column (LogID) that is not part of the partition. The table will be partitioned but there could be a performance impact. I asked a similar question on here a while ago, here's the link:-

Partitioning Query

  • Thanks for this reply. I added the LogId, as the Time its self is not unique, however it is my partitioning column. So with the LogId which is Unique, my PK is now unique and partitioned. but I hear what you are saying. Its only half partitioned. So must I remove my current Clustered index and replace it with the new one? – Zapnologica Sep 17 '14 at 14:16
  • Have a read through of the link to my question. Aaron Bertrand went through the different options and the possible performance impacts. I have several tables in which the lead column in the PK is not the partitioning column and I haven't seen any issues (yet). – dbafromthecold Sep 17 '14 at 14:20
  • So to fix the issue with my index, I should simply swap the index column order from (id, time) to (time,id) ? – Zapnologica Sep 19 '14 at 12:48
  • It depends, will that be OK for your clustered index? All I can say is test, test, test I'm afraid. – dbafromthecold Sep 19 '14 at 12:58

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.