1. What is blocking ? How to identify and resolve the blocking ?
Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait , block on the first.
Determining blocking sessions :
We can determine blockings using Activity Monitor , sp_who2 , sp_lock,sys.sysprocesses,sys.dm_exec_requests,sys.dm_os_waiting_tasks.
Resolving blocking session :
Right click on session and kill in Activity Monitor
Kill Session_ID
SQL Server DBA Interview question and answers Part 5
2. What is Deadlock ?
A deadlock occurs when users try to place exclusive locks on each other's object.
Ex:
User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on Table2 and user tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.
The database engine picks one of the victim (users) and kills their query and send a error message to users "You are the victim of a deadlock and try again later"
Deadlock Information Tools :
1. TraceFlags :
DBCC TRACEON(1204) & DBCC TRACEON(1222). When these trace flags are enabled the dead lock information is captured by the SQL Server errorlog.
2. Deadlock graph event in SQL Profiler :
SQL Server profiler graphically representation of tasks and resources involved in a deadlock.
3. System View :
We can find the blocking sessions by writing the following query.
Select session_id,status,blocking_session_id from sys.dm_exec_requests where blocking_session_id>0
Resolving Deadlock :
After finding the session causing the problem we can use Kill process_id command.
3. How to find the locks on a resource?
We can use sys.dm_tran_locks system view or sp_lcok
Ex: Select resource_type,resource_mode,request_status,request_session_id from sys.dm_tran_locks.
4. What is Lock escalation ?
Lock escalation is the process of converting a lot of low level locks like ro locks,page locks into higher level locks like table locks.
5. What is virtual memory ? How to assign virtual memory ? How much space required for virtual memory ?
A reserved disk space to maintain transactions whenever memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.
To set Virtual Memory : Right click on System --> Select system properties --> Advanced --> Virtual Memory --> Change --> Select directory --> Provide Min and Max value.
6. What is check point and when it occurs ?
A check point is a SQL Server operation that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.
Check point occurs :
When SQL Server shutdown.
When Alter database command is used to add or remove data files.
When recovery model change from Full / Bulk-Logged to Simple.
Before a database backup is perfromed.
In Simple recovery model log is truncated after checkpoints occurs.
Manual CHECKPOINT command.
Ex: CHECKPOINT 60 (60 is duration in seconds)
Checkpoint runs periodically on Full / Bulk Logged recovery model databases as specified by the recovery interval setting.
7. What is the purpose of LSN Numbers?
Every record in the transaction log is uniquely identified by a log sequence number.LSNs are used internally during a restore sequence to track the point in time to which data has been restored.
8. What is Fill Factor ? How to assign FillFactor ?
A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.
Assign Fill Factor : Right click on Server --> Properties --> Database Settings --> Default Index Fill Facto --> Provide the Value.
9. Backup Strategy ?
Generally backups are done by client requirement. But most of the companies will follow the strategy like
Every Sunday night Full Backup
Every Day night Differential Backup
Every 15/30/45 Minutes Transactional Log Backup.
10. Restore Strategy ?
First restore recent last full backup WITH NORECOVERY.
Then restore last Diff.backup WITH NORECOVERY.
Then restore all transaction log backups since recent Full / Diff backups WITH RECOVERY.
11. What is Point-in-time restore and use of that ?
Point-in-time restore is used to restore a database to a particular time just before a failure has occurred or before corrupt data.
To Restore a database to a point-in-time :
STOPAT option is used to restore a database to specific time.
12. What is End Point ?
An endpoint is a network protocol which is used to communicate Principal,Mirror & Witness Servers over the network.
13. What is Hardening ?
As quick as possible the log buffer is written to the transaction log on disk . This process is called as hardening.
14. What is Log Buffer ?
A Log buffer is a special location in memory (RAM) . SQL Server stores the changes in the databases log buffer.
15. What are the agents used for Transactional Replication ?
SLD i.e Snapshot Agent , Log Reader Agent & Distributor Agent.
16. What are the Agents used in Merge Replication ?
SM i.e Snapshot Agent & Merger Agent
17. What is Database Mail ?
Database Mail is designed for reliability , scalability , security and supportability. DB Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server.
Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait , block on the first.
Determining blocking sessions :
We can determine blockings using Activity Monitor , sp_who2 , sp_lock,sys.sysprocesses,sys.dm_exec_requests,sys.dm_os_waiting_tasks.
Resolving blocking session :
Right click on session and kill in Activity Monitor
Kill Session_ID
SQL Server DBA Interview question and answers Part 5
2. What is Deadlock ?
A deadlock occurs when users try to place exclusive locks on each other's object.
Ex:
User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on Table2 and user tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.
The database engine picks one of the victim (users) and kills their query and send a error message to users "You are the victim of a deadlock and try again later"
Deadlock Information Tools :
1. TraceFlags :
DBCC TRACEON(1204) & DBCC TRACEON(1222). When these trace flags are enabled the dead lock information is captured by the SQL Server errorlog.
2. Deadlock graph event in SQL Profiler :
SQL Server profiler graphically representation of tasks and resources involved in a deadlock.
3. System View :
We can find the blocking sessions by writing the following query.
Select session_id,status,blocking_session_id from sys.dm_exec_requests where blocking_session_id>0
Resolving Deadlock :
After finding the session causing the problem we can use Kill process_id command.
3. How to find the locks on a resource?
We can use sys.dm_tran_locks system view or sp_lcok
Ex: Select resource_type,resource_mode,request_status,request_session_id from sys.dm_tran_locks.
4. What is Lock escalation ?
Lock escalation is the process of converting a lot of low level locks like ro locks,page locks into higher level locks like table locks.
5. What is virtual memory ? How to assign virtual memory ? How much space required for virtual memory ?
A reserved disk space to maintain transactions whenever memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.
To set Virtual Memory : Right click on System --> Select system properties --> Advanced --> Virtual Memory --> Change --> Select directory --> Provide Min and Max value.
6. What is check point and when it occurs ?
A check point is a SQL Server operation that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.
Check point occurs :
When SQL Server shutdown.
When Alter database command is used to add or remove data files.
When recovery model change from Full / Bulk-Logged to Simple.
Before a database backup is perfromed.
In Simple recovery model log is truncated after checkpoints occurs.
Manual CHECKPOINT command.
Ex: CHECKPOINT 60 (60 is duration in seconds)
Checkpoint runs periodically on Full / Bulk Logged recovery model databases as specified by the recovery interval setting.
7. What is the purpose of LSN Numbers?
Every record in the transaction log is uniquely identified by a log sequence number.LSNs are used internally during a restore sequence to track the point in time to which data has been restored.
8. What is Fill Factor ? How to assign FillFactor ?
A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.
Assign Fill Factor : Right click on Server --> Properties --> Database Settings --> Default Index Fill Facto --> Provide the Value.
9. Backup Strategy ?
Generally backups are done by client requirement. But most of the companies will follow the strategy like
Every Sunday night Full Backup
Every Day night Differential Backup
Every 15/30/45 Minutes Transactional Log Backup.
10. Restore Strategy ?
First restore recent last full backup WITH NORECOVERY.
Then restore last Diff.backup WITH NORECOVERY.
Then restore all transaction log backups since recent Full / Diff backups WITH RECOVERY.
11. What is Point-in-time restore and use of that ?
Point-in-time restore is used to restore a database to a particular time just before a failure has occurred or before corrupt data.
To Restore a database to a point-in-time :
STOPAT option is used to restore a database to specific time.
12. What is End Point ?
An endpoint is a network protocol which is used to communicate Principal,Mirror & Witness Servers over the network.
13. What is Hardening ?
As quick as possible the log buffer is written to the transaction log on disk . This process is called as hardening.
14. What is Log Buffer ?
A Log buffer is a special location in memory (RAM) . SQL Server stores the changes in the databases log buffer.
15. What are the agents used for Transactional Replication ?
SLD i.e Snapshot Agent , Log Reader Agent & Distributor Agent.
16. What are the Agents used in Merge Replication ?
SM i.e Snapshot Agent & Merger Agent
17. What is Database Mail ?
Database Mail is designed for reliability , scalability , security and supportability. DB Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server.
No comments:
Post a Comment