Tuesday, March 12, 2013

SQL Queries to check configurations in SQL Server

SQL Queries to check different server Properties:

1.    Max Server Memory:


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

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

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)


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

5.    How to check Authentication mode:
            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:

    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.'
    growth AS 'GrowthValue',
    'GrowthIncrement' =
            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.'
FROM tempdb.sys.database_files;

1 comment: