1. What is index reorganization? 2. How sql engine knows which index has to be used while dealing with indexed views? 3. How to prevent bad parameter sniffing? What exactly it means? 4. What dll file that handle the transaction logs in logshipping? 5....
What database objects are stored in tempdb? Ans: There are three different types of objects stored in tempdb. Internal Objects: Intermediate runs for sort. Intermediate results for hash join and hash aggregates. XML variables or other large object (LOB)...
Performance Tuning – SQL Server Part – 1 Q. What are the bottlenecks that effects the performance of a Database / Application Ans: The top performance bottlenecks for OLTP applications are outlined as: Database Design \ Database Code Application Design...
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,...
