Sharing of thoughts and information is what blogging is all about. This way we can learn from each other. Post A Comment!These postings are provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use.
Anthony Bartolo Twitter | LinkedIn
Pierre Roman Twitter | LinkedIn
There are times when SQL Server may be so busy processing requests that it can no longer allocate memory or processor resources to even allow an SA to connect. This has been an issue with older versions of SQL Server.
SQL Server solves this problem by introducing a feature called "Dedicated Administrator Connection” (DAC).
DAC uses a specific TCP endpoint in a SQL Server instance that is always attached to a dedicated UMS (User Mode Scheduler). DAC provides a connection that can be used by a member of the sysadmin role to access a SQL Server instance, thereby guaranteeing that an administrator could not be locked out of SQL Server due to resource allocation issues.
DAC connection is similar to a regular SQL Server connection except that it has only one worker thread to serve requests. It cannot run any queries that require parallel work/multiple threads such as BACKUP or parallel query plan. It will use the regular system memory and in case of failure, it will use its reserved memory. Only a single connection of DAC is allowed at a time and if a connection is already being used, any subsequent connections are refused.
Due to the limitation in available resources (One thread and limited amount of memory), you must be very careful of the types of queries you run. You MUST use DAC connection exclusively for diagnostic purposes to resolve a condition that cannot normally be resolved through a regular connection.
You can create a connection to DAC in two ways:
1) Using SQLCMD from the Command Prompt
2) Using SQL Server Management Studio
*You must use -A parameter when using SQLCMD to use Dedicated Administrator Connection.
THINGS TO KEEP IN MIND:
1. DO NOT USE DAC FOR EVERYDAY WORK. IT IS EXCLUSIVELY RESERVED FOR EMERGENCY PURPOSES ONLY.
2. ONLY ONE DAC CONNECTION IS ALLOWED PER INSTANCE. IF SOMEONE ELSE IS CONNECTED TO SQL SERVER USING DAC CONNECTION OR HAS LEFT THE CONNECTION OPEN, YOU WILL NOT BE ABLE TO CONNECT TO SQL SERVER USING DAC UNLESS THE OTHER CONNECTION IS CLOSED.
3. BY DEFAULT, DAC CONNECTION IS ALLOWED ONLY ON A LOCAL SERVER CONNECTION. YOU MUST ENABLE IT FOR REMOTE CONNECTION IF YOU WISH TO USE IT REMOTELY. (BY DEFAULT IT IS NOT ON FOR A CLUSTERED INSTANCE)
CONNECTING TO DAC FROM COMMAND PROMPT
C:\> SQLCMD –S SERVERNAME –E –A
* The administrator can now execute queries to diagnose the problem and possibly terminate the unresponsive sessions.
RECOMMENDED QUERIES AND COMMANDS FOR DAC
1. KILL COMMAND
2. DBCC TRACEON/TRACEOFF
3. DMV/BASIC SINGLE TABLE QUERIES
4. SET OPTIONS
5. SP_CONFIGURE/RECONFIGURE (OPTIONS THAT DO NOT REQUIRE RESTARTING SQL SERVER)
AVOID QUERIES AND COMMANDS FOR DAC
2. DBCC CHECK COMMANDS
3. AVOID ANY COMMANDS THAT REQUIRE MULTIPLE THREADS
4. MULTIPLE TABLE JOINS
5. AVOID STATEMENTS THAT MAY GENERATE LOTS OF IO
6. AVOID ANY DDL STATEMENT