1 / 13

SQL Server Internals & Architecture

SQL Server Internals & Architecture. Kevin Kline, SQL Sentry Director of Engineering Services, kekline@sqlsentry.net Microsoft SQL Server MVP since 2003 Twitter , Facebook, LinkedIn @ KEKline Website: http://KevinEKline.com/ , http://ForITPros.com. New eBOOK Available!.

brina
Download Presentation

SQL Server Internals & Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Internals & Architecture • Kevin Kline, SQL Sentry Director of Engineering Services, kekline@sqlsentry.net Microsoft SQL Server MVP since 2003 Twitter , Facebook, LinkedIn @ KEKline Website: http://KevinEKline.com/, http://ForITPros.com

  2. New eBOOKAvailable! Monthly tips and tricks in our eNews at http://www.sqlsentry.net/newsletter-archive.asp

  3. Win one of 3 Rookie Experience packages and 3 Ride Along packages from the Richard Petty Driving Experience at Charlotte Motor Speedway after PASS Summit 2013! October 18, 2013. Details at http://sqlsentry.net/webinarlistings.aspx

  4. 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

  5. Tools from SQL Sentry

  6. Dropping acid - Why Does SQL Server Do what it Does? • ACID properties of Transactions • Atomic • Consistent • Isolated • Durable • Speed, scalability, and • performance • Maximize hardware • Competitive features

  7. OUR TOUR GUIDE Talk nerdy to me, baby!

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

  9. SQLOS and Schedulers No problem. Step aside… More syrup for the sodas! Uh oh! The out of soda! • 1 Cash Register = 1 Scheduler • Users are assigned to a thread Yeah! I’m next in line! Goes to the waiting, i.e. “suspended queue”

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

  11. Cache Aging & LRU-K behavior Memory getord 16 14 12 16 13 15 finduser 7 4 7 5 6 sp_1 sp_4 What about buffer cache? 3 2 2 1 3 1 0 2 0

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

  13. SUMMARY • Understanding the internals is as important as any other bit of info you might have • Remember: • ACID!!! • key components of the relational engine? • key components of the storage engine? • Key areas of cache? • Key areas of the transaction manager? • What two processes conduct writes? • More info?

More Related