DAC - Dedicated Administrator Connection

Why It's needed ?

SQL Server grands a special connection when normal/standard connection are not possible. So, The DAC helps DBAs to query and troubleshoot problems even normal/standard connection also not responding.

Note:DAC also may not possible under extreme situation! 

SQL Browser service should be running for Named Instance! But, Its not need for "Default Instance"

By default, DAC is allowed within the server. Only sysadmnin members are allowed to connect using DAC.

Connecting SQL Server from remote machine is not allowed unless they are configured "remote admin connections" using the sp_configure stored procedure

DAC only listens on the loop-back IP address (127.0.0.1), port 1434

If TCP port 1434 is not available, a TCP port is dynamically assigned when the Database Engine starts up

For DAC connection using sqlcmd

sqlcmd -SServer\Instance -A
Or
sqlcmd -S127.0.0.1 -A -- Default Instance
Or
sqlcmd -S127.0.0.1\Instance -A -- Named Instance
Or
sqlcmd -SAdmin:Server\Instance

DCA is declined with an Error 17810 If its already a DAC is running

Note: SQL Server Express does not listen on the DAC port unless started with a trace flag 7806

The DAC initially connects to the default database associated with the login. If the default database is offline or otherwise not available, the connection will return error 4060

"Microsoft recommends to connect the master database with the DAC because master is guaranteed to be available"

SQL Server prohibits running parallel queries or commands with the DAC.

The DAC session might get blocked on a latch. You might be able to terminate the DAC session using CTRL-C but it is not guaranteed. In that case, your only option may be to restart SQL Server

On cluster configurations, the DAC will be off by default

When connecting to the default instance, the DAC avoids using a SQL Server Resolution Protocol (SSRP) request to the SQL Server Browser Service when connecting

When connecting to the Azure SQL Database with the DAC, you must also specify the database name in the connection string by using the -d option.

Have you faced ?

The following Err occurred when I try to connect with SQL Server through Management studio!
Yeah!

"There was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)"

It seems like the Maximum connection has been limited. Oh...

Then, How to fix/increase the Maximum connections ? 

here we go with DAC!

Ok. I need to connect with Named Instance

Check, What is the current MAX connections ?

Increase/Reset the MAX connections!

Reconfiguring the running value of the User Connections option requires restarting the Database Engine!

Yeah! It worked!!!

optimize for ad hoc workloads - Configuration Option

Normally, SQL Server compile, generate and stores the Plan for reuse. The process continues for all the following object types  

• Proc
• Prepared
• Adhoc
• ReplProc
• Trigger
• View
• Default
• UsrTab
• SysTab
• Check
• Rule

For the every type of queries the process generates and stores the plan and every plan consume some Size.

Here, we going to see about the Transact-SQL statement also referred to as "Adhoc" query.

USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

The plan consumes 72 KB (73728 Bytes) and the query uses 1st time. But, not sure whether the same plan going to be re-used next time. So, do we need to store the entire plan with consumes 72 KB at first time itself ?

No.

So, We have an option "optimize for ad hoc workloads" is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc queries/batches.

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE WITH OVERRIDE

Done. 

As long as the above option is ON. All the Adhoc queries' entire plan are not going to be stored in Plan cache. But, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time.

To Remove the specific plan from the cache
DBCC FREEPROCCACHE(0x06000A004230791470B5CC340100000001000000000000000000000000000000000000000000000000000000)
GO

Run the query at first time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

The following query to get the Stored Plan related Info for the above query
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Yes. Now, we got only 352 Bytes (small compiled plan stub) for the plan at first compile/use.

When run the same query again second time

Run the query at second time
USE AdventureWorks2012 
GO
SELECT C.AccountNumber, SH.OrderDate, SH.DueDate, SH.TotalDue FROM Sales.SalesOrderHeader SH WITH(NOLOCK) JOIN Sales.Customer C WITH(NOLOCK)
ON (SH.CustomerID = C.CustomerID)
JOIN Sales.SalesTerritory ST WITH(NOLOCK)
ON (ST.TerritoryID = SH.TerritoryID) 
WHERE  ST.Name='United Kingdom'
GO

Check whether the plan re-generated and stored entirely in Plan Cache
SELECT A.usecounts [Use Count], A.size_in_bytes [Plan Size(Bytes)],X.[text] [Query Used] FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text(A.[plan_handle]) AS X
WHERE A.objtype ='ADHOC' 
AND X.[Text] LIKE '%SELECT C.AccountNumber, SH.OrderDate%'

Now, New plan generated (See the use count column as 1) and stored the entire plan in plan cache (See the Plan Size(Bytes)).