Introduction to Synapse
SQL pool internals
Jan Remko Yntema
Data Platform Architect
ilionx
jryntema@ilionx.com
SMP vs. MPP Architecture
VS
Separate compute from storage
SQL
Control
Compute
Premium Storage
Independently scale compute
SQL
Control
Compute
Premium Storage
Pause and resume workload SQL
Control
Compute
Premium Storage
Resource classes
Dynamic
Increases resource consumption as you scale
No increase in concurrency as you scale
Static
Maintain resource consumption as you scale
Increase concurrent queries as you scale
Increase memory
Consume Slots
Isolate resources
Gen 2 Concurrency – Static RC
Maximum Concurrency
Service concurrent slots staticrc10 staticrc20 staticrc30 staticrc40 staticrc50 staticrc60 staticrc70 staticrc80
Level queries available
DW100c 4 4 1 2 4 4 4 4 4 4
DW200c 8 8 1 2 4 8 8 8 8 8
DW300c 12 12 1 2 4 8 8 8 8 8
DW400c 16 16 1 2 4 8 16 16 16 16
DW500c 20 20 1 2 4 8 16 16 16 16
DW1000c 32 40 1 2 4 8 16 32 32 32
DW1500c 32 60 1 2 4 8 16 32 32 32
DW2000c 48 80 1 2 4 8 16 32 64 64
DW2500c 48 100 1 2 4 8 16 32 64 64
DW3000c 64 120 1 2 4 8 16 32 64 64
DW5000c 64 200 1 2 4 8 16 32 64 128
DW6000c 128 240 1 2 4 8 16 32 64 128
DW7500c 128 300 1 2 4 8 16 32 64 128
DW10000c 128 400 1 2 4 8 16 32 64 128
DW15000c 128 600 1 2 4 8 16 32 64 128
DW30000c 128 1200 1 2 4 8 16 32 64 128
Dynamic Resource Classes
4 resources classes
Resource Class Percentage Memory
smallrc 3%
mediumrc 10%
largerc 22%
xlargerc 70%
Gen 2 Concurrency – Dynamic RC
Maximum
concurrent Concurrency Slots used by Slots used by Slots used by Slots used by
Service Level queries slots available smallrc mediumrc largerc xlargerc
DW100c 4 4 1 1 1 2
DW200c 8 8 1 1 1 5
DW300c 12 12 1 1 2 8
DW400c 16 16 1 1 3 11
DW500c 20 20 1 2 4 14
DW1000c 32 40 1 4 8 28
DW1500c 32 60 1 6 13 42
DW2000c 32 80 2 8 17 56
DW2500c 32 100 3 10 22 70
DW3000c 32 120 3 12 26 84
DW5000c 32 200 6 20 44 140
DW6000c 32 240 7 24 52 168
DW7500c 32 300 9 30 66 210
DW10000c 32 400 12 40 88 280
DW15000c 32 600 18 60 132 420
DW30000c 32 1200 36 120 264 840
Operations not governed by resource classes
Some queries always run in the smallrc resource class even though the user is a member of a larger resource
class. These exempt queries do not count towards the concurrency limit.
The following statements are exempt from resource classes and always run in smallrc:
•CREATE or DROP TABLE
•ALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
•ALTER INDEX DISABLE
•DROP INDEX
•CREATE, UPDATE, or DROP STATISTICS
•TRUNCATE TABLE
•ALTER AUTHORIZATION
•CREATE LOGIN
•CREATE, ALTER, or DROP USER
•CREATE, ALTER, or DROP PROCEDURE
•CREATE or DROP VIEW
•INSERT VALUES
•SELECT from system views and DMVs
•EXPLAIN
•DBCC
Maximize your ROI through granular
workload control
Dynamically allocate your data
warehouse resources using classifiers
Prioritize your Prioritize your most essential tasks
with workload importance
workloads using
workload management
Scheduler without importance
CEO CEO CEO
1 2 9
3 4 10
5 6 7 8 11
10
9 12
10 11 12
What if you want to
prioritize the workloads that Running Queued
Queued
Queued
get access to resources? By default, workloads are run on a first-in first out basis.
CREATE WORKLOAD CLASSIFIER classifier_name
WITH
(
WORKLOAD_GROUP = 'name’ ,
MEMBERNAME = 'security_account' [ [ , ]
IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL (default) | ABOVE_NORMAL | HIGH }])
Scheduler With Importance Turned On
CEO CEO
1 2 12
3 4 5 6 7 8 9 10 11 12
With workload importance,
Normal Low Normal High
prioritized workloads
take precedence
Running Queued
Queued
Row store & Column store
ROW STORE COLUMN STORE
Table and Index Terminology
Primary Data Stores
Heap = Base Row Store
Clustered Index (CI) = Base Row Store maintained as a B-Tree
Clustered Columnstore Index (CCI) = Base Column Store
Secondary Data Stores
Non Clustered Index (NCI) = Secondary B-Tree Index
NCI can be on Heap
NCI can be on Clustered Columnstore Index (NCI on CCI)
Non Clustered Columnstore Index (NCCI) = not supported in SQLDW
Column store taxonomy
Data Row Group Segments Column store
2034857,23552534,26262569085923458958294582342-52935-2385349085295-
25894-589245-285928592-5845829582-58258295849058-28592-
582945824059829485290584095895845902859028592045829458259820589582905 2034857,23552534,26262569085923458958294582342-52935-2385349085295-25894-
82945082905825-2502-45905-93245,vitoortkgldkggjwov j4o534585- 589245-285928592-5845829582-58258295849058-28592-
0348565920345234059=3405943=- 5829458240598294852905840958958459028590285920458294582598205895829058294508
5923405=23950345923=509235=239560235932=46942306496046940693=46043693 2905825-2502-45905-93245,vitoortkgldkggjwov j4o534585-
b069,hb05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t- 0348565920345234059=3405943=-
=8349652- 5923405=23950345923=509235=239560235932=46942306496046940693=46043693b069,hb
=856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw0 05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t-=8349652-
6=62=96069,b]si5=96292500000- =856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw06=62=96
2034857,23552534,26262569085923458958294582342-52935-2385349085295- 069,b]si5=96292500000-2034857,23552534,26262569085923458958294582342-52935-
25894-589245-285928592-5845829582-58258295849058-28592- 2385349085295-25894-589245-285928592-5845829582-58258295849058-28592-
582945824059829485290584095895845902859028592045829458259820589582905 5829458240598294852905840958958459028590285920458294582598205895829058294508
82945082905825-2502-45905-93245,vitoortkgldkg vlgjwov j4o534585- 2905825-2502-45905-93245,vitoortkgldkg vlgjwov j4o534585-
0348565920345234059=3405943=- 0348565920345234059=3405943=-
5923405=23950345923=509235=239560235932=46942306496046940693=46043693 5923405=23950345923=509235=239560235932=46942306496046940693=46043693b069,hb
b069,hb05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t- 05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t-=8349652-
=8349652- =856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw06=62=96
=856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw0 069,b]si5=96292500000-2034857,23552534,26262569085923458958294582342-52935-
6=62=96069,b]si5=96292500000- 2385349085295-25894-589245-285928592-5845829582-58258295849058-28592-
2034857,23552534,26262569085923458958294582342-52935-2385349085295- 582945824059829485290584095892034857,23552534,26262569085923458958294
25894-589245-285928592-5845829582-58258295849058-28592-
582945824059829485290584095892034857,23552534,26262569085923458958294
582342-52935-2385349085295-25894-589245-285928592-5845829582-
58258295849058-28592-
582945824059829485290584095895845902859028592045829458259820589582905
82945082905825-2502-45905-93245,vitoortkgldkggjwov j4o534585-
0348565920345234059=3405943=- 2034857,23552534,26262569085923458958294582342-52935-2385349085295-25894-
5923405=23950345923=509235=239560235932=46942306496046940693=46043693 589245-285928592-5845829582-58258295849058-28592-
b069,hb05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t- 5829458240598294852905840958958459028590285920458294582598205895829058294508
=8349652- 2905825-2502-45905-93245,vitoortkgldkggjwov j4o534585-
=856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw0 0348565920345234059=3405943=-
6=62=96069,b]si5=96292500000- 5923405=23950345923=509235=239560235932=46942306496046940693=46043693b069,hb
2034857,23552534,26262569085923458958294582342-52935-2385349085295- 05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t-=8349652-
25894-589245-285928592-5845829582-58258295849058-28592- =856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw06=62=96
582945824059829485290584095895845902859028592045829458259820589582905 069,b]si5=96292500000-2034857,23552534,26262569085923458958294582342-52935-
82945082905825-2502-45905-93245,vitoortkgldkg vlgjwov j4o534585- 2385349085295-25894-589245-285928592-5845829582-58258295849058-28592-
0348565920345234059=3405943=- 5829458240598294852905840958958459028590285920458294582598205895829058294508
5923405=23950345923=509235=239560235932=46942306496046940693=46043693 2905825-2502-45905-93245,vitoortkgldkg vlgjwov j4o534585-
b069,hb05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t- 0348565920345234059=3405943=-
=8349652- 5923405=23950345923=509235=239560235932=46942306496046940693=46043693b069,hb
=856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw0 05,b6905869347 87-987g89-9s8g-89-89 89-89-89mg89wer-t8t9et8-t-=8349652-
6=62=96069,b]si5=96292500000- =856=8=98t0e=t9e0t=e9t09=90=39560=659450693=565096=35695=69305=69,vw06=62=96
2034857,23552534,26262569085923458958294582342-52935-2385349085295- 069,b]si5=96292500000-2034857,23552534,26262569085923458958294582342-52935-
25894-589245-285928592-5845829582-58258295849058-28592- 2385349085295-25894-589245-285928592-5845829582-58258295849058-28592-
58294582405982948529058409589 582945824059829485290584095892034857,23552534,26262569085923458958294
Row store or Column store?
• Small Data Set (< 60 million • Large Data Set (> 60 million
rows) rows)
• Frequent updates • Mostly append only data
• Small Dimension tables • Fact tables or large dimension
tables
Row store Column store
Load impact on scan performance
union all
Scans need to combine data from
locator not in compressed row groups, delta stores
and delete bitmaps to produce correct
results
Segment metadata used to eliminate
Deltastores row groups that do not qualify
Compressed Delete
rowgroups bitmap
Row & Column Store & Partition
Partitioning Guidance
Partition for data management
Lesser benefit had on partition elimination for faster performance
Don’t over partition!
Partitioning granularity likely to differ to SQL Server
Data is already spread across 60 distributions
Columnstore index row groups give ideal performance with 1 million rows each
Need at least 60 million per partition!
