I covered some of the basics of table geometry in this post: Synapse Fundamentals for Performance Tuning – Distribution; however, it’s critical to dive deeper into the performance impact of having the correct table distribution defined. Sometimes we think we have the correct distribution chosen for a table but are painfully mistaken as performance suffers. Generally, that…
Category: Top 10 Performance Considerations
Synapse Fundamentals for Tuning – CPU Intensive Operations
This performance consideration is not unique to Synapse but can be exasperated in Synapse due to the quantity of data that is being worked with. The CPU intensive operations that I am referring to come in three different flavors. These topics all have one thing in common. They are operations that could have been pushed…
Synapse Fundamentals for Tuning – Computed Columns
So why are computed columns such a big performance consideration in Synapse Dedicated SQL Pools? There are two main reasons: Many times, computed columns are just a variation of the data that was originally stored in the source system. Instead of transforming the data as part of an ELT (Extract, Load, and Transform) process, a…
Synapse Fundamentals for Tuning – Nesting
Calling stored procedures from other stored procedures or referencing a view within another view can introduce performance complexities that are difficult to troubleshoot. I will breakdown views and stored proc nesting issues individually, but the same message applies to both. Avoid heavily nested objects in Synapse if reliable performance is a priority. Understandably, we like…
Synapse Fundamentals for Tuning – Partitioning
Next on my list of top performance killers in Synapse Dedicated SQL Pools, is Partitioning. Partitioning is too often overused in Synapse. Let’s first talk about when you would use partitioning and then how to use it effectively. Partitioning should only be applied on a table with a very large number of records and even…
Synapse Fundamentals for Tuning – Indexes Part2
Rowstore Index Health For SQL Server, rowstore index fragmentation is a critical indicator for performance. In Synapse Dedicated SQL Pools, the same holds true but high fragmentation is generally less of a performance impact since tables are distributed. This however doesn’t mean that clustered/non-clustered indexes and heaps don’t have to be rebuilt. Due to the…
Synapse Fundamentals for Tuning – Indexes Part1
If you have been around SQL Server for any length of time, you know by now that indexes are critical for performance. In Synapse Dedicated SQL Pools, indexes play a lesser role in query tuning because they do not impact the DSQL plans but rather impact the SQL plans on each of the distributions for…
Synapse Fundamentals for Performance Tuning – Distribution
If you are coming from a SQL Server world, what are the fundamental concepts in Synapse that are essential to building a performant Dedicated SQL Pool? How does Synapse differ from regular SQL Server? Let’s start with the most basic foundational principle of distribution. You might hear the term “MPP” thrown around when we talk…
Synapse Fundamentals for Performance Tuning – Statistics
Statistics in Synapse Dedicated SQL Pools is without question the most important and the most frequently overlooked reason for poor query performance. SQL Server is forgiving in that autocreate and autoupdate statistics are frequently enabled but Synapse only offers autocreate statistics. Autoupdate statistics is not an option available in Synapse today. This leads to a…