SQL SERVER INTERNALS ARCHITECTURE Kevin Kline SQL Sentry

DROPPING ACID - WHY DOES SQL SERVER DO WHAT IT DOES? • ACID properties

OK, WE’RE DONE Query Tree SELECT Language Event Protocol Layer Relational Engine Cmd Parser

QUERY OPTIMIZATION Query Processor / Relational Engine Parser Query Optimizer Query Executor • Optimization

SCHEDULERS, THREADS, AND WAITS • 1 Cash Register = 1 scheduler • Users are

SQL SERVER WAITS Resource Waits Running Scheduler 1 55 53 Running Runnable Scheduler 1

TROUBLESHOOTING WAIT STATS? SOS_Scheduler_Yield Query Tree Relational Engine Cmd Parser Optimizer Writelog, Logbuffer Query

MEMORY MANAGER: BUFFER POOL Memory allocations within sqlservr process space OS OS CLR, MPA,

CACHES? • How long does a page of data or a block of code

PLAN CACHE AGING Plan Cache get_order 12 16 16 14 13 15 reset_user What

BUT WAIT! THERE’S MORE! Query Tree Language Event Protocol Layer Relational Engine Cmd Parser

HEKATON, A. K. A. INMEMORY OLTP Query Tree Language Event Relational Engine Cmd Parser

SUMMARY Underst an other bi ding the intern a t of info you mig

Slides: 14

Download presentation

SQL SERVER INTERNALS & ARCHITECTURE Kevin Kline, SQL Sentry Tech Evangelist, SQL Sentry Microsoft SQL Server MVP since 2003 Twitter , Facebook, Linked. In @ KEKline Website: http: //blogs. sqlsentry. com/Kevin. Kline

DROPPING ACID - WHY DOES SQL SERVER DO WHAT IT DOES? • ACID properties of Transactions o o Atomic Consistent Isolated Durable • Speed, scalability, and performance; Maximize hardware • Competitive features

OUR TOUR GUIDE Talk nerdy to me, baby!

OK, WE’RE DONE Query Tree SELECT Language Event Protocol Layer Relational Engine Cmd Parser Optimizer Query Plan TLog SNI Query Executor OLE DB ? Storage Engine Data File Transaction Manager Access Methods Buffer Manager ? TDS SQL Server Network Interface Buffer Pool ------Data Cache ------Plan Cache

QUERY OPTIMIZATION Query Processor / Relational Engine Parser Query Optimizer Query Executor • Optimization is cost-based o Optimized for worst case scenario: “everything comes from disk” • The cost numbers may as well be unicorn freckles o Estimated costs may have no basis in current reality • Multiple phases (a. ka. “searches”) o o Pre-optimization determines if the plan is “trivial” Phase 0: simple plans: e. g. nested loops without parallelism Phase 1: quick plans: plans that can be simplified Phase 2: full plans: complex queries, parallelism, spills & spools to tempdb

SCHEDULERS, THREADS, AND WAITS • 1 Cash Register = 1 scheduler • Users are assigned to a thread Uh oh! Out of soda! No problem. Step aside… More syrup for the sodas! Yeah! I’m next in line! Goes to the waiting, i. e. “suspended queue”

SQL SERVER WAITS Resource Waits Running Scheduler 1 55 53 Running Runnable Scheduler 1 53 Runnable 56 Runnable Suspended Scheduler 1 55 52 PAGEIOLATCH_SH 54 CXPACKET 60 LCK_M_S 61 LCK_M_S 59 Runnable 52 Runnable Signal Waits

TROUBLESHOOTING WAIT STATS? SOS_Scheduler_Yield Query Tree Relational Engine Cmd Parser Optimizer Writelog, Logbuffer Query Plan Trans-action Manager: Log & Lock Mgr Locks Access Methods Data Write LCK_x, Lazywriter LCK_M_x Protocol Layer SNI Pagelatch_x, Latch_x, SQL Server Resource_Semaphore ? Storage Engine Check Point Data File(s) SQL OS Query Executor Page. IOLatch_x, OLE Async_IO_Completion, DB IO_Completion TLog Async_Network_IO Language Event Buffer Manager ? TDS Latches Network Interface Buffer Pool ------Data Cache ------Plan Cache

MEMORY MANAGER: BUFFER POOL Memory allocations within sqlservr process space OS OS CLR, MPA, DWA, TS Buffer Pool (SPA) Data cache --------Plan cache --------Other caches SQL Server 2008 R 2 & earlier Max server memor y Memory allocations within sqlservr process space Buffer Pool (SPA) Data cache -~-~-~Plan cache Max server memor y -~-~-~Other caches SQL Server 2012 & later 8

CACHES? • How long does a page of data or a block of code stay in cache? • Uses a LRU algorithm • Usually performed by the lazywriter, but can also be done by any worker thread after scheduling its own I/O

PLAN CACHE AGING Plan Cache get_order 12 16 16 14 13 15 reset_user What about buffer cache? 7 proc 11 3 3 1 0 2 7 4 5 6 proc 14 2 2 1 0

BUT WAIT! THERE’S MORE! Query Tree Language Event Protocol Layer Relational Engine Cmd Parser Optimizer Query Plan TLog Check. Point Data File SNI Query Executor OLE DB ? Storage Engine Transaction Access Manager: Methods Log & Lock Data Write Mgr Lazywriter INSERT, UPDATE, or DELETE Buffer Manager ? TDS SQL Server Network Interface Buffer Pool ------Data Cache Oooh! So dirty! ------Plan Cache

HEKATON, A. K. A. INMEMORY OLTP Query Tree Language Event Relational Engine Cmd Parser Optimizer Query Plan TLog Trans-action Manager: Log & Lock Mgr Locks Access Methods Data Write Lazywriter Protocol Layer SNI ? Storage Engine Check Point Data File(s) SQL OS Query Executor OLE DB bit. ly/1 u. Lr. XLN - Ovw bit. ly/1 u 4 n. ODQ - WP Buffer Manager ? TDS Latches SQL Server Network Interface Buffer Pool ------Data Cache ------Plan Cache

SUMMARY Underst an other bi ding the intern a t of info you mig ls is as importan ht have t as any Remem ber: ACID!!! key com p onents o f the rel f the sto ational e ngine? rage en gine? s of cach e? Key area s of the transact ion man What tw ager? o proce s s es cond More in uct writ fo? es? Key area