Azure SQL Clustered Index On Partition

Question

Azure SQL Clustered Index On Partition

Som Pathak 0

I created a table which partition on schedule_day , Do i required to create cluster index on schedule_day ?

In my case i have all queries in this format :

select * from our_table where store_id = <>, dep_id = <>, schedule_day <> and timestamp = <>

Should i create clustered index on (store_id, dep_id, timestamp) ? Azure SQL will able to locate the correct partition and then use index for search ? How i can verify if my queries are going in correct partition not scanning whole table ?

Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-05-31T17:11:24.17+00:00

Som Pathak Thank you for reaching out.

My understanding is that you are trying to create clustered index on (store_id, dep_id, timestamp) and wants to know if Azure SQL will be able to locate the correct partition and then use index for search.

I created a table which partition on schedule_day , do i required to create cluster index on schedule_day ?

If you have partitioned your table on the schedule_day column, you do not need to create a clustered index on the same column. However, you can create a clustered index on the columns (store_id, dep_id, timestamp) to improve the performance of your queries.

When you execute a query with a filter on the partition key column (schedule_day in your case), Azure SQL will be able to locate the correct partition and then use the clustered index for searching. This will help to reduce the amount of data scanned and improve the query performance.

How i can verify if my queries are going in correct partition not scanning whole table?

To verify if your queries are going to the correct partition and not scanning the whole table, you can use the EXPLAIN command to see the query plan or the sys.dm_exec_requests dynamic management view to monitor the query execution.

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-05-31T19:21:28.1+00:00

Som Pathak Please do comment below if you need further clarification.

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-06-01T16:48:27.91+00:00

Som Pathak Checking if you had the chance to review the above answer. Please do comment below if you need further clarification.

Regards,

Oury
Som Pathak 0 Reputation points

2023-06-21T19:22:47.03+00:00
Som Pathak 0 Reputation points

2023-06-21T19:24:27.9066667+00:00
As send in above plan my query is scanning very large number of rows. It's not directly going in one partition

select TOP(100) * from my_table where schedule_day = {d '2023-06-22'}

1 answer

Your answer

Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-05-31T17:11:24.17+00:00

Som Pathak Thank you for reaching out.

My understanding is that you are trying to create clustered index on (store_id, dep_id, timestamp) and wants to know if Azure SQL will be able to locate the correct partition and then use index for search.

I created a table which partition on schedule_day , do i required to create cluster index on schedule_day ?

If you have partitioned your table on the schedule_day column, you do not need to create a clustered index on the same column. However, you can create a clustered index on the columns (store_id, dep_id, timestamp) to improve the performance of your queries.

When you execute a query with a filter on the partition key column (schedule_day in your case), Azure SQL will be able to locate the correct partition and then use the clustered index for searching. This will help to reduce the amount of data scanned and improve the query performance.

How i can verify if my queries are going in correct partition not scanning whole table?

To verify if your queries are going to the correct partition and not scanning the whole table, you can use the EXPLAIN command to see the query plan or the sys.dm_exec_requests dynamic management view to monitor the query execution.

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-05-31T19:21:28.1+00:00

Som Pathak Please do comment below if you need further clarification.

Regards,

Oury
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-06-01T16:48:27.91+00:00

Som Pathak Checking if you had the chance to review the above answer. Please do comment below if you need further clarification.

Regards,

Oury
Som Pathak 0 Reputation points

2023-06-21T19:22:47.03+00:00
Som Pathak 0 Reputation points

2023-06-21T19:24:27.9066667+00:00

As send in above plan my query is scanning very large number of rows. It's not directly going in one partition

select TOP(100) * from my_table where schedule_day = {d '2023-06-22'}

Answer 1

Oury Ba-MSFT 21,121 Microsoft Employee Moderator

@Som Pathak

Let me correct myself here.

In general, if there is an equality predicate, an index is a must. partitioning is usually not required however it is recommended when the size of data is larger and efficient management (loading, building indexes, archiving, etc.) of that data is required.

Azure SQL will be able to locate the correct partition and then use index for search.

Should i create clustered index on (store_id, dep_id, timestamp)?

If the goal is just to make this one query perform well, then probably yes.

You should not create the clustered index for just one query, or even for a type of queries. There are many factors to consider, including column data types, the read and write workloads against the table, other indexes that may be needed, etc. Without all of that data, we cannot make a recommendation as to what the right clustered index should be in this case.

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16#aligned-index

https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-ver16#query-processing-enhancements-on-partitioned-tables-and-indexe

We recommend reading a series of blogs on sqlskills.com to learn the relevant considerations when selecting the clustered index, for example: More considerations for the clustering key – the clustered index debate continues! – Kimberly L. Tripp (sqlskills.com)

Regards,

Oury

Som Pathak 0 Reputation points

2023-06-25T10:31:58.7033333+00:00

Currently all our queries will be one type only will contain this filters for sure schedule_day,store_id, dep_id, timestamp.

I have ordered the index in this order - store_id, scheduled_day, timestamp, dep_id.

As i want to user store_id first so it will filter more data. For Write, we do it every hours where job write around 20 million rows to one of the partition current_day and future day partition. We hold only 15 days of data in our DB.

In this scenario we should have one clustered index based on composite key ?
Som Pathak 0 Reputation points

2023-06-25T10:35:21.9166667+00:00

All our query will contain these filters - (store_id, schedule_day, dep_id, timestamp).
Oury Ba-MSFT 21,121 Reputation points Microsoft Employee Moderator

2023-07-17T17:18:30.79+00:00

Som Pathak

Sorry for the late reply. If all your queries will contain the above filters, then yes, you can implement a clustered index on them.

Please let me know if that answer your question.

Regards,

Oury

Share via

Azure SQL Clustered Index On Partition

1 answer

Your answer