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…
Month: August 2022
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…
Synapse Dedicated SQL Pool Execution Plans
Synapse execution plans are specifically for distributed SQL (DSQL) and are not the same as SQL Server execution plans. SQL Server execution plans show the types of join patterns (hash, nested, loop), bookmark lookups, sorts, etc… Whereas Synapse execution plans show you how data must move between the different distributions of a Synapse Dedicated SQL…