This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Which two isolation levels support optimistic/row-versioned-based concurrency control?
First is READ COMMITTED and Second is SNAPSHOT Isolation level.
What is the size of a lock structure?
The size of lock structure is 96 bytes.
Which isolation level is using key-range lock?
The SERIALIZABLE isolation level is using key-range lock.
Can deadlocks occur on resources other than a database object and if it YES, what are that other resources?
YES, deadlock can occur on other resources like: worker threads, Memory, Parallel query execution related resources, Multiple Active Result Sets resources.
When to use Lock Pages in Memory with SQL Server?
The Lock Pages in Memory user right is a good performance optimization practice when Tier 1 mission-critical SQL Server. When setting the SQL Server Lock Pages in Memory user right, the virtual machine’s memory reservation should also be set to match the amount of the provision memory.
What is the use of DEADLOCK_PRIORITY option?
SET DEADLOCK_PRIORITY is set at execute or run time and not at parse time.
If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.
What is the use of LOCK_TIMEOUT option?
It is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error.
A value of -1 (default) indicates no time-out period and value of 0 means to not wait at all and return a message as soon as a lock is encountered.
How to check the value of LOCK_TIMEOUT?
|
1 2 |
SELECT @@LOCK_TIMEOUT GO |
How you can set maximum number of available locks to minimize the memory usage?
You can use sp_configure to set a locks value.
Below is a T-SQL Script to set the lock value up to 20000.
Which steps should be taken to address common deadlock issues?
- Understand which tables are involved in the deadlocks.
- Understand which pieces of code correspond to the deadlock and which users execute those pieces of code.
- Understand how the application is affected when a deadlock occurs.
- Determine the length of the transaction.
- Determine if the tables are highly fragmented and causing unnecessary page reads to complete the transaction.
- Determine if the tables can be redesigned to prevent deadlocking.
- Determine if additional or less indexes would improve the deadlocking.
- Determine if the code accesses the tables in the same order and with a similar query plan.
Can we capture deadlock information in the XML File format?
SQL Server Profiler has the ability to capture the deadlock related information as XML files which can be analyzed to determine the overall locking and blocking issue.
What are the features of XML Deadlock File?
- The XML Deadlocks feature gives you the ability to capture the deadlock related information and review the deadlocks independent of the Profiler interface.
- Each of the deadlock files can be analyzed in order to determine trends with the deadlocks on your SQL Server.
- Each deadlock situation can be stored in a separate file for per deadlock analysis.
- The XML format gives you the ability to programmatically review the deadlocks.
What is the Multiversion Concurrency Control (MVCC)?
What is the Implicit Locking?
Implicit Lock means Lock is implied but is not acquired.
The Database will implicitly obtain the appropriate locks for your application at the point at which they are needed. An operation that reads an object will obtain a read lock; an operation that modifies an object will obtain a write lock.
What is the Explicit Locking?
Explicit Lock means Lock is explicitly requested for a record or table.
An application needing to reserve access to all required objects in advance can explicitly lock objects.
Explicit locking effectively freezes the objects, because no other session can modify them as long as they are locked.
I am confused between UPDLOCK and HOLDLOCK, would you please help me?
UPDLOCK does not block concurrent SELECT nor INSERT, but blocks any UPDATE or DELETE of the rows selected by T1.
HOLDLOCK means SERALIZABLE and therefore allows SELECTS, but blocks UPDATE and DELETES of the rows selected by T1, as well as any INSERT in the range selected by T1.
What is the meaning of wait and await in the Deadlock situation?
wait will synchronously block until the task completes. So the current thread is literally blocked waiting for the task to complete. As a general rule, you should use “async all the way down”; that is, don’t block on async code.
await will asynchronously wait until the task completes. This means the current method is “paused” (its state is captured) and the method returns an incomplete task to its caller. Later, when the await expression completes, the remainder of the method is scheduled as a continuation.
What is a Transaction in SQL Server ?
Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure.
What are the types of Transactions?
Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit. No need to specify Explicitly.
Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.
