SQL Server Internals Architecture How it all works

SQL Server Internals & Architecture - How it all works Kevin Kline, Quest Software SQL Server Strategy Manager Microsoft SQL Server MVP since 2004 Twitter @KEKline, Website: http: //Kevin. EKline. com/ © 2010 Quest Software, Inc. ALL RIGHTS RESERVED

Your Speaker: Kevin Kline My first book Founding PASS MVP Status 1

Quest Software Swag for SQL Server Free posters, guides, and other goodies. HTTP: //www. quest. com/backstage/promotion. aspx March 2010 July 2010 Free DVD Training: HTTP: //db-management. com/live 2

Agenda • • Droppin’ Acid with RDBMSes Our Host and Tour Guide The Life of a Read Statement Dancing for Cache The Life of a Write Statement Summary Q&A 3

Relational Databases 101, or, Why the Heck SQL Server Does the Crazy Stuff it Does? !? • ACID properties of Transactions – – Atomic Consistent Isolated Durable • Speed, scalability, and • performance • Maximize hardware • Competitive features 4

Architecture & Internals – With Your Guide, The Ultimate Nerd Transaction Talk nerdy to me, baby! 5

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

Call Out – Schedulers • 1 Cash Register = 1 Scheduler Uh oh! • Users are assinged The tag to a thread fell off! No problem. Step aside… Look up on CDs! Yeah! I’m next in line! Goes to the waiting, i. e. “suspended queue” 7

Holla! Buffer Pool Structures Windows Memory, 32 -bit Buffer Pool ------Plan Cache (only RAM under the 4 GB AWE threshold can only be used for Plan Cache) ------Special caches: Sort Cache, Hashing Cache, etc…xxx ------Data Cache (any RAM available to the instance can be used for Data Cache, but RAM over the 4 GB AWE threshold can be used only for Data Cache) • Two otherwise identical servers • 16 gb of RAM • Limitations of 32 bit: • /3 gb switch opens more RAM • AWE needed to utilize anything above 4 gb on • The “give & take” dichotomy Windows Memory, 64 -bit Buffer Pool ------Plan Cache (no restrictions) ------Special caches: Sort Cache, Hashing Cache, etc…xxx ------Data Cache (no restrictions) 8

Hands in da Air! Private Dancer – Dances for Cache • 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 9

Procedure Plan Aging Memory getord 16 14 12 16 13 15 finduser 7 sp_1 3 3 1 0 2 7 4 5 6 sp_4 2 2 1 0 10

But Wait! There’s More! Query Tree Language Event Protocol Layer Relational Engine Optimizer Query Plan INSERT, UPDATE, or DELETE Cmd Parser SNI Query Executor TDS TLog OLE DB ? Storage Engine Data File Transaction Manager: Log & Lock Mgr Access Methods Data Write SQL Server Network Interface Buffer Manager ? Buffer Pool ------Data Cache Oooh! So dirty! ------Plan Cache 11

SUMMARY Underst an any othe ding the intern al r bit of i nfo you s is as importan might h t as Remem ave ber: The key co engine? mponents of th e relatio nal The key co engine? mponents of th e storag e Key area s of cach e? Key area s of the transact ion man Lazywrit ager? er and c heckpoi More in nt proce fo? sses 12

Quest Software Swag for SQL Server Free posters, guides, and other goodies. HTTP: //www. quest. com/backstage/promotion. aspx March 2010 July 2010 Free DVD Training: HTTP: //db-management. com/live 13

Questions ? • • • Send questions to me at: kevin. kline@quest. com Twitter @kekline Blogs at SQLServer. Pedia. com, SQLblog. com, SQLMag. com Rate Me – http: //Speaker. Rate. com/kekline/ Content at http: //Kevin. EKline. com/Slides/ © 2010 Quest Software, Inc. ALL RIGHTS RESERVED