Category: Performance Tuning

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…

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…

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…

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…

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…

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…

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…

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…