Azure Data explorer database partitioning

Question

Azure Data explorer database partitioning

Nathan Sommer 80

Hi,

I'm trying to have a database with 1 big table, partitioned by a column string called UNIT_NUMBER. However, when I change the partition policy, I don't see the partitions and the queries' memory and execution time is higher than expected. Is there something I'm doing wrong?

User's image

Thanks,

Nathan

0 comments

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Manoj Kumar Boyini 12,735 Microsoft External Staff Moderator

Hi Nathan Sommer

It seems like you're having some challenges with partitioning your Azure Data Explorer database using the UNIT_NUMBER column. Here are a few things you can check and try:

Confirm Partitioning Policy: Make sure that your partitioning policy is correctly set for the table. Verify that the UNIT_NUMBER is defined correctly as the partition key.

Memory and Execution Time: Sometimes, high memory usage and execution times can occur if there are too many partitions (over 1,000). Check if you're nearing this limit, as it can lead to performance issues.

Statistics and Query Performance: If your queries are slow, it may be beneficial to check if statistics on the partitioned indexes are being updated. Use the UPDATE STATISTICS command with the FULLSCAN clause to ensure accurate statistics are based on all rows.

Partition Management: Ensure you are managing the partitions efficiently. Keeping some partitions empty, especially at either end of your partition range, can help avoid inefficient split and merge operations.

Database Scaling: If you're running memory-intensive operations, consider scaling your database to a higher service tier temporarily. This can help improve performance during demanding queries.

Review Documentation: It's a great idea to refer to the detailed guidance regarding partitioning in Azure SQL Database for best practices and optimization tips.

If these steps don't resolve your issue, I would recommend providing more details such as:

The specific changes you made to the partitioning policy.
The queries you are running, which are experiencing high execution times.
Your current database service tier and any recent changes made to it.

Hope this helps and happy to assist further!

References

Manoj Kumar Boyini 12,735 Reputation points Microsoft External Staff Moderator

2026-02-02T04:18:32.22+00:00

Hi Nathan Sommer

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Nathan Sommer 80 Reputation points

2026-02-04T14:38:35.3733333+00:00

Hi @Manoj Kumar Boyini

This is a KQL database - i've looked at the extents and can tell that there is no partitioning being done on UNIT_NUMBER. I've checked the policy as well
Manoj Kumar Boyini 12,735 Reputation points Microsoft External Staff Moderator

2026-02-05T20:14:55.89+00:00

Hi Nathan Sommer

When you enable a partitioning policy, ADX applies it immediately only to newly created extents. Existing extents are handled by a background repartitioning process that reads and rewrites data into homogeneous extents. This process is asynchronous and throttled to protect the cluster, so it can take time before partitioning becomes visible when you inspect extents. If you need immediate and deterministic partitioning for historical data, the supported approach is to create a new table with the partitioning policy defined first and then re-ingest the data.

Partitioning in ADX is intended to improve query pruning and reduce scanned data when queries frequently filter on the partition key and when the key’s cardinality and distribution are suitable. It is not designed to organize device data or to reduce ingestion or routing latency. If the partition key has high cardinality or uneven distribution, partitioning can increase fan-out and metadata overhead, which explains the slower queries you observed.

Regarding your main concern about scaling devices: update policies in ADX do not run per device or per message. They execute once per ingested extent (batch). This means increasing from 10 to 50 or even hundreds of devices does not multiply update-policy executions and does not inherently introduce latency. Latency only increases if the update logic is expensive, ingestion produces extremely small extents (for example with very aggressive streaming), or the cluster becomes CPU or memory constrained.

Your current architecture one Event Hub (or a small number), one Bronze table for raw ingestion, and Silver tables populated via update policies — is the recommended and scalable IoT pattern in ADX. You do not need additional source tables or Event Hubs as device count grows. If sub-second end-to-end latency is ever required, small queued ingestion batches or upstream routing are the usual next steps.

Reference:
https://learn.microsoft.com/en-us/kusto/management/partitioning-policy?view=azure-data-explorer&preserve-view=true
https://learn.microsoft.com/en-us/kusto/management/extents-overview?view=azure-data-explorer&preserve-view=true
https://learn.microsoft.com/en-us/kusto/management/update-policy?view=azure-data-explorer&preserve-view=true
https://learn.microsoft.com/en-us/azure/data-explorer/create-event-hubs-connection?tabs=get-data%2Cget-data-2

Hope this helps, Please let us know if you have any questions and concerns.

Answer 2

Sander van de Velde | MVP 37,056 MVP

Hello ,

welcome to this Azure community forum.

Adding a partition policy can add overhead during ingestion. You need to 'play' with the MaxPartitionCount:

User's image

If your database is already filled with a substantial amount of data, setting this policy can cause throttling.

As seen in the sample scenarios adding a partition policy seems to add value only for specific occasions. For example, I do a lot with IoT but having a cardinality with at least 1,000,000 distinct values and an even distribution is not always in place. So, check if you really need partitioning.

--

If this answer helps you, a thumbs-up or marking it as accepted answer is appreaciated. All community members with similar questions will benefit by doing so. Your contribution is highly appreciated.

