CIS Benchmark : Center for internet security

1 Installation, Updates and Patches
 Ensure Latest SQL Server Service Packs and Hotfixes are Installed

SELECT SERVERPROPERTY('ProductLevel') as SP_installed, SERVERPROPERTY('ProductVersion') as Version;

2. Ensure Single-Function Member Servers are Used

Ensure that no other roles are enabled for the underlying operating system and that no excess tooling is installed, per enterprise policy.

3.Surface Area Reduction 

Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries';

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE; EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

4 Ensure 'CLR Enabled' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled';

 EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;

Move Database

(A) Move USER database :3 methods

1 Backup and restore on new location

2. attach and detach database

3. Alter Command

First check current location

sp_helpdb 'database name'
or
SELECT name,physical_name FROM AdventureWorks.sys.database_files

ALTER DATABASE AdventureWorks SET OFFLINE

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME ='D:\MSSQL\Data\databasename.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME ='D:\MSSQL\Log\databasename_Log.ldf');

ALTER DATABASE AdventureWorks SET ONLINE

(B) Move master Database to another drive

(C) Move Temp Database to another drive  

SQL Query Performance

common issues with performance such as:

• deadlocks
• blocking
• missing and unused indexes
• I/O bottlenecks
• poor query plans
• statistics
• wait stats
• fragmentation

Tools to collect performance related data.

• Dynamic Management Views (DMVs) and System Catalog Views
• Profiler and Server Side Traces
• Windows Performance Monitor
• Built in performance reports in SSMS
• Query Plans
• Database Tuning Advisor

Server Health Check Checklist

1  SQL Server Service Health Check

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'

2 SQL Server Backup Health Check

SELECT d.name AS "Database",
 ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
 WHERE type LIKE 'D'
 GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
SELECT d.name AS "Database",
 ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
 WHERE type LIKE 'L'
 GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
 AND (backupdate IS NULL OR backupdate < getdate()-1)

3. Check for Available Disk Space
exec master.dbo.xp_fixeddrives

4. Check for free memory available for SQL Server

SELECT available_physical_memory_kb/1024 as "Total Memory MB",
 available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory

5. Check the size of the transaction log

DBCC SQLPERF(LOGSPACE)

6. Check for Index Fragmentation

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'<YOUR DATABASE>'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

Performance Tuning

1) Missing Index

Performance is always associated with Indexes. If your database is missing many indexes, kindly execute the below script to create necessary missing indexes for your database tables. 

SELECT TOP 20

dm_mid.database_id AS DatabaseID,

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,

dm_migs.last_user_seek AS Last_User_Seek,

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')

+ CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL THEN '_'

ELSE ''

END

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')

+ ']'

+ ' ON ' + dm_mid.statement

+ ' (' + ISNULL (dm_mid.equality_columns,'')

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns

IS NOT NULL THEN ',' ELSE

'' END

+ ISNULL (dm_mid.inequality_columns, '')

+ ')'

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC




2) Unused Index 

A bad index can hamper the performance

SELECT TOP 25

o.name AS ObjectName

, i.name AS IndexName

, i.index_id AS IndexID

, dm_ius.user_seeks AS UserSeek

, dm_ius.user_scans AS UserScans

, dm_ius.user_lookups AS UserLookups

, dm_ius.user_updates AS UserUpdates

, p.TableRows

, 'DROP INDEX ' + QUOTENAME(i.name)

+ ' ON ' + QUOTENAME(s.name) + '.'

+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'

FROM sys.dm_db_index_usage_stats dm_ius

INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id

AND dm_ius.OBJECT_ID = i.OBJECT_ID

INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID

FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p

ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID

WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1

AND dm_ius.database_id = DB_ID()

AND i.type_desc = 'nonclustered'

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC




3) Statistics of Whole Database

SQL Server has a logic(500+20% of the table rows are affected) of updating outdated statistics. Two option for this

1. If you have left auto update or auto create statistics on, you should not worry at all, SQL Server will make the task itself.
2. If you have left auto update or auto create statistics off, you should manually update the statistics when it reaches the default of 500+ 20% of table rows.

EXEC sp_updatestats;




4) SQL Wait Statistics

Once SQL Server services have been restarted, manually clear the wait stats

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);