In a dedicated Synapse pool, do non-clustered indexes "live" on the compute node? Since they're not physical, I'd imagine that they're not in the distribution storage, correct? On a related note, if data movement happens (either between nodes or between distributions in the same node), how does Synapse use the indexes? My best guess is that if data movement is always just a copy into tempDB as a heap, then it sort of "loses" the benefit of the original index. Is that right?

Hi @ JenP , In a dedicated Synapse pool, non-clustered indexes do not "live" on the compute node. Since they are not physical, they are not stored in the distribution storage either. Non-clustered indexes are stored separately from the table and serve as pointers to the physical storage, which is usually unordered. When data movement occurs, such as between nodes or between distributions in the same node, the indexes may lose their benefit When data movement happens in Synapse Analytics, the indexes are still used to optimize query performance. If data movement is between distributions in the same node, the indexes are still used to optimize the query performance. If data movement is between nodes, the query performance may be impacted due to the data movement. When data movement is always just a copy into tempDB as a heap, then the original index is not used. But, if the data is moved to a table with the same schema, the index can be rebuilt on the new table and can be used to optimize query performance. Reference link: https://afolabianimashaun.medium.com/understanding-indexes-in-azure-synapse-to-optimize-perfomance-ccb148d1bb18 https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/massively-parallel-processing-mpp-architecture Hope this info helps

Where does a Synapse dedicated index "live"?

Accepted answer

Pramod o 155 Reputation points

2023-11-07T12:58:06.44+00:00

Hi @JenP,

In a dedicated Synapse pool, non-clustered indexes do not "live" on the compute node.

Since they are not physical, they are not stored in the distribution storage either.

Non-clustered indexes are stored separately from the table and serve as pointers to the physical storage, which is usually unordered.

When data movement occurs, such as between nodes or between distributions in the same node, the indexes may lose their benefit

When data movement happens in Synapse Analytics, the indexes are still used to optimize query performance.

If data movement is between distributions in the same node, the indexes are still used to optimize the query performance.

If data movement is between nodes, the query performance may be impacted due to the data movement.

When data movement is always just a copy into tempDB as a heap, then the original index is not used. But, if the data is moved to a table with the same schema,

the index can be rebuilt on the new table and can be used to optimize query performance.

Reference link:

https://afolabianimashaun.medium.com/understanding-indexes-in-azure-synapse-to-optimize-perfomance-ccb148d1bb18

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/massively-parallel-processing-mpp-architecture

Hope this info helps
Please sign in to rate this answer.

1 person found this answer helpful.
JenP 50 Reputation points

2023-11-07T16:15:42.3833333+00:00

Thanks for that answer, it was helpful!

I guess my question is when you say "Non-clustered indexes are stored separately from the table and serve as pointers to the physical storage, which is usually unordered.", where does that "stored separately" part occur? It sounds like maybe that's on the compute node, given that it data movement between distributions on the same node wouldn't be affected...?

Harishga 5,990 Reputation points Microsoft Vendor

2023-11-08T10:29:35.44+00:00

Hi JenP
Yes, you are correct. Non-clustered indexes are stored separately from the table and are usually stored on the compute node. They serve as pointers to the physical storage, which is usually unordered. When data movement occurs between distributions on the same node, the non-clustered indexes are not affected because they are stored separately from the table. However, when data movement occurs between nodes, the non-clustered indexes need to be rebuilt to reflect the new distribution of data. You can find more information about indexing tables in dedicated SQL pool in Azure Synapse Analytics.
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index

JenP 50 Reputation points

2023-11-08T16:27:53.96+00:00

@Harishga Thanks for your answer, that's really interesting. What I thought I was seeing in the query plan was that for any data movement, it wasn't actually moving the data between node distributions per say, but instead just moved the required data into the shared tempdb and then performed the rest of the query off of that. In a case like that, I was assuming that the index could really only be used to find what data was required for that move, then once it was in tempdb it would be its own new table without an index.

