Month: November 2015

--Turn on advanced options

sp_configure​​ 'show advanced options',​​ 1

GO

RECONFIGURE

GO

--Configure min server memory

sp_configure​​ 'min server memory',​​ 0

GO

RECONFIGURE

GO

--Configure max server memory

sp_configure​​ 'max server memory',​​ 2048

GO

RECONFIGURE

GO

--Configure processor affinity

ALTER​​ SERVER​​ CONFIGURATION​​ SET​​ PROCESS​​ AFFINITY​​ CPU​​ =​​ 1,​​ 2​​ --AUTO

GO

--Configure fill factor

sp_configure​​ 'fill factor',​​ 75

GO

RECONFIGURE

GO

EXEC​​ sp_configure​​ 'Show Advanced Options',​​ 1;

GO

RECONFIGURE;

GO

EXEC​​ sp_configure;

DECLARE​​ @PortNumber​​ VARCHAR(10),​​ @KeyValue​​ VARCHAR(100)

IF​​ CHARINDEX('\',@@SERVERNAME,0)​​ <>0

BEGIN

SET​​ @KeyValue​​ =​​ 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@SERVICENAME+'\MSSQLServer\Supersocketnetlib\TCP'

END

ELSE

BEGIN

SET​​ @keyValue​​ =​​ 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'

END

--How to read Registry in T-SQL Terms - ROOTKEY, KEY, VALUENAME,VALUE​​ 

EXEC​​ master..xp_regread​​ 'HKEY_LOCAL_MACHINE',@KeyValue,'Tcpport',@value=@PortNumber​​ OUTPUT

SELECT​​ 'SQLSERVER_NAME: '+@@SERVERNAME​​ +​​ ' : SQL SERVER Port Number:'+CONVERT(VARCHAR(10),

@PortNumber)