1.Storage

 

1         Storage or Files -          SELECT * FROM SYS.sysfiles 1.1         Files 1.1.1        Data file (.mdf, .ndf) -          Only one database is allowed per database file, but a single database can span multiple files. -          One primary (.mdf) file will be available per database which is autocreated -          N-number of .ndf  (n=non-Primary) secondary files  can be used which newly added by user. -          Max 32767 files we can use -          Each file assigned to one file group either PRIMARY (default) or newly created 1.1.1.1         Pages and extents -          https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-2017 -          Page = 8 KB -          Extent = A collection of 8 Consecutive pages size 64 KB -          Types of Extent o   Uniform Extent o   Mixed Extent -          Data allocation occurs at extent level -            1.1.2        Log file (.ldf) -          When you create a database and do not specify the size of the log file, the log file will be 25% of the database file but minimum is 1MB by default. All the data and log  files must reside on the same computer as SQL Server. -          Log file always store in a separated drive - dedicated drive is good -          Log file is entry gate for data - from log file the data distributed to respective files -          Log file use filegroup to route the correct data file -          Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. -          The transaction log is a critical component of the database and, if there is a system failure, it can be the only source of recent data. -          The transaction log is not a part of any Filegroup. One data base can have multiple log files but no filegroup -          EVERY OPERATION IS AUTO AUDITTED IN "LOG FILE". THEN THE DATA IS SENT TO "DATA FILE" -          LOG FILES ARE RECOMMENDED TO BE PLACED ON A DEDICATED DRIVE. -          SIZE  OF LOG FILE = 25% OF TOTAL DATA FILES INITIAL SIZE -          Every operation is audit / written into log file before writing into Data file.  This mechanism is called “WRITE AHEAD LOG”.  This process is governed by Master Database process called “Lazy Writer” -          Complete log file is divided into  VLF “Virtual Log Files” -          Each VLF is of size 512 KB -          Each VLF has a start location address “MINI LSN” -          Files are divided into pages -          EXTENT  = collection of 8 pages -          Lazy Writter - Write Ahead Log Machenism -          VLF = virtual Log File -          Can I change the table by redefining defferent filegroup -          Watch one more time from 30" -            1.2         FileGroup -          SELECT * FROM SYS.filegroups -          Data files can be grouped together in FILEGROUPS for easy data allocation. -          A Filegroup can contain multiple secondary data files inside it. -          Max 32767 filegroup we can use -            1.3         Checkpoint -          Checkpoint File Pairs (CFP) -          Checkpoint processes for In-Memory OLTP tables are responsible for writing committed data from the transaction log files to data and delta files. In-Memory OLTP tables use Data and Delta file pairs (also known as Checkpoint File Pairs (CFP)) to store the data.  They are o   Data Files – Files usually of size 128 mb, used to store the inserted data o   Delta Files - Files usually of size 16 mb, used to store pointers to Data files for the rows that were deleted. -          Data and Delta files are always occurred in pairs. In other words, there is always a Delta file for a Data File. Checkpoint processes for In-Memory OLTP tables write to smaller sized data and delta files, instead of larger data files as in disk based tables. 1.4         B Tree -          Whenever we create  Index, the storage engine generate B Tree.  The structure is o   Root o   Intermediate Pages o   Leaf Pages -          To know all the indexs on the table o   exec sp_helpindex 'tblName'