Under extreme conditions such as a complete lack
of available resources, it is possible for SQL Server to enter an abnormal
state in which no further connections can be made to the SQL Server instance.
Prior to SQL Server 2005, this situation meant that an administrator could not
get in to kill any troublesome connections or even begin to diagnose the
possible cause of the problem.
SQL Server 2005 introduced a special connection called the DAC that was designed to be accessible even when no other access can be made.
The user logon to connect via the DAC must be a member of the sysadmin server role.
There are only a few restrictions on the SQL statements that can be executed on the DAC. (For example, you cannot run BACKUP or RESTORE using the DAC.) However, it is recommended that you do not run any resource-intensive queries that might exacerbate the problem that led you to use the DAC. The DAC connection is created primarily for troubleshooting and diagnostic purposes.
A special thread is assigned to the DAC that allows it to execute the diagnostic functions or queries on a separate scheduler. This thread cannot be terminated. You can kill only the DAC session, if needed. The DAC scheduler always uses the scheduler_id value of 255, and this thread has the highest priority. There is no lazywriter thread for the DAC, but the DAC does have its own IOCP, a worker thread, and an idle thread.
You might not always be able to accomplish your intended tasks using the DAC. Suppose you have an idle connection that is holding on to a lock. If the connection has no active task, there is no thread associated with it, only a connection ID. Suppose further that many other processes are trying to get access to the locked resource, and that they are blocked. Those connections still have an incomplete task, so they do not release their worker. If 255 such processes (the default number of worker threads) try to get the same lock, all available workers might get used up and no more connections can be made to SQL Server. Because the DAC has its own scheduler, you can start it, and the expected solution would be to kill the connection that is holding the lock but not do any further processing to release the lock. But if you try to use the DAC to kill the process holding the lock, the attempt fails. SQL Server would need to give a worker to the task to kill it, and no workers are available. The only solution is to kill several of the (blameless) blocked processes that still have workers associated with them.
The DAC is not guaranteed to always be usable, but because it reserves memory and a private scheduler and is implemented as a separate node, a connection probably is possible when you cannot connect in any other way.
http://www.mybasicknowledge.com/2012/09/sql-server-administration-optimizations.html
SQL Server 2005 introduced a special connection called the DAC that was designed to be accessible even when no other access can be made.
Access via the DAC must be
specially requested. You can connect to the DAC using the command-line tool
SQLCMD, and specifying the -A (or /A) flag. This method of connection is recommended because it uses
fewer resources than the graphical user interface (GUI).
Through Management Studio, you can specify that
you want to connect using DAC by preceding the name of your SQL Server with ADMIN: in the Connection dialog box.
For
example, to connect to the default SQL Server instance on machine, MYMachine,
we would enter ADMIN:MYMachine. To connect to a named instance called SQL2008
on the same machine, we would enter ADMIN:MYMachine\SQL2008.
The DAC is a special-purpose connection designed
for diagnosing problems in SQL Server and possibly resolving them. It is not
meant to be used as a regular user connection. Any attempt to connect using the
DAC when there is already an active DAC connection results in an error. The
message returned to the client says only that the connection was rejected; it
does not state explicitly that it was because there already was an active DAC.
However, a message is written to the error log indicating the attempt (and
failure) to get a second DAC connection. You can check whether a DAC is in use
by running the following query. If there is an active DAC, the query will
return the SPID for the DAC; otherwise, it will return no rows.
SELECT s.session_id FROM sys.tcp_endpoints as e JOIN
sys.dm_exec_sessions as s ON e.endpoint_id = s.endpoint_id WHERE
e.name='Dedicated Admin Connection';
You should keep the following points in mind
about using the DAC:
By default, the DAC is available only locally.
However, an administrator can confi gure
SQL Server to allow remote connection by using the configuration option called Remote Admin Connections.
The user logon to connect via the DAC must be a member of the sysadmin server role.
There are only a few restrictions on the SQL statements that can be executed on the DAC. (For example, you cannot run BACKUP or RESTORE using the DAC.) However, it is recommended that you do not run any resource-intensive queries that might exacerbate the problem that led you to use the DAC. The DAC connection is created primarily for troubleshooting and diagnostic purposes.
A special thread is assigned to the DAC that allows it to execute the diagnostic functions or queries on a separate scheduler. This thread cannot be terminated. You can kill only the DAC session, if needed. The DAC scheduler always uses the scheduler_id value of 255, and this thread has the highest priority. There is no lazywriter thread for the DAC, but the DAC does have its own IOCP, a worker thread, and an idle thread.
You might not always be able to accomplish your intended tasks using the DAC. Suppose you have an idle connection that is holding on to a lock. If the connection has no active task, there is no thread associated with it, only a connection ID. Suppose further that many other processes are trying to get access to the locked resource, and that they are blocked. Those connections still have an incomplete task, so they do not release their worker. If 255 such processes (the default number of worker threads) try to get the same lock, all available workers might get used up and no more connections can be made to SQL Server. Because the DAC has its own scheduler, you can start it, and the expected solution would be to kill the connection that is holding the lock but not do any further processing to release the lock. But if you try to use the DAC to kill the process holding the lock, the attempt fails. SQL Server would need to give a worker to the task to kill it, and no workers are available. The only solution is to kill several of the (blameless) blocked processes that still have workers associated with them.
The DAC is not guaranteed to always be usable, but because it reserves memory and a private scheduler and is implemented as a separate node, a connection probably is possible when you cannot connect in any other way.
http://www.mybasicknowledge.com/2012/09/sql-server-administration-optimizations.html
