Database Reference
In-Depth Information
You can connect to SQL Server with DAC by using the
ADMIN:
server name prefix in the Management Studio
connection box or with the
-A
option in
sqlcmd
. Only members of the
sysadmin
server role are allowed to connect,
and only one session can use a DAC connection at any point in time.
■
You should use the connection dialog initiated from the Query Window when you use DaC from
management Studio. object explorer uses multiple database connections by design, and therefore it cannot use DaC.
make sure that intellisense and other management Studio plug-ins are disabled before you attempt this connection.
Important
A DAC connection can utilize a limited amount of resources, and it has a few restrictions on what operations can
be done. For example, DAC does not support parallel query execution or backup/restore functions. It is designed for
troubleshooting, and you should use DAC only for such a purpose.
We have already discussed worker thread starvation as one reason SQL Server may become unresponsive.
Another possibility is
run-away queries
, which consume a major part of the resources on the server. You can detect
such queries based on the
sys.dm_exec_requests
view, as shown in Listing 27-15.
Listing 27-15.
Detecting run-away queries
select top 10
er.session_id
,er.start_time
,er.cpu_time
,er.status
,er.command
,er.blocking_session_id
,er.wait_time
,er.wait_type
,er.last_wait_type
,er.logical_reads
substring(qt.text, (er.statement_start_offset/2)+1,
((
case er.statement_end_offset
when -1 then datalength(qt.text)
else er.statement_end_offset
end - er.statement_start_offset)/2)+1) as SQL
from
sys.dm_exec_requests er with (nolock)
cross apply sys.dm_exec_sql_text(er.sql_handle) qt
order by cpu_time desc
option (recompile)
You can terminate a session with a run-away query using the
KILL
command. You should be careful, however,
and analyze what the session is doing. SQL Server rolls back the active session transaction when you terminate it,
which could be time and resource consuming in the case of heavy data modifications. It is entirely possible that
allowing a session to finish a task is a faster and better option.
You can also consider using Resource Governor to prevent tasks from consuming all SQL Server resources. This
could be especially useful if a server hosts multiple databases that belong to multiple systems. You can separate
connections to different systems between resource pools, configured in a way that leaves some resources available for
every system.
