This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is an Active Log?
The section of the log file from the MinLSN 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 MinLSN.
What is “Write Ahead Transaction Log”?
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, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database.
What is a Clustered table?
Clustered tables are tables that have a clustered index. The data rows are stored in order based on the clustered index key.
What is a Heap Table?
Heaps are tables that have no clustered index. The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages.
What is a Worktables?
The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries.
What is a COMPATIBILITY_LEVEL option of the Database?
This lets SQL Server know with which version of SQL Server to make the database compatible.
What is AUTO_SHRINK option of the Database?
This option either turns on or off the feature that will automatically shrink your database files when free space is available. In almost all cases, this should be set to OFF.
What is AUTO_UPDATE_STATISTICS option of the Database?
When turned ON, as it should be in most cases, the optimizer will automatically keep statistics updated, in response to data modications.
What is READ_WRITE option of the Database?
This is the default option and the one to use if you want users to be able to update the database. We could also set the database to READ_ONLY to prevent any users making updates to the database.
What is Lazy writer?
Lazy writer can adjust the number of buffers in the buffer pool if dynamic memory management is enabled. The lazy writer is a system process that keeps free buffers available by removing infrequently used pages from the buffer cache.
Ensure that a specified number of buffers are free in the Buffer Pool so they can be allocated for use by the server.
How you explicitly recompile your execution plan?
- You can use sp_recompile to explicitly recompile your execution plan.
- You can also use WITH RECOMPILE to recompile the stored procedure.
What is buffer pool?
SQL Server builds a buffer pool in memory to hold pages read from the database. You can minimize physical I/O to the database files by maximizing the size of the buffer pool.
What is a query wait option?
Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
You can change query wait value using sp_configure.
How SQL Server executes a single SELECT statement?
- The parser scans the SELECT statement and breaks it into logical units such as keywords, expressions, operators, and identifiers.
- A query tree, sometimes referred to as a sequence tree, is built describing the logical steps needed to transform the source data into the format required by the result set.
- The query optimizer analyzes different ways the source tables can be accessed and create an optimized version of the query tree is called the execution plan.
- The relational engine starts executing the execution plan.
- The relational engine processes the data and returns the result set to the client.
What is Query Plan?
The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory.
What is Execution Context?
Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context.
How SQL Server uses procedure cache?
When any SQL statement is executed, it first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists.
It reuses any existing plan it finds, saving the overhead of recompiling the SQL statement
