Tuesday, March 12, 2013

SQL Queries to check configurations in SQL Server



SQL Queries to check different server Properties:

1.    Max Server Memory:

Queries:

SELECT * FROM sys.Configurations WHERE
NAME = 'max server memory (MB)'

SELECT * FROM sys.dm_os_sys_memory


Update Query for Max Server Memory:

Exec sp_configure 'max server memory (MB)', 12228
Reconfigure


2.    Backup Compression Default:
Default Value: 0
Updated Value as per standards: 1

Queries:
SELECT * FROM sys.Configurations WHERE
NAME = 'backup compression default'


How to update Backup Compression:
Exec sp_configure 'backup compression default', 1



3.    Instant Data File Initialization:
Default Value: Off
Standards: On

4.    MAXDOP
Default Value: 0
Standards: n (If there are n processors)

Queries:

exec master..xp_msver processorCount
Exec sp_configure 'max degree of parallelism', 4


5.    How to check Authentication mode:
SELECT
            CASE SERVERPROPERTY('IsIntegratedSecurityOnly')  
                        WHEN 1 THEN 'Windows Authentication'  
                        WHEN 0 THEN 'Windows and SQL Server Authentication'  
            END as [Authentication Mode] 


6.    How to check TempDB Properties:

SELECT
    name AS FileName,
    size*1.0/128 AS FileSizeinMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;



No comments:

Post a Comment