SQL Server Internals Architecture Naomi Williams SQL DBA

SQL Server Internals & Architecture Naomi Williams, SQL DBA Twitter @naomithesqldba Linked. In www. linkedin. com/in/nfwilliams/

Platinum Level Sponsors Gold Level Sponsors Venue Sponsor Key Note Sponsor 2 9/15/2020 2 | | Pre Conference Sponsor

Silver Level Sponsors Bronze Level Sponsors 3 9/15/2020 3 | |

Why is there ACID in my SQL Server? ACID properties of Transactions Atomic Consistent Isolated Durable 4

Welcome to SQL Server! Where does the path begin? 9/15/2020 5

SELECT SNI (SQL Server Network Interface) SQL Server Interface aka API Protocols SQLOS TCP/IP Shared Memory Named Pipes TDS packets Virtual Interface Adapter (VIA) SNI (SQL Server Client Network Interface) 9/15/2020 6

SQLOS, Workers and Schedulers SQLOS creates a set of schedulers upon start equal to the number of logical CPU’s. Each scheduler manages workers, for every task to execute it’s assigned a worker that is in an idle state. Workers do not move between schedulers, tasks are never moved between workers. However SQLOS can create child tasks and assign them different workers. Tasks can have one of six states Pending; task is waiting for an available worker Done; task is completed Running; task is currently executing Runnable; task is waiting for the scheduler Suspended; task is waiting for external event or resource Spinloop; task is processing a spinlock 9/15/2020 7

SQLOS, Workers and Schedulers, cont Running state Suspended queue and suspended state Runnable queue and runnable state

SELECT SNI (SQL Server Network Interface) SQL Server Interface aka API Protocols SQLOS TCP/IP Shared Memory Named Pipes TDS packets Virtual Interface Adapter (VIA) SNI (SQL Server Client Network Interface) Language Event If query hash does not exist in Plan Cache ü Language ü Hash of execution plan Buffer Pool Data Cache Database Engine Cmd Parser Query Tree Optimizer Plan Cache Query Plan Does the hash exist in the Plan Cache? Query Executor 9/15/2020 YES! Query has plan exists! 9

Logical Query Tree Select * from Customers c inner join Orders o on c. cid=o. cid where o. date=‘ 2014 -12 -12’; Project (*) Filter (o. date=‘ 2014 -12 -12’) Inner Join c. cid=o. cid Get (customers) as C Get (orders) as O 9/15/2020 10

SELECT SNI (SQL Server Network Interface) SQL Server Interface aka API Protocols SQLOS TCP/IP Shared Memory Named Pipes TDS packets SNI (SQL Server Client Network Interface) Virtual Interface Adapter (VIA) Buffer Pool Language Event Data Cache Database Engine Cmd Parser Query Tree Optimizer Plan Cache Query Plan Query Executor 9/15/2020 11

Query Optimizer Parse and logical query tree Binding, do they exist? Generate physical query plan Does it make sense and how can I get there? Do the objects exist? Create path in binary from algebrizer for optimizer. Most efficient plan (path) to the data. 9/15/2020 12

Physical Query Plan 9/15/2020 13

SELECT SQL Server Interface aka API SNI (SQL Server Network Interface) Database Engine Cmd Parser Optimizer Protocols SQLOS TCP/IP Query Plan Query Tree Shared Memory TDS packets SNI (SQL Server Client Network Interface) Query Executor Named Pipes Virtual Interface Adapter (VIA) Language Event Does Data exist in Cache? OLE DB Transaction Log File Storage Engine Transaction Manager Data File Retrieves data from disk to cache Buffer Pool Access Methods Data Cache Buffer Manager 9/15/2020 14 Plan Cache

Insert, Update, Delete SQL Server Interface aka API SNI (SQL Server Network Interface) Database Engine Cmd Parser Optimizer Protocols SQLOS TCP/IP Query Plan Query Tree Shared Memory TDS packets SNI (SQL Server Client Network Interface) Virtual Interface Adapter (VIA) Language Event Check. Point Transaction Log File Storage Engine Transaction Manager Data File Query Executor Named Pipes Lock and Log manager OLE DB Buffer Pool Data Cache Access Methods Buffer Manager 9/15/2020 15 Plan Cache

WAIT, WAIT SOS_Scheduler_Yield Page. IOLatch_x, Async_IO_Completion, IO_Completion Async_Network_IO Locks LCK_x, LCK_M_x Writelog, Log. Buffer Pagelatch_x, Latch_x, Resource_Semaphore Latches Backup. IO CXPacket IO_Completion OLEDB MSQL_DQ MSQL_XP Pre. Emptive_OS_ Data File Thread. Pool 9/15/2020 16

WAIT, WAIT SQL Server Interface SOS_Scheduler_Yield, Async_Network_IO SNI (SQL Server Network Database Engine aka API CXPacket Interface) Cmd Parser Protocols TCP/IP Shared Memory TDS packets SNI (SQL Server Client Network Interface) Writelog, Log. Buffer Transaction Log File Data File LCK_x, LCK_M_x Query Plan Query Tree SQLOS Query Executor Named Pipes Virtual Interface Adapter (VIA) Language Event Page. IOLatch_x, Async_IO_Completion, IO_Completion Transaction Manager Lock and Log manager Pagelatch_x, Latch_x, Resource_Semaphore OLE DB Storage Engine Locks Optimizer Buffer Pool Latches Data Cache Access Methods Buffer Manager 9/15/2020 17 Plan Cache

Data Cache and LRU-K Buffer Pool Data Cache ABC Data File Free Buffer List Ø Every buffer has a header Ø Last two times the page was referenced Ø Status information SQLOS Lazy Writer 0 0 LAZY WRITER ABC AB C 9/15/2020 18

Plan Cache and LRU SQLOS Buffer Pool Plan Cache Resource Monitor 0 0 9 5 2 ABC 73 0 ABC 92 AB C 9/15/2020 19

Hekaton SQL Server Interface aka API SNI (SQL Server Network Interface) Natively compiled stored Database Engine Cmd Parser procedures Optimizerand schema Protocols SQLOS TCP/IP Query Plan Query Tree Shared Memory TDS packets SNI (SQL Server Client Network Interface) Query Executor Named Pipes Language Event Check. Point Memory Optimized Transaction Tables and Indexes Log File Storage Engine Transaction Manager Data File In-Memory OLTP Compiler Virtual Interface Adapter (VIA) Lock and Log manager OLE DB Buffer Pool Data Cache Access Methods Buffer Manager 9/15/2020 22 Plan Cache

Summary ACID SQLOS Components of database engine Components of storage engine Cache aging Limitations of Hekaton 9/15/2020 23

Session Evaluation This is how I get paid, so please pay me http: //www. sqlsaturday. com/492/sessions/sessionevaluati on. aspx? sid=46428 9/15/2020 24