Nathan Sommer 80 Reputation points

2026-02-04T14:42:08.1233333+00:00

@Sander van de Velde | MVP I'm trying to have a structured/scalable database where everything is organized into 1 table - is there another way to do this? I'm currently using update policies; however it doesn't seem very scalable. I thought this might work, however I can't seem to get the data organized by that variable UNIT_NUMBER
Sander van de Velde | MVP 37,056 Reputation points MVP

2026-02-04T18:42:02.9833333+00:00

Hello Nathan,

are you familiar with the concept of the 'medallion architecture'?

Raw data arrived in a 'bronze' table. Using update policies, the raw data is typed/split up/transformed/filters into 'silver' tables. Additionally, 'gold' tables can be introduced for very specific reporting, again using table update policies.

Additionally, materialize views can be added for aggregations.

This makes you data more approachable.

Still, having all data in one table is not a real problem for Azure Data Explorer. The underlying (file) storage of ADX is very efficient and optimized for supporting even an extreme number of rows.

That one table approach is a bit vulnerable for inefficient queries requestion more rows than needed.

--

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar questions will benefit by doing so. Your contribution is highly appreciated.
Nathan Sommer 80 Reputation points

2026-02-04T19:08:45.1666667+00:00

Hi @Sander van de Velde | MVP ,

Update policies are actually what I have currently. However, I'm not sure if I have 50 tables looking at 1 source table that has 50 different sources of iot data coming in at 1 sec will cause issues with the latency. I'd like to reduce latency as much as possible, which is why I'm looking at other alternatives. I could keep making different event hubs to other source tables, but looking down the road seems to be disorganized. Would like to hear your thoughts.

Thanks,

Nathan
Sander van de Velde | MVP 37,056 Reputation points MVP

2026-02-04T19:48:33.2366667+00:00

Hello Nathan,

so you have 50 different sources of data and likely these have all different message formats.

The full picture is not clear but do you want to split all 50 types of sources into 50 different 'silver' tables?

Then it's up to you to have them connected, eg. via 50 data connections with 50 bronze tables or something in between.

There are more angles to this story like number of megabyte per second being ingested and choosing direct ingest over batching.

I recommend testing the various combination and see what fits your needs.

Keep in mind that Azure Data Explorer will scale with you regarding ingestion and data storage.

--

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar questions will benefit by doing so. Your contribution is highly appreciated.
Nathan Sommer 80 Reputation points

2026-02-04T20:19:08.8033333+00:00

Hi @Sander van de Velde | MVP ,

So, the current format is that I have about 13 sources with the same format - going to a source table (bronze). They are filtered via update policies (silver). Looking ahead, if I get 50 sources (all the same format) filtered, each update policy is looking at a function. Each function is looking at the data coming in (streaming ingestion) and copying the data to the table.

What I'm asking is wouldn't this cause latency? If I were to have 50 sources coming into 1 source table at a 1 sec interval (50 functions looking at 50 messages a sec), I'd assume there would be a latency with the increase of these update policies.

I'm just wondering if there is a better way to ingest the data.

Thanks.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Nathan Sommer 80 Reputation points

2026-02-04T20:34:47.1533333+00:00

Hi @Sander van de Velde | MVP ,What I'm trying to say is I do already have what you're describing (Devices going to a source table, then getting filtered to each respected table via update policy)

I'm trying to see what the limitations are. If I get 50 more devices in that source table, will it slow down the filtering and cause latency? If so, what can I do to fix this? I don't want to keep making more source tables, let me know if this makes since.

Thanks
Sander van de Velde | MVP 37,056 Reputation points MVP

2026-02-05T21:39:34.6866667+00:00

Hello Nathan,

Azure Data Explorer is designed to handle huge amount of data, both for ingestion and for querying.

That said, more load comes with more CPU usage and more pressure on ingestion.

Having dozens of update policies running will turn into more CPU usage.

Azure Data Explorer is a cluster so it will load balance this as good as possible.

It's true that you can help it with very specific tinkering.

I suggest to just start with the approach you thinks fits best and start measuring. Do not start optimizing at the start. Read the documentation.

Making more tables is not a problem and should not be limited. But do not provide a table for each device. The same type of devices with the same message schema are normally grouped in a certain table. The medallion architecture is a proven concept and add value day in day out or many customers. We mostly use it to split message schema (telemetry, metrics, alerts, etc.). And we mostly query on the typed, silver table.

And we normally query with having time on our mind. Reporting is normally for a specific timespan. Or its spanning multiple months or years but aggregated. So we have data big aggregated upfront.

That's where ADX shines.

To circle back to your question, you asked about partitioning. I suggest to get more straightforward tasks like update policies and aggregations and deduplication set up first.

And if queries start to slow down, you can think of partitioning. Also because only at that point, you can actually measure if partitioning will offer benefits.

Your on the right track regarding working out a real-time solution architecture.

Start with thinking big but acting small.

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar questions will benefit by doing so. Your contribution is highly appreciated.

Share via

Azure Data explorer database partitioning

References

1 additional answer

Your answer