CTE(Common Table Expression) ?

Common Table Expression (CTE) is mainly used for following 2 features.

• Alternate to views, temporary tables
• Recursive queries. Especially this is very useful for data hierarchy queries where parent IDs and child IDs are in same table
• Disposable view. It can't work if u add some statement not related to CTE 

Reasons to use CTE

When should you use a CTE? Below are some good reasons when to use a CTE to resolve your TSQL statement requirements:

• If your query needs a recursive solution
• If your query has multiple subqueries, or you have repeated the same subquery code in a single statement
• If your query is large and complex

When your TSQL code is complex and/or contains one or more subqueries it become very hard to read. By breaking up your complex code into one of more CTEs you will make your code much easier to follow, maintain and debug.

Case 1: Simple CTE

With CTE_example
    AS
    (SELECT 100 Digit)
    SELECT * FROM CTE_example

We can also declare column names with CTE table declaration as shown below.

With CTE(Digit)
AS
(SELECT 100)
SELECT * FROM CTE

Output
Digit
-----------
100

Case 2: Declaring and using multiple CTEs

With CTE1(Digit_CTE1) 
AS
(SELECT 100 Digit)
 ,CTE2(Digit_CTE2)
AS
(SELECT 200 Digit)
SELECT * FROM CTE1 CROSS JOIN CTE2

Output

        Digit_CTE1    Digit_CTE2
        -------------------------
        100           200
    

Case 3: Recursive query using CTE

Without using recursive CTE query, it is not possible to display starts(*) in ascending order 5 times using one single query as shown below.

*
**
***
*****
*****

Recursive query to display starts(*) as shown above.

With CTE_Stars
AS
(select CONVERT(VARCHAR(10),'*') Stars
UNION ALL
SELECT CONVERT(VARCHAR(10),CTE_Stars.Stars+'*') Stars FROM CTE_Stars  WHERE  LEN(Stars)<6
)
SELECT * FROM CTE_Stars

Case 4: CTE Recursive query for data hierarchy (Parent Child hierarchy)

For this visit the link Recursive Data Hierarchy.

Limitations of CTE

1. Use select query of CTE in very first line immediately after CTE declaration.
2. We can use only one select query of CTE for one CTE declaration.
3. Sub queries and outer joins won't work within CTE declaration.
4. If there is a sequence of queries to be executed and if you want to use CTE query in between of them then the immediate query above CTE should end with semicolon;
as shown below.

                    select 'First query'
                    select 'Second query'
                    select 'Third Query';
                    With CTE(Digit)
                    AS
                    (SELECT 100)
                    SELECT * FROM CTE
            

If you remove ";" at the end of the query which is just above CTE then CTE query won't be executed and throws error.

Sample for real time CTE use

For example, create 2 tables as shown below to maintain student marks and college name.

CREATE TABLE Student_Results(name varchar(50),Subject varchar(40),Marks int)
insert into Student_Results values('Andrew','subject1',60)
insert into Student_Results values('Andrew','subject2',56)
insert into Student_Results values('Andrew','subject3',44)
insert into Student_Results values('Mark','subject1',74)
insert into Student_Results values('Mark','subject2',68)
insert into Student_Results values('Mark','subject3',98)
insert into Student_Results values('Steve','subject1',90)
insert into Student_Results values('Steve','subject2',86)
insert into Student_Results values('Steve','subject3',60)


CREATE TABLE Student_College(StudentName varchar(50),CollegeName varchar(40))
INSERT INTO Student_College values('Steve','Learners College')
INSERT INTO Student_College values('Andrew','Masters College')
INSERT INTO Student_College values('Mark','Arts College')

There are 3 students with their marks in three subjects. Another table has college name for each student. Below query using with CTE displays one row for each student with average marks and college name.

With CTE_Student(Name,AverageMarks)
AS(
SELECT name, avg(Marks) FROM Student_Results GROUP BY NAME)
SELECT CTE.Name,CTE.AverageMarks,C.CollegeName FROM CTE_Student CTE 
INNER JOIN Student_College C on CTE.name=C.StudentName

Output

Defining and Using CTE's

With the introduction of CTE's by Microsoft you now have a different way to build and document complex pieces of TSQL code. By using a CTE you can write and name a TSQL SELECT statement and then reference the named statement later on much like you would reference a table or a view.

Below is the syntax for defining a CTE:

WITH <expression_name> (Column1, Column2, …) AS (CTE Definition)

Where:

• "<expression_name>" is the name of the CTE
• "Column 1, Column2,…" are the names of the columns returned from the CTE (only required if column names are not provided, or are not unique)
• "CTE Definition" is a SELECT that defines the result set of the CTE.

