Delivering a SQL Server Health Check - SQL Server 2012 Internals and Troubleshooting - page 414

NOTE Whenever I see every instance-level property set at its default value, I
know that the DBA or other administrator who installed and/or manages this
instance is inexperienced or perhaps just an “accidental DBA” who does not
understand which instance settings should be changed. It is also possible that this
person was just too busy to change anything from the default settings or does not
pay sufi cient attention to detail (a critical trait for a great DBA).
Next, you are going to i nd out a little bit about the network coni guration settings on the database
server with the query shown in Listing 15-12.
LISTING 15-12: TCP Listener information
-- Get information about TCP Listener for SQL Server
SELECT listener_id, ip_address, is_ipv4, port, type_desc, state_desc, start_time
FROM sys.dm_tcp_listener_states WITH (NOLOCK) OPTION (RECOMPILE);
-- Helpful for network and connectivity troubleshooting
This DMV, which was added in SQL Server 2008 R2 SP1, tells you which TCP ports are being
used by the TCP Listener — for T-SQL, the Service Broker, and database mirroring. This is useful
information for troubleshooting general network connectivity and i rewall issues. It is also useful
for investigating connectivity issues with SQL Server AlwaysOn availability replicas.
The next query, shown in Listing 15-13, provides SQL Server-related information collected from
the Windows Registry, using the sys.dm_server_registry DMV that was added in SQL Server
2008 R2 SP1.
LISTING 15-13: SQL Server Registry information
-- SQL Server Registry information
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry WITH (NOLOCK) OPTION (RECOMPILE);
-- This lets you safely read some SQL Server related
-- information from the Windows Registry
This query, which was added in SQL Server 2008 R2 SP1, gives you quite a bit of useful informa-
tion about your SQL Server instance, such as which network protocols are enabled, where the SQL
Server main executable is located, and where the SQL Server Agent executable is installed. This is
safer and easier to use than the old xp_instance_regread extended stored procedure, although it
admittedly does not allow you to query as many different values.
Next, to investigate whether this instance of SQL Server has been generating any memory dumps,
you can use the query shown in Listing 15-14.