SQL 2016 From https://www.sqlservertutorial.net/getting-started/what-is-sql-server/ SQL Server Operating System - SQLOS.

1         Architecture 1.1         Relational Engine -          It prepares the query execution plan to execute the query.  Once plan is prepared it give to Storage engine -          Relational Engine has o   Query Parser – check for syntax error o   Compiler – converts to machine language o   Query governor – Prepares plan to execute query called Execution Plan 1.1.1        Query Processing 1.1.2        Memory Management 1.1.3        Thread and Task Management 1.1.4        Buffer Management 1.1.5        Distributed Query Processing 1.2         Storage Engine -          Actual execution takes place   1.3         Storage Structure (.mdf, .ndf, .ldf) -          Records >> Pages >> Extents >> Files >> Databases -          .mdf – Primary Data file / .ndf – Secondary data file / ldf – Transaction Data file 1.3.1        Pages (Page = 8 KB size) 1.3.1.1         Data Page -          This page stores data (mdf or ldf) 1.3.1.2         Index Page -          It provides pointers to data.  This pointer used for quickly locating actual data which gives better performance (.mdf or .ldf) 1.3.1.3         Free space Page -          It is going to list out all empty pages inside data page.  It will check which page have space / full. 1.3.2        Extents -          An extent is 8 continuous page arranged sequentially one by one.  We are storing all data in the form of Extents to make space allocation efficient.  1.3.2.1         Uniform Extent (Dedicated Extent) -          All pages have same data related to same object. 1.3.2.2         Mixed Extent -          Pages related to different object 1.3.3        Files -          All the extents are stored in the form of files – either .mdf or .ldf files. 1.3.3.1         Ldf -          Ldf stores modified data. -          Buffer stores whatever changes done, it will record actions in buffer (transaction log) -          Transaction should process all ACID properties then only the changes move from ldf to mdf files. -          Transaction Log divided into two parts o   Active Portion (Transactions which are in processing state) – checkpoint moves once it is committed o   Inactive Portion (committed moved  mdf then moved to inactive) 1.3.3.1.1         ACID Properties 1.3.3.1.1.1        A-Atomicity -          Statements to be executed FULL or NONE 1.3.3.1.1.2        C-Consistency -          Modification on parent should reflect in child tables. 1.3.3.1.1.3        I-Isolation -          One transaction should not depend on other transaction.  Users can access multiple transactions at a time. 1.3.3.1.1.4        D-Durability -          Once any transaction is updated it should be a permanent change in mdf file. 1.3.3.2         mdf -          stores permanent data 1.3.3.3         Mechanism 1.3.3.3.1         Write Ahead Logging (WAL) -          Before going to mdf data log in ldf file to check consistency, this is called WAL.  Once concistency check done it move to mdf file which are not committed will be rolled back. 1.3.3.3.2         Check Point Process -          Chcek point is a process of checking transactions. It checks the transactions if any committed data move to mdf file and failed transactions flushed out of log file. -          It helps in speeding of recovery process. 1.3.3.3.3         Recovery Process -          Whenever sql server started it scans log file and check what is the state of log file.  In this we have two process o   Roll Back o   Roll Forward -          If Checkpoint runs in regular interval there will be no pending files so that recovery process will be very fast.   For running check point there is no time interval.  It depends on transactions it runs   1.3.3.3.4         Lazy Writer -          Most of the time it is in sleep mode. Whenever RAM space is less, it invokes and clears buffer pages. -          It checks how many times this page was used  - this is called refered pages aka Reference Counter. -          ??? By using this reference counter least refered pages will be deleted. 1.3.3.3.5         Dirty Page -          The page modified in buffer and not yet committed in mdf file called dirty page. 1.3.4        Database -          All the files make database. 1.3.5        FileGroup -          Logically dividing database into groups 1.3.6        Fill Factor -          Default Index Fill factor is 0 -          Settings : o   High Fill Factor  - there will be no space for new things – which goes to next page o   Low Fill Factor  - It allocates data in more page -  It affects performance o   Ideal fill factor is 70% to 80% -            1.4         OS Info 1.4.1        Processors -          Inside processor many threads are there, each thread is executing one task.  Depends on processor number of max threads differ. -          https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017 -