A CTE definition needs to be followed by a single INSERT, UPDATE, DELETE, or SELECT statement that references the CTE. If the CTE is part of a batch then the statement prior to the WITH clause must end with a semi-colon. When you define multiple CTE's, a CTE that references another CTE needs to be define after the CTE it is referencing. This may sound confusing, but we'll see some examples shortly.

Below are some clauses that can't be used in a CTE:

• ORDER BY (unless used with TOP clause)
• INTO
• OPTION clause with query hints
• FOR XML
• FOR BROWSE

Summary

The CTE functionality was a great addition to SQL Server 2005. CTEs allow you to take your complex code and break it up into smaller more manageable pieces of code. CTEs also allow you to build recursive code. CTEs provides yet another method to implement complicate TSQL logic. Take the time to develop a few CTEs for your environment.

Question and Answer

In this section you can review how well you have understood using Common Table Expressions (CTEs).

Question 1:

When your CTE code is not the first statement in its batch, what must appear prior to the WITH clause of the CTE?

• a “GO” command
• a semicolon character
• a “END” keyword
• a “(“ character

Question 2:

If you define 4 CTEs to be used by a SELECT statement how many WITH clauses will be required to define the 4 different CTEs:

• 1
• 2
• 3
• 4

Question 3:

What is the maximum number of recursive CTE calls that can be made?

• 100
• 256
• 32, 787
• None of the above

Answers:

Question 1:

The correct answer is b. When a CTE is not the first statement in a batch, the statement prior to the WITH clause needs to be terminated with a semicolon.

Question 2:

The correct answers is a. The WITH clause is only specified 1 time when you have multiple CTEs that are used for a given query. The WITH clause precedes the first CTE definition. Each CTE definition that follows the first CTE definition needs to be preceded by a comma.

Question 3:

The correct answer is d. By default the MAXRECUSION default setting is 100. You can override this value by setting the MAXRECURSION option when you call your recursive CTE. When you use the MAXRECURSION option the limit is 32,787. If your CTE needs to be called recursively more than 32,787 times to complete, then you can set the MAXRECURSION to 0. Therefore there really is no maximum number of times a recursive CTE can be called.

Clustered and Nonclustered Indexes Explanation

Clustered and Nonclustered Indexes

index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

A table or view can contain the following types of indexes:

• Clustered
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
• Nonclustered
• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

  Non Index Key Value (Pointer)--------------------->> Data Row------------->Key Value

• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
• You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more information, see Create Indexes with Included Columns.

Both clustered and nonclustered indexes can be unique. This means no two rows can have the same value for the index key. Otherwise, the index is not unique and multiple rows can share the same key value. For more information, see Create Unique Indexes.

Indexes are automatically maintained for a table or view whenever the table data is modified.

What is the maximum number of Index per table? 

For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx

Limitations and Restrictions 

You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.

• The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. To obtain information about tables that might contain row-overflow data, use the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management function.

• If a clustered index is created on a heap with several existing nonclustered indexes, all the nonclustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Similarly, if a clustered index is dropped on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.
  The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes. Consider setting the ONLINE option to ON when you create indexes on existing tables. When set to ON, long-term table locks are not held. This enables queries or updates to the underlying table to continue. 
• When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. The old structure is not deallocated until the complete transaction commits. Additional temporary disk space for sorting may also be required.

How Indexes Are Used by the Query Optimizer

Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE, or MERGE statements. Consider the query SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250 in the AdventureWorks2012 database. When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist.

When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.

When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.

The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select. SQL Server provides the Database Engine Tuning Advisor to help with the analysis of your database environment and in the selection of appropriate indexes.

Ref:

https://msdn.microsoft.com/en-IN/library/ms190457.aspx

https://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

when you click above link see below this for more info:

Maximum Capacity Specifications for SQL Server 2005:

SQL Server 2005 Database Engine object	Maximum sizes/numbers SQL Server 2005 (32-bit)	Maximum sizes/numbers SQL Server 2005 (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8,000	8,000
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8,060	8,060
Bytes per varchar(max), varbinary(max),xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max)column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per base table	1,024	1,024
Columns per SELECT statement	4,096	4,096
Columns per INSERT statement	1,024	1,024
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,258 terabytes	524,258 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server 2005 editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances. SQL Server 2005 supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	249	249
Parameters per stored procedure	2,100	2,100
Parameters per user-defined function	2,100	2,100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1,000	1,000
Statistics on non-indexed columns	2,000	2,000
Tables per SELECT statement	256	256
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
UNIQUE indexes or constraints per table	249 nonclustered and 1 clustered	249 nonclustered and 1 clustered
User connections	32,767	32,767
XML indexes	249	249

What is Difference Between Locking, Blocking and Dead Locking in SQL Server?

What is Difference Between Locking, Blocking and Dead Locking in SQL Server?

Ref: http://www.connectsql.com/2011/03/sql-server-difference-between-locking.html

LOCKING occurs when connection needs access to a piece of data in database and it’s necessary for SQL Server when managing multiple connections. Just assume an example of your garage, when you park your car in garage, basically you are locking the place of garage.

BLOCKING occurs when two connections need access to same piece of data concurrently and one connection is blocked because at a particular time, only one connection can have access. Just like, you stop (block) your car on a traffic signal because some other car or cars are using the crossing area.

DEAD LOCK occurs when one connection is blocked and waiting for a second to complete his work, but on other side, second connection is also waiting for first connection to release the lock. Just like, you need to cross the signal area but same time someone else from opposite side also want to cross the signal. Now, you need a way which other is holding and other need way where your car is.

That is why, one should be clear that locking is integral part of SQL Server to handle concurrency, blocking is bad when one connection/transaction is waiting unnecessary for a long time, and deadlocking is a phenomenon which should never occur.

What is DeadLock in SQL Server? How to Minimize it?

What is DeadLock in SQL Server?

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

• Transaction A acquires a share lock on row 1.
• Transaction B acquires a share lock on row 2.
• Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
• Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

Using certain coding conventions in applications reduces the chance that applications will cause deadlocks. For more information, see Minimizing Deadlocks.

Deadlocking is often confused with normal blocking. When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. By default, SQL Server transactions do not time out, unless LOCK_TIMEOUT is set. The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

Deadlocks are sometimes called a deadly embrace.

Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. The waiting thread is said to have a dependency on the owning thread for that particular resource. In an instance of the Database Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. Similarly, transaction T2 has a dependency on transaction T1 for the Suppliertable lock resource. Because these dependencies form a cycle, there is a deadlock between transactions T1 and T2.

Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the Database Engine to lock table partitions at the HoBT level instead of at the TABLE level. However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.

To help minimize deadlocks:

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
  • Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
  • Use snapshot isolation.
• Use bound connections.

Access Objects in the Same Order

If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.

Avoid User Interaction in Transactions

Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

Keep Transactions Short and in One Batch

A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

Use a Lower Isolation Level

Determine whether a transaction can run at a lower isolation level. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

Use a Row Versioning-based Isolation Level

When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

Note
Some applications rely upon locking and blocking behavior of read committed isolation. For these applications, some change is required before this option can be enabled.

Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

Use Bound Connections

Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

Detecting and Ending Deadlocks

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

• Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
• Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).
• Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Resources That Can Deadlock

Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. The following types of resources can cause blocking that could result in a deadlock.

