~楓花雪岳~: [SQL] 伺服器組態選項

伺服器組態選項就是 SQL Server instance 屬性。

介面組態區

在 SQL Server 2005 是在 SQL Server 介面組態區進行設定，SQL Server 2008 開始則是整合在 instance Facet 中。

2005 畫面

2012 畫面

利用 sys.configurations 檢視伺服器組態選項

SELECT 
    name ,
    description ,
    value ,
    value_in_use ,
    minimum ,
    maximum ,
    is_advanced
FROM sys.configurations

修改伺服器組態選項 - Database Mail XPs 為例說明

利用 SSMS 來變更：instance => 右鍵 => Facet => 介面區組態 => Database Mail XPs => True

利用 T-SQL 語法變更

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

系統預設值檢查

從 sp_BLITZ™ – SQL Server Takeover Script 截取相關內容

-- 整理至 SQL Server 2012 版本
DECLARE @ConfigurationDefaults TABLE
(
  name NVARCHAR(128) ,
  DefaultValue BIGINT
)
 
INSERT INTO @ConfigurationDefaults VALUES  ('access check cache bucket count', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('access check cache quota', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('Ad Hoc Distributed Queries', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('affinity I/O mask', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('affinity mask', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('Agent XPs', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('allow updates', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('awe enabled', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('blocked process threshold', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('c2 audit mode', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('clr enabled', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('cost threshold for parallelism', 5);
INSERT INTO @ConfigurationDefaults VALUES  ('cross db ownership chaining', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('cursor threshold', -1);
INSERT INTO @ConfigurationDefaults VALUES  ('Database Mail XPs', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('default full-text language', 1033);
INSERT INTO @ConfigurationDefaults VALUES  ('default language', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('default trace enabled', 1);
INSERT INTO @ConfigurationDefaults VALUES  ('disallow results from triggers', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('fill factor (%)', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('ft crawl bandwidth (max)', 100);
INSERT INTO @ConfigurationDefaults VALUES  ('ft crawl bandwidth (min)', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('ft notify bandwidth (max)', 100);
INSERT INTO @ConfigurationDefaults VALUES  ('ft notify bandwidth (min)', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('index create memory (KB)', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('in-doubt xact resolution', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('lightweight pooling', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('locks', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('max degree of parallelism', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('max full-text crawl range', 4);
INSERT INTO @ConfigurationDefaults VALUES  ('max server memory (MB)', 2147483647);
INSERT INTO @ConfigurationDefaults VALUES  ('max text repl size (B)', 65536);
INSERT INTO @ConfigurationDefaults VALUES  ('max worker threads', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('media retention', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('min memory per query (KB)', 1024);
INSERT INTO @ConfigurationDefaults VALUES  ('nested triggers', 1);
INSERT INTO @ConfigurationDefaults VALUES  ('network packet size (B)', 4096);
INSERT INTO @ConfigurationDefaults VALUES  ('Ole Automation Procedures', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('open objects', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('optimize for ad hoc workloads', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('PH timeout (s)', 60);
INSERT INTO @ConfigurationDefaults VALUES  ('precompute rank', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('priority boost', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('query governor cost limit', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('query wait (s)', -1);
INSERT INTO @ConfigurationDefaults VALUES  ('recovery interval (min)', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('remote access', 1);
INSERT INTO @ConfigurationDefaults VALUES  ('remote admin connections', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('remote proc trans', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('remote query timeout (s)', 600);
INSERT INTO @ConfigurationDefaults VALUES  ('Replication XPs', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('RPC parameter data validation', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('scan for startup procs', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('server trigger recursion', 1);
INSERT INTO @ConfigurationDefaults VALUES  ('set working set size', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('show advanced options', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('SMO and DMO XPs', 1);
INSERT INTO @ConfigurationDefaults VALUES  ('SQL Mail XPs', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('transform noise words', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('two digit year cutoff', 2049);
INSERT INTO @ConfigurationDefaults VALUES  ('user connections', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('user options', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('Web Assistant Procedures', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('xp_cmdshell', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('affinity64 mask', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('affinity64 I/O mask', 0);
INSERT INTO @ConfigurationDefaults VALUES  ('contained database authentication', 0);
 
-- 0 和 16 都是設定值
IF EXISTS 
  ( 
      SELECT 1 
      FROM sys.configurations 
      WHERE name = 'min server memory (MB)' 
          AND value_in_use IN (0,16) 
  ) 
    INSERT INTO @ConfigurationDefaults
    SELECT 
        'min server memory (MB)' , 
        CAST(value_in_use AS BIGINT)
    FROM sys.configurations
    WHERE name = 'min server memory (MB)'
 ELSE
    INSERT INTO @ConfigurationDefaults 
    VALUES  ('min server memory (MB)', 0);
 
IF @@VERSION LIKE '%Microsoft SQL Server 2005%'
    OR @@VERSION LIKE '%Microsoft SQL Server 2008%'
    INSERT INTO @ConfigurationDefaults VALUES ('remote login timeout (s)', 20)
ELSE
    -- 2012
    INSERT  INTO @ConfigurationDefaults VALUES ('remote login timeout (s)', 10);
      
 
SELECT 
    cr.name AS ServerConfig ,
    ( 
        '預設值： ' + ISNULL(CAST(cd.[DefaultValue] AS VARCHAR(100)),'(unknown)') + ' ' +
        '目前： ' + CAST(cr.value_in_use AS VARCHAR(100))
    ) AS Details
FROM sys.configurations cr
  JOIN @ConfigurationDefaults cd ON cd.name = cr.name
  LEFT JOIN @ConfigurationDefaults cdUsed ON cdUsed.name = cr.name 
                                              AND cdUsed.DefaultValue = cr.value_in_use
WHERE cdUsed.name IS NULL