Performance Tuning – SQL Server Part -2 – Q. What are the primary differences between an index reorganization and an index rebuild? Ans: Reorganization is an “online” operation by default; a rebuild is an “offline” operation by default Reorganization...
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 128 pages = 1 MB. Page starts with the header of 96 bytes that is used to store page number,...
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 128 pages = 1 MB. Page starts with the header of 96 bytes that is used to store page number,...
Can you briefly describe about EXTENT? Ans: An extent is eight physically contiguous pages, or 64 KB means 16 Extents= 1 MB. There are two types of Extents. Uniform Extents and Mixed Extents. Uniform extents are owned by a single object; Mixed extents...
“Transactionlog” Logical Architecture: Each log record is identified by a unique number called LSN (Log Sequence Number). A log record contains the LSN, TransactionID to which it belongs and data modification record. Data modification record: It’s either...
What are the CheckPoints 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...
What is “Write Ahead Transaction Log”? Ans: SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. Data modifications are not made directly to disk,...
1. What is the Guest user account in SQL Server? What login is it mapped to it? Ans: The Guest user account is created by default in all databases and is used when explicit permissions are not granted to access an object. It is not mapped directly to...
Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?
Q. See I have an environment, Sunday night full backup, everyday night diff backup and every 45 min a transactional backup. Disaster happened at 2:30 PM on Saturday. You suddenly found that the last Sunday backup has been corrupted. What’s your recovery...
