1

I have a table "usage" that records calls.

The table already has > 250 million records and is growing fast so I am moving the table into a new partitioned table in SQL Server to improve query performance. (The source table is sitting in ORACLE).

The source table has a clustered PK field called "CODE" (it is an IDENTITY field in the source table) and I would like to keep this field as the PK and (presumably) as a clustered index in the destination table. There is another field "USAGETIME", which is a datetime field. This field contains duplicates. Although CODE is incremented in the order new records are inserted, records are not always inserted in USAGETIME order.

The vast majority of queries on this table are filtered on usage date (datetime) ranges, so I have decided to partition the table on this field - this should give the maximum benefit of the partitions.

I have created a partition function and partition scheme (with associated files and file groups) successfully for the field USAGETIME and am testing population of the partitions.

In the new partitioned destination table, USAGETIME has a non-clustered index and is aligned to the USAGETIME partition scheme. This is the first time I have attempted partitioning on a non-clustered index so it's a new area for me.

When I checked 50 test rows by partition and index using the following query, it concerned me to see the following results (see below).

The row counts are fine by my non-clustered index (USAGETIME). Rows are placing into the correct partitions.

But my other indexes (including my clustered primary key) are indicating all rows in partition 1.

e.g.,

index                       Partition Rows  
service_code_non_clustered  1         50  
processing_date_non_clust   1         50  
PK_CODE_clustered           1         50  

So my question is this - is this something I need to be worried about or not? Will I still get all the benefits of the partitioning?

In the past, partitioning on a clustered index has 'carried' all other non-clustered indexes with it into the same partitions so I have not seen this pattern before. Any comments would be helpful!

PARTITION CHECK QUERY:

SELECT 
  si.name AS indexname,
  sp.partition_number,
  sp.rows
FROM sys.partitions sp
    JOIN sys.indexes si
        ON si.object_id=sp.object_id 
        AND si.index_id=sp.index_id

RESULTS:

indexname   partition_number    rows
usagetime_non_clustered 1   19
usagetime_non_clustered 2   24
usagetime_non_clustered 3   7
usagetime_non_clustered 4   0
usagetime_non_clustered 5   0
usagetime_non_clustered 6   0
usagetime_non_clustered 7   0
usagetime_non_clustered 8   0
usagetime_non_clustered 9   0
usagetime_non_clustered 10  0
usagetime_non_clustered 11  0
usagetime_non_clustered 12  0
usagetime_non_clustered 13  0
usagetime_non_clustered 14  0
usagetime_non_clustered 15  0
usagetime_non_clustered 16  0
usagetime_non_clustered 17  0
usagetime_non_clustered 18  0
usagetime_non_clustered 19  0
usagetime_non_clustered 20  0
usagetime_non_clustered 21  0
usagetime_non_clustered 22  0
usagetime_non_clustered 23  0
usagetime_non_clustered 24  0
usagetime_non_clustered 25  0
usagetime_non_clustered 26  0
usagetime_non_clustered 27  0
usagetime_non_clustered 28  0
usagetime_non_clustered 29  0
usagetime_non_clustered 30  0
usagetime_non_clustered 31  0
usagetime_non_clustered 32  0
usagetime_non_clustered 33  0
usagetime_non_clustered 34  0
usagetime_non_clustered 35  0
usagetime_non_clustered 36  0
usagetime_non_clustered 37  0
usagetime_non_clustered 38  0
usagetime_non_clustered 39  0
usagetime_non_clustered 40  0
usagetime_non_clustered 41  0
usagetime_non_clustered 42  0
usagetime_non_clustered 43  0
usagetime_non_clustered 44  0
usagetime_non_clustered 45  0
usagetime_non_clustered 46  0
usagetime_non_clustered 47  0
usagetime_non_clustered 48  0
usagetime_non_clustered 49  0
usagetime_non_clustered 50  0
usagetime_non_clustered 51  0
usagetime_non_clustered 52  0
usagetime_non_clustered 53  0
usagetime_non_clustered 54  0
usagetime_non_clustered 55  0
usagetime_non_clustered 56  0
usagetime_non_clustered 57  0
usagetime_non_clustered 58  0
usagetime_non_clustered 59  0
usagetime_non_clustered 60  0
usagetime_non_clustered 61  0
usagetime_non_clustered 62  0
usagetime_non_clustered 63  0
usagetime_non_clustered 64  0
usagetime_non_clustered 65  0
usagetime_non_clustered 66  0
usagetime_non_clustered 67  0
usagetime_non_clustered 68  0
usagetime_non_clustered 69  0
usagetime_non_clustered 70  0
usagetime_non_clustered 71  0
usagetime_non_clustered 72  0
usagetime_non_clustered 73  0
usagetime_non_clustered 74  0
usagetime_non_clustered 75  0
usagetime_non_clustered 76  0
usagetime_non_clustered 77  0
usagetime_non_clustered 78  0
usagetime_non_clustered 79  0
usagetime_non_clustered 80  0
service_code_non_clustered  1   50
processing_date_non_clust   1   50
PK_CODE_clustered   1   50
NULL    1   19
NULL    2   24
NULL    3   7
NULL    4   0
NULL    5   0
NULL    6   0
NULL    7   0
NULL    8   0
NULL    9   0
NULL    10  0
NULL    11  0
NULL    12  0
NULL    13  0
NULL    14  0
NULL    15  0
NULL    16  0
NULL    17  0
NULL    18  0
NULL    19  0
NULL    20  0
NULL    21  0
NULL    22  0
NULL    23  0
NULL    24  0
NULL    25  0
NULL    26  0
NULL    27  0
NULL    28  0
NULL    29  0
NULL    30  0
NULL    31  0
NULL    32  0
NULL    33  0
NULL    34  0
NULL    35  0
NULL    36  0
NULL    37  0
NULL    38  0
NULL    39  0
NULL    40  0
NULL    41  0
NULL    42  0
NULL    43  0
NULL    44  0
NULL    45  0
NULL    46  0
NULL    47  0
NULL    48  0
NULL    49  0
NULL    50  0
NULL    51  0
NULL    52  0
NULL    53  0
NULL    54  0
NULL    55  0
NULL    56  0
NULL    57  0
NULL    58  0
NULL    59  0
NULL    60  0
NULL    61  0
NULL    62  0
NULL    63  0
NULL    64  0
NULL    65  0
NULL    66  0
NULL    67  0
NULL    68  0
NULL    69  0
NULL    70  0
NULL    71  0
NULL    72  0
NULL    73  0
NULL    74  0
NULL    75  0
NULL    76  0
NULL    77  0
NULL    78  0
NULL    79  0
NULL    80  0

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.