SQL Server Database Administrator Questions & Answers
Q. In SQL Server, What is Checkpoint?
A. Checkpoint flushes\writes all dirty data pages from the Buffer Cache to disk and also its writes log record from log buffer to physical log file. This minimizes the active portion of the log that must be processed during a full recovery of a database. At the time of full recovery, the following types of actions are performed:
a) The log records of modifications not flushed to disk before the system stopped are rolled forward.
b) All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.
In SQL Server, we have following types of checkpoints :
1.Automatic: Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.
EXEC sp_configure 'recovery interval', 'seconds'
2. Indirect: Issued in the background to meet a user-specified target recovery time for a given database. Beginning with SQL Server 2016 Community Technology Preview 2 (CTP2) the default value for new databases is 1 minute, which indicates database will use indirect checkpoints. For older versions the default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. Microsoft recommends 1 minute for most systems.
Syntax: ALTER DATABASE … SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUT ES }
3.Manual: Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.
Syntax: CHECKPOINT [ checkpoint_duration ]
4. Internal: Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.
Whenever database backup has been triggered, the DB Engine automatically calls a checkpoint so that all changes to the database pages are contained in the backup. Also, stopping a SQL server to issues a checkpoint on each database on the server.
Also it occurs at SQL Server services Started or Stopped whether it is manually, in case of cluster failover or auto restart due to unknown issue.
For more details click here
Q.What is write-ahead log (WAL) in SQL Server?
A. 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. A page modified in the cache, but not yet written to disk, is called a dirty page. The internal process that actually goes on:
1.Copy of the data pages are pulled and placed in buffer cache.
2.Applied the operation on the pages that are on buffer cache.
3.Write the log record details (Pages modified) to Disk.
4.Write / flush /apply the page to the disk.
If step 4 happens before the step 3 then rollback is impossible. SQL Server takes the responsibility of writing the log details to disk before flushing the dirty pages.
For more details click here
Q. What are the useful log files in SQL Server and how to access them?
A. In SQL Server, following types of log files:
a) SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs.
b) SQL Server Agent Log: SQL Server’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT.OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.
c) Windows Event Log: An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server Integration Services (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.
d) SQL Server Setup Log: You might already be familiar with the SQL Server Setup log, which is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.
e) SQL Server Profiler Log: SQL Server Profiler, the primary application-tracing tool in SQL Server, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory.
For more details click here
Q.How can we read SQL Server Errorlog using stored procedure?
A. This can be achieved using undocumented stored procedure EXEC master.dbo.xp_readerrorlog or sp_readerrorlog.
For more details click here
Q. What is trace flag in SQL Server?
A. Trace Flag: Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.
In SQL Server, there are two types of trace flags: Session and Global.
1. Session trace flags are active for a connection and are visible only to that connection.
2. Global trace flags are set at the server level and are visible to every connection on the server.
Some flags can only be enabled as global, and some can be enabled at either global or session scope.
The following rules apply:
a) A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option.
b) If a trace flag has either global or session scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
Trace flags are set on or off by using either of the following methods:
Using the DBCC TRACEON and DBCC TRACEOFF commands.
For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1). To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
Using the -T startup option to specify that the trace flag be set on during startup.
The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option. For more information about startup options, see Database Engine Service Startup Options.
Use the DBCC TRACESTATUS command to determine which trace flags are currently active.
For more details click here
Q.What are the common useful trace flags in SQL Server?
A. Here are the few useful trace flags: Deadlock Information: 1204, 1205, 1222
Network Database files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 – (SQL 2005 and 2008) To reduces TempDB contention.
Command to check the trace status:
DBCC TRACESTATUS(); GO
For more details click here
Q. Why extents are used to group eight 8K pages?
A: Extents are used because it is more resource efficient for SQL Server to allocate eight, 8K pages in a single step than it is for SQL Server to create eight, 8K pages in eight separate steps. There are two types of extents: uniform and mixed extents. All eight pages of a uniform extent are all owned by the same object. In a mixed extent, each page of the extent can be owned by a different object.
Q. Why are there two types of extents? Can't it work having only one type of extents?
A: This is because many objects in a database are less than 64K in size. If only uniform extends were allowed, then there would be a lot of wasted space on extents that are not fully used by an object. By allowing mixed extends, multiple, smaller objects can all fit onto a single extent, which makes more efficient use of available space. Whenever a new table or index is created, it generally is first created on a mixed extent.
Q. In SQL Server, What is Checkpoint?
A. Checkpoint flushes\writes all dirty data pages from the Buffer Cache to disk and also its writes log record from log buffer to physical log file. This minimizes the active portion of the log that must be processed during a full recovery of a database. At the time of full recovery, the following types of actions are performed:
a) The log records of modifications not flushed to disk before the system stopped are rolled forward.
b) All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.
In SQL Server, we have following types of checkpoints :
1.Automatic: Issued automatically in the background to meet the upper time limit suggested by the recovery interval server configuration option. Automatic checkpoints are throttled based on the number of outstanding writes and whether the Database Engine detects an increase in write latency above 20 milliseconds.
EXEC sp_configure 'recovery interval', 'seconds'
2. Indirect: Issued in the background to meet a user-specified target recovery time for a given database. Beginning with SQL Server 2016 Community Technology Preview 2 (CTP2) the default value for new databases is 1 minute, which indicates database will use indirect checkpoints. For older versions the default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance. Microsoft recommends 1 minute for most systems.
Syntax: ALTER DATABASE … SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUT ES }
3.Manual: Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints. Optionally, the checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete.
Syntax: CHECKPOINT [ checkpoint_duration ]
4. Internal: Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.
Whenever database backup has been triggered, the DB Engine automatically calls a checkpoint so that all changes to the database pages are contained in the backup. Also, stopping a SQL server to issues a checkpoint on each database on the server.
Also it occurs at SQL Server services Started or Stopped whether it is manually, in case of cluster failover or auto restart due to unknown issue.
For more details click here
Q.What is write-ahead log (WAL) in SQL Server?
A. 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. A page modified in the cache, but not yet written to disk, is called a dirty page. The internal process that actually goes on:
1.Copy of the data pages are pulled and placed in buffer cache.
2.Applied the operation on the pages that are on buffer cache.
3.Write the log record details (Pages modified) to Disk.
4.Write / flush /apply the page to the disk.
If step 4 happens before the step 3 then rollback is impossible. SQL Server takes the responsibility of writing the log details to disk before flushing the dirty pages.
For more details click here
Q. What are the useful log files in SQL Server and how to access them?
A. In SQL Server, following types of log files:
a) SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs.
b) SQL Server Agent Log: SQL Server’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT.OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.
c) Windows Event Log: An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server Integration Services (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.
d) SQL Server Setup Log: You might already be familiar with the SQL Server Setup log, which is located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.
e) SQL Server Profiler Log: SQL Server Profiler, the primary application-tracing tool in SQL Server, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\LOG directory.
For more details click here
Q.How can we read SQL Server Errorlog using stored procedure?
A. This can be achieved using undocumented stored procedure EXEC master.dbo.xp_readerrorlog or sp_readerrorlog.
For more details click here
Q. What is trace flag in SQL Server?
A. Trace Flag: Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. For example, if trace flag 3205 is set when an instance of SQL Server starts, hardware compression for tape drivers is disabled. Trace flags are frequently used to diagnose performance issues or to debug stored procedures or complex computer systems.
In SQL Server, there are two types of trace flags: Session and Global.
1. Session trace flags are active for a connection and are visible only to that connection.
2. Global trace flags are set at the server level and are visible to every connection on the server.
Some flags can only be enabled as global, and some can be enabled at either global or session scope.
The following rules apply:
a) A global trace flag must be enabled globally. Otherwise, the trace flag has no effect. We recommend that you enable global trace flags at startup, by using the -T command line option.
b) If a trace flag has either global or session scope, it can be enabled with the appropriate scope. A trace flag that is enabled at the session level never affects another session, and the effect of the trace flag is lost when the SPID that opened the session logs out.
Trace flags are set on or off by using either of the following methods:
Using the DBCC TRACEON and DBCC TRACEOFF commands.
For example, DBCC TRACEON 2528: To enable the trace flag globally, use DBCC TRACEON with the -1 argument: DBCC TRACEON (2528, -1). To turn off a global trace flag, use DBCC TRACEOFF with the -1 argument.
Using the -T startup option to specify that the trace flag be set on during startup.
The -T startup option enables a trace flag globally. You cannot enable a session-level trace flag by using a startup option. For more information about startup options, see Database Engine Service Startup Options.
Use the DBCC TRACESTATUS command to determine which trace flags are currently active.
For more details click here
Q.What are the common useful trace flags in SQL Server?
A. Here are the few useful trace flags: Deadlock Information: 1204, 1205, 1222
Network Database files: 1807
Log Record for Connections: 4013
Skip Startup Stored Procedures: 4022
Disable Locking Hints: 8755
Forces uniform extent allocations instead of mixed page allocations 1118 – (SQL 2005 and 2008) To reduces TempDB contention.
Command to check the trace status:
DBCC TRACESTATUS(); GO
For more details click here
Q. Why extents are used to group eight 8K pages?
A: Extents are used because it is more resource efficient for SQL Server to allocate eight, 8K pages in a single step than it is for SQL Server to create eight, 8K pages in eight separate steps. There are two types of extents: uniform and mixed extents. All eight pages of a uniform extent are all owned by the same object. In a mixed extent, each page of the extent can be owned by a different object.
Q. Why are there two types of extents? Can't it work having only one type of extents?
A: This is because many objects in a database are less than 64K in size. If only uniform extends were allowed, then there would be a lot of wasted space on extents that are not fully used by an object. By allowing mixed extends, multiple, smaller objects can all fit onto a single extent, which makes more efficient use of available space. Whenever a new table or index is created, it generally is first created on a mixed extent.
thank you for sharing SQL Server DBA Online Training Bangalore
ReplyDelete