Database Reference
In-Depth Information
This list is by no means complete, however it should serve as a good starting point.
■
read “SQl Server 2005 performance tuning using the Waits and Queues” whitepaper for more detail
about wait statistics-based performance troubleshooting methodology. it is available for download at:
http://technet.microsoft.com/en-us/library/cc966413.aspx
. even though this whitepaper was written to
address SQl Server 2005, the information within it applies to any newer version of SQl Server.
Note
What to Do When the Server Is Not Responding
Situations where SQL Server stops responding, or where it is not accepting user requests, do not happen very often.
Nevertheless, they do sometimes happen, and the first and most important rule is not to panic. SQL Server always
treats data consistency as its top priority, and it is highly unlikely that something will happen to the data.
As a first step, you should validate that the problem is not infrastructure-related. You should check that the server
and network are up and running and that the problem is not isolated to particular client workstation or subset of the
network. It is entirely possible that the problem is not related to SQL Server at all. For example, changes in a firewall
configuration or a network switch malfunction could block communication between SQL Server and client applications.
Next, you should check the SQL Server error log. Some conditions, for example prolonged worker thread
starvation, leave error messages in the log, notifying the system administrator about the problem. Moreover, such
conditions could introduce unhandled internal exceptions and mini-dumps. Unfortunately, there is no guarantee
that SQL Server always recovers after such exceptions, and in some cases you will need to restart it. The key point
with restart, however, is performing root-cause analysis of the problem. You need to analyze the error logs and
default trace, do the research and, in some cases, open a support case with Microsoft to make sure that the problem is
detected and addressed.
■
unhandled exceptions often occur due to bugs in SQl Server, which may already be fixed in the most recent
service packs and cumulative updates. Consider applying them and open a support case with microsoft CSS if this does
not help.
Note
You might need to connect to SQL Server for further troubleshooting. Fortunately, SQL Server 2005 introduced
a special connection called
Dedicated Admin Connection (DAC)
that can be used for such a purpose. SQL Server
reserves a private scheduler and a small amount of memory for DAC, which will allow you to connect even when SQL
Server does not accept regular connections.
By default, DAC is available only locally. In some cases, when a server is completely overloaded, the operating
system would not have adequate resources to handle user sessions, which prevents you from using DAC in local mode.
You can change the configuration setting to allow a remote DAC connection with the code shown in Listing 27-14.
Listing 27-14.
Enabling Remote Admin Connection
exec sp_configure 'remote admin connections', 1
go
reconfigure
go