• Locks. Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.
• Worker threads. A queued task waiting for an available worker thread can cause deadlock. If the queued task owns resources that are blocking all worker threads, a deadlock will result. For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. This results in a deadlock.
• Memory. When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.
• Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. Also, when a parallel query starts execution, SQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.
• Multiple Active Result Sets (MARS) resources. These resources are used to control interleaving of multiple active requests under MARS (see Batch Execution Environment and MARS).
  • User resource. When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.
  • Session mutex. The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. Before the task can run, it must have exclusive access to the session mutex.
  • Transaction mutex. All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. Before the task can run, it must have exclusive access to the transaction mutex.
  In order for a task to run under MARS, it must acquire the session mutex. If the task is running under a transaction, it must then acquire the transaction mutex. This guarantees that only one task is active at one time in a given session and a given transaction. Once the required mutexes have been acquired, the task can execute. When the task finishes, or yields in the middle of the request, it will first release transaction mutex followed by the session mutex in reverse order of acquisition. However, deadlocks can occur with these resources. In the following code example, two tasks, user request U1 and user request U2, are running in the same session.
  U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");
  U2:    Rs2=Command2.Execute("select colA from sometable");
  

  The stored procedure executing from user request U1 has acquired the session mutex. If the stored procedure takes a long time to execute, it is assumed by the Database Engine that the stored procedure is waiting for input from the user. User request U2 is waiting for the session mutex while the user is waiting for the result set from U2, and U1 is waiting for a user resource. This is deadlock state logically illustrated as:

Deadlock Detection

All of the resources listed in the section above participate in the Database Engine deadlock detection scheme. Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process:

• The default interval is 5 seconds.
• If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
• If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds.
• If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.