Wednesday, August 19, 2015

What port is SQL Server listening on?

I was going through some of my older posts today, and I found a response to one that I had never seen before.  You'll see it at the bottom of this page:
    http://www.sqlfingers.com/2011/05/tcp-port-ip-address.html

My post is just a quick tip for finding the port that SQL Server is listening on, using xp_regread. As my reader says, xp_regread is undocumented and unsupported... I have used the xp's many times over the years, but the point is valid. They're unsupported and could even be gone in the next build.  If you can work around them, you probably should.

And I have! One of my favorite DMVs is sys.dm_exec_connections. You can use it to monitor your active connections and all kinds of fun stuff, as I've sampled hereIn this particular case, we can use sys.dm_exec_connections to return the port number that SQL Server is listening on. 

       SELECT local_tcp_port
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

Your output will resemble this:
















Super easy, documented... and no longer unsupported.  :-) 

Take a look at both of these for more details regarding the DMVs:
    https://msdn.microsoft.com/en-us/library/ms181509(v=sql.110).aspx
    https://msdn.microsoft.com/en-us/library/ms188754.aspx

Monday, August 17, 2015

Use SERVERPROPERTY to list the SQL Server Instance Name

I received an email from one of my customers this morning -- " How do I get the name of the SQL instance and what version it is, with tSQL? "  I love the easy ones! :-)
This is a quick post of one of the examples I gave to them, using SERVERPROPERTY.  

         SELECT 
                 SERVERPROPERTY('MachineName') [HostName]
                SERVERPROPERTY('ServerName') [SQLServerName]
                 CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'DEFAULT' 
                     ELSE SERVERPROPERTY('InstanceName') END [InstanceName]
                 LEFT(@@Version, CHARINDEX('-', @@version) - 2) [ReleaseName],
                 SERVERPROPERTY('Edition') [Edition],
                 SERVERPROPERTY('ProductVersion') [ProductVersion]

See the CASE on 'InstanceName' -- I'm just doing that to return either the name of the named instance, or 'DEFAULT', if it is not a named instance.

These are the results from one of my v2012 instances:



Please also take a look at MSDN for more details on SERVERPROPERTY:
    https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx

Friday, August 14, 2015

SQL Server - Query all non-default configurations

If you're a SQL Server DBA, then you know sp_configure. If not, well... it's time to learn. sp_configure is a system stored procedure which allows you to view or change SQL Server configuration settings. Min/max memory, backup compression, fill factor (%), etc. There are many different opinions on which configurations should be changed to what, but that's not what this post is about. I just want to show you how to quickly list all non-default SQL Server configuration settings. Why? Well, maybe you just inherited a server, or maybe you're troubleshooting a problem and want to see if any configurations could be contributing -- or any that could help! This returns ONLY the non-defaults. Much easier than running 'EXEC sp_configure' and then going through all configurations...

      /* Query SQL Server for any non-default configuration settings.    */
      SET NOCOUNT ON;

-- declarations
DECLARE
@version VARCHAR(128),
@charindex BIGINT,
@majversion VARCHAR(MAX),
@temp SQL_VARIANT;

-- set local variables
SELECT @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128));
SELECT @charindex = CHARINDEX('.', @version);
SET @majversion = SUBSTRING(@version, 1, @charindex-1);
IF @majversion IN (9,10)
       SET @temp = 20  ELSE SET @temp = 10

-- load all defaults into table variable
DECLARE @defaults TABLE (
id int IDENTITY(1,1),
config VARCHAR(128),
value SQL_VARIANT
);

INSERT @defaults VALUES
('access check cache bucket count', 0),
('access check cache quota', 0),
('Ad Hoc Distributed Queries', 0),
('affinity I/O mask', 0),
('affinity64 I/O mask', 0),
('affinity mask', 0),
('affinity64 mask', 0),
('Agent XPs',0),
('allow updates', 0),
('backup compression default', 0),
('blocked process threshold', 0),
('c2 audit mode', 0),
('clr enabled', 0),
('common criteria compliance enabled', 0),
('contained database authentication', 0),
('cost threshold for parallelism', 5),
('cross db ownership chaining', 0),
('cursor threshold', -1),
('Database Mail XPs', 0),
('default full-text language', 1033),
('default language', 0),
('default trace enabled', 1),
('disallow results from triggers', 0),
('EKM provider enabled', 0),
('filestream_access_level', 0),
('fill factor(%)', 0),
('ft crawl bandwidth (max)', 100),
('ft crawl bandwidth (min)', 0),
('ft notify bandwidth (max)', 100),
('ft notify bandwidth (min)', 0),
('index create memory(KB)', 0),
('in-doubt xact resolution', 0),
('lightweight pooling', 0),
('locks', 0),
('max degree of parallelism', 0),
('max full-text crawl range', 4),
('max server memory(MB)', 2147483647),
('max text repl size(B)', 65536),
('max worker threads', 0),
('media retention', 0),
('min memory per query(KB)', 1024),
('min server memory(MB)', 0),
('nested triggers', 1),
('network packet size(B)', 4096),
('Ole Automation Procedures', 0),
('open objects', 0),
('optimize for ad hoc workloads', 0),
('PH timeout(s)', 60),
('precompute rank', 0),
('priority boost', 0),
('query governor cost limit', 0),
('query wait(s)', -1),
('recovery interval(min)', 0),
('remote access', 1),
('remote admin connections', 0),
('remote login timeout(s)', @temp),
('remote proc trans', 0),
('remote query timeout(s)', 600),
('Replication XPs', 0),
('scan for startup procs', 0),
('server trigger recursion', 1),
('set working set size', 0),
('show advanced options', 0),
('SMO and DMO XPs', 1),
('transform noise words', 0),
('two digit year cutoff', 2049),
('user connections', 0),
('user options', 0),
('xp_cmdshell', 0);

-- now pull back which ones you're using that aren't defaults
SELECT
s.name [Configuration],
s.value_in_use [Yours],
d.Value [Default]
FROM
@defaults d JOIN sys.configurations s
 ON s.name LIKE '%' + d.config + '%'
 AND d.Value <> s.value_in_use
WHERE
s.name <> 'show advanced options'
ORDER BY
s.name

These are the results from my own instance:




That's about it.  Now there are many different opinions about which configs should be changed, and to what. I've got my changes that I make on every server I manage, and then there are other changes that I make, for different needs. Then, of course, there are countless other recommendations from the other SQL experts out there. These are two of my favorites:  

www.brentozar.com - Five SQL Server Settings to Change
http://sqlblog.com  - Did You Know? What settings to always change

I would also be sure to take a look at this one for more detail on sp_configure:
     https://msdn.microsoft.com/en-us/library/ms188787.aspx