Does this mean that the data movement actually does move data in the physical storage to the required distribution? And I guess then move it back afterwards? Everything I've read just sort of vaguely says that it "moves data", but I can't picture the details about what that truly means (and the video in the documentation about DMS seems to contradict other things I've read so this is pretty confusing. For instance, the video shows that data movement is a shuffle to the relevant tempdb on the relevant nodes. However, in an answer to a question I asked here, I was told:

"Each compute node has its own local storage, but tempDB is a shared resource that is used by all compute nodes. This means that any operation that requires tempDB will write to the same tempDB, regardless of which compute node is executing the operation."

I think what I need is clarification on what happens physically to the data during data movement, how tempdb is used during that, and then maybe that will help me picture what's happening to the indexes (non-clustered or otherwise).

Harishga 5,990 Reputation points Microsoft Vendor

2023-11-09T14:50:22.21+00:00

Hi JenP
Thank you for your response.
During data movement in Azure Synapse, parts of distributed tables are moved to different nodes for query execution. This is done through shuffle and broadcast operations, which are chosen by the SQL DW query optimizer to minimize the number of rows transferred. The Data Movement Service (DMS) coordinates data movement between Compute nodes to ensure parallel queries return accurate results.

During data movement in Azure Synapse Analytics, data is temporarily stored in the tempdb database. The data is then moved to the target distribution, and the tempdb is cleared.

The tempdb database is used as a staging area for data movement operations. When data is moved between distributions or nodes, it is first copied to the tempdb database on the source node. The data is then compressed and encrypted and moved to the tempdb database on the target node. Finally, the data is uncompressed and decrypted, and written to the target distribution.

Regarding non-clustered indexes, they are stored on the compute node where the data resides. When data is moved between nodes or distributions, the non-clustered indexes are not moved. Instead, they are rebuilt on the target distribution after the data movement is complete.

If the data movement is always just a copy into tempdb as a heap, then it sort of "loses" the benefit of the original index. However, the indexes are rebuilt on the target distribution after the data movement is complete, so the benefits of the index are not lost permanently.

Reference link:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver16

I hope this information helps you. Let me know if you have any further questions or concerns.

JenP 50 Reputation points

2023-11-09T16:29:56.14+00:00

Hi @harishgada

When you say:

The tempdb database is used as a staging area for data movement operations. When data is moved between distributions or nodes, it is first copied to the tempdb database on the source node. The data is then compressed and encrypted and moved to the tempdb database on the target node. Finally, the data is uncompressed and decrypted, and written to the target distribution.

does that contradict @AnnuKumari-MSFT answer I quoted earlier that said:

"Each compute node has its own local storage, but tempDB is a shared resource that is used by all compute nodes. This means that any operation that requires tempDB will write to the same tempDB, regardless of which compute node is executing the operation."

Your answer indicates that there's a tempdb on the nodes, but the other answer indicates that it's one shared tempdb that all the nodes use.

Harishga 5,990 Reputation points Microsoft Vendor

2023-11-14T12:16:45.2733333+00:00

Hi JenP

The tempdb database is a shared resource that is used by all compute nodes in a dedicated SQL pool.
Each compute node also has its own local storage, where it can store intermediate results during query execution.
These intermediate results are written to the tempdb database on the local storage of the source node, and then moved to the tempdb database on the local storage of the target node.
This way, the data movement operations are more efficient and do not consume the shared tempdb space.

The shared tempdb space is allocated based on the size of the dedicated SQL pool.
For example, for every DW100c configured, 399 GB of tempdb space is allocated (DW1000c would have 3.99 TB of total tempdb space).
The shared tempdb space is used for temporary tables and other operations that require tempdb, such as sorts, loads, memory spills, etc. High utilization of the shared tempdb space can lead to slow query performance or errors.

Therefore, there is no contradiction between the two answers. The first answer is about the local storage of each compute node, where the data movement operations are performed. The second answer is about the shared tempdb space, where the temporary tables and other operations are performed.

JenP 50 Reputation points

2023-11-14T16:30:01.25+00:00

Thanks for your response @Harishga

I think a big part of my confusion is coming from when I see a query plan like the one below. When it's doing a shuffle or broadcast move, it seems like it's creating a shared tempdb table and the moves are going directly into it. Since nothing in the location column says DMS, maybe it's saying it skipped the local storage tempdbs on each compute node? Is there a such thing as a shuffle or broadcast move that's not actually a "move"?

Out of curiosity, the space allocated to the shared tempdb is 399GB per DW100c...what's allocated for the local storage ones?

Harishga 5,990 Reputation points Microsoft Vendor

2023-11-16T10:53:43.5733333+00:00

Hi JenP
The query plan shows the logical steps that the dedicated SQL pool optimizer has chosen to execute the query, not the actual data movement operations.

The shuffle and broadcast moves are data movement operations that use a direct TCP connection between nodes, without going through the Data Movement Service (DMS). They are shown as tempdb operations in the query plan, because they create temporary tables in the shared tempdb database to store the intermediate results.

The shared tempdb database is a special database that is accessible by all compute nodes, and it is used for data movement, sorts, spills, and other operations that require temporary storage. The space allocated to the shared tempdb database is 399 GB per DW100c.

The local tempdb databases are used for operations that do not require data movement, such as temporary tables, table variables, and local sorts. The space allocated to the local tempdb databases is 32 GB per DW100c. The local tempdb databases are not visible in the query plan, because they are not shared across nodes.

JenP 50 Reputation points

2023-11-16T18:01:14.0766667+00:00

Thanks for that explanation @Harishga , that's really interesting information!

You say:

The shuffle and broadcast moves are data movement operations that use a direct TCP connection between nodes, without going through the Data Movement Service (DMS). They are shown as tempdb operations in the query plan, because they create temporary tables in the shared tempdb database to store the intermediate results... it is used for data movement, sorts, spills, and other operations that require temporary storage. The local tempdb databases are used for operations that do not require data movement, such as temporary tables, table variables, and local sorts.

but in your response on the 14th, you say:

...the local storage of each compute node, where the data movement operations are performed. The second answer is about the shared tempdb space, where the temporary tables and other operations are performed.

To add to all that, in the video in the Synapse architecture documentation, it seems like the local tempdb is the one used for data movement and it never even mentions how the shared tempdb comes into the picture. They say:

"I need to basically shuffle that sales account territory, I just go directly from the distributed tables into the actual tempdb on the other compute nodes, and that happens just across the board. We have knowledge of the topology so we can move the data directly where we know it needs to go. So you just eliminated an entire step, forget the DMS service, I'm just going to take the data, put it into tempdb on the other nodes, do the shuffle, bring the data back."

JenP 50 Reputation points

2023-11-16T18:07:34.3566667+00:00

@Harishga

You say:

The shared tempdb database is...used for data movement, sorts, spills, and other operations that require temporary storage...The local tempdb databases are used for operations that do not require data movement, such as temporary tables, table variables, and local sorts.

In your previous answer, you said

...the local storage of each compute node, where the data movement operations are performed. The second answer is about the shared tempdb space, where the temporary tables and other operations are performed.

And then in the official Synapse architecture documentation, the video says that the movement happens between the local tempdb and it never mentions the shared tempdb at all:

I need to basically shuffle that sales account territory, I just go directly from the distributed tables into the actual tempdb on the other compute nodes, and that happens just across the board. We have knowledge of the topology so we can move the data directly where we know it needs to go. So you just eliminated an entire step, forget the DMS service, I'm just going to take the data, put it into tempdb on the other nodes, do the shuffle, bring the data back.

Harishga 5,990 Reputation points Microsoft Vendor

2023-11-20T10:17:08.4833333+00:00

Hi JenP
I apologize for any confusion my previous answer may have caused.

Depending on the specific scenario. The shared tempdb database is used for data movement, sorts, spills, and other operations that require temporary storage. The local tempdb databases are used for operations that do not require data movement, such as temporary tables, table variables, and local sorts.

In the video you mentioned, the speaker is referring to a specific scenario where data movement is happening between the local tempdb databases on different compute nodes, rather than using the shared tempdb. This is possible because Synapse has knowledge of the topology and can move the data directly where it needs to go. However, it is important to note that the shared tempdb is still a crucial component of the Synapse architecture and is used for many data movement operations.

JenP 50 Reputation points

2023-11-20T16:20:40.4266667+00:00

Thanks @Harishga that was helpful and I learned a lot. I appreciate your thorough answers!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Where does a Synapse dedicated index "live"?

0 additional answers

Your answer