Overview of Triggers. Like a stored procedure, a trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly. Unlike a stored.
Here are the list of Sql Server Architecture Questions and Answers which are asked in SQL Server developer / DBA interviews. Q. Can you draw SQL Server architectural diagram with all the components? Ans: Network Flow – SQL Server Architecture. Q. Can you explain the network flow in SQL Server Architecture? Ans: These steps are defined as follows: 1. The user selects an option in a client application. This option calls a function in the client application that generates a query that is sent to SQL Server. The application uses a database access library to send the query in a way SQL Server can understand. The database library transforms the original request into a sequence of one or more Transact- SQL statements to be sent to SQL Server. These statements are encapsulated in one or more Tabular Data Stream (TDS) packets and passed to the database network library to be transferred to the server computer. The database network library uses the network library available in the client computer to repackage the TDS packets as network protocol packets. The network protocol packets are sent to the server computer network library across the network, where they are unwrapped from their network protocol. The extracted TDS packets are sent to Open Data Services (ODS), where the original query is extracted. ODS sends the query to the relational engine, where the query is optimized and executed in collaboration with the storage engine. The relational engine creates a result set with the final data and sends it to ODS. ODS builds one or more TDS packets to be sent to the client application, and sends them to the server database network library. The server database network library repackages the TDS packets as network protocol packets and sends them across the network to the client computer. A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is executed whenever an event associated with a table occurs e.g., insert. The client computer receives the network protocol packets and forwards them to the network libraries where the TDS packets are extracted. The network library sends the TDS packets to the database access library, where these packets are reassembled and exposed as a client result set to the client application. The client application displays information contained in the result sets to the user. Pages and Extents. Q. What is a page in database systems and tell me something about pages? Ans: The fundamental unit of data storage in SQL Server is the page. Page size is 8kb means 1. MB. Page starts with the header of 9. The maximum size of a single row on a page is 8. But this restriction is relaxed for tables which are having varchar, nvarchar, Varbinary, Taxt or Image columns. Q. Any idea what is “ROW_OVERFLOW_DATA allocation unit”? Ans: When the total row size of all fixed and variable columns in a table exceeds the 8,0. SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit. This is done whenever an insert or update operation increases the total size of the row beyond the 8. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 2. IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page. Q. Can you briefly describe about EXTENT? Ans: An extent is eight physically contiguous pages, or one extent = 6. KB. That means 1. Extents= 1 MB. There are two types of Extents. Uniform Extents and Mixed Extents. Uniform extents are owned by a single object; Mixed extents are shared by up to eight objects. Q. What are the different Types of Pages available? Ans: GAM and SGAM (Global Allocation Map & Shared GAM): GAM: Extents have been allocated: 1 – Free space 0 – No space. SGAM: Mixed Extents have been allocated: 1 – Free Space + Mixed Extent and 0 – No space. Each GAM / SGAM covers 6. GBPFS (Page Free Space): Percentage of free space available in each page in an extent. DCM (Differential Changed Map): This tracks the extents that have changed since the last BACKUP DATABASE statement. Modified, 0 – Not modified. BCM (Bulk Changed Map): This tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement. Modified, 0 – Not modified (Used only in bulk logged Recovery model)In each data file pages are arranged like below. Along with that we have three different data pages. Data. Index. Text/ Image (LOB, ROW_OVERFLOE, XML)Files and File Groups. Q. Have you ever heard the word “Files” or “File Groups” in SQL Server? Ans: Files: There are three types, Primary- . Secondary – . ndf and Log files – . File Groups: There are two, Primary File Group – All system tables and User Defined – Depends. All secondary files and user defined file groups are created to optimize the data access and for partitioning the tables. Q. What are the advantages and disadvantages over using filegroups? Ans: Advantages: Using filegroups, you can explicitly place database objects into a particular set of database files. For example, you can separate tables and their nonclustered indexes into separate filegroups. This can improve performance, because modifications to the table can be written to both the table and the index at the same time. This can be especially useful if you are not using striping with parity (RAID- 5). Another advantage of filegroups is the ability to back up only a single filegroup at a time. This can be extremely useful for a VLDB, because the sheer size of the database could make backing up an extremely time- consuming process. Yet another advantage is the ability to mark the filegroup and all data in the files that are part of it as either read- only or read- write. Disadvantages. The first is the administration that is involved in keeping track of the files in the filegroup and the database objects that are placed in them. The other is that if you are working with a smaller database and have RAID- 5 implemented, you may not be improving performance. Transaction log Architecture. Q. Can you talk about “Transactionlog” Logical and Physical architecture? Ans: “Transactionlog” Logical Architecture: Each log record is identified by a unique number called LSN (Log Sequence Number). A log record contains the LSN, Transaction. ID to which it belongs and data modification record. Data modification record: It’s either operation performed or before and after data image. When recorded “Operation Performed”Transaction committed – Logical Operation is permanently applied to the data. Transaction rollback – Reverse Logical Operation is applied to the data. When Recorded “Before and After Data Image”Transaction committed – Applied the after transaction image. Transaction rollback – Applied the before transaction image“Transactionlog” Physical Architecture: The transaction log is used to guarantee the data integrity of the database and for data recovery. The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations. Q. What are the Check. Points in SQL Server database? Ans: Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During a full recovery, the following types of actions are performed: The log records of modifications not flushed to disk before the system stopped are rolled forward. All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back. Before a database backup, the Database Engine automatically performs a checkpoint so that all changes to the database pages are contained in the backup. Also, stopping a server issues a checkpoint in each database on the server. Q. What is an Active Log? Ans: The section of the log file from the Min. LSN to the last- written log record is called the active portion of the log, or the active log. This is the section of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log records must be truncated from the parts of the log before the Min.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |