-
Notifications
You must be signed in to change notification settings - Fork 2
/
sql_version_compression_memory_settings.sql
50 lines (47 loc) · 2.75 KB
/
sql_version_compression_memory_settings.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
SELECT
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL 2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL 2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL 2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL 2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL 2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL 2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL 2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL 2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL 2019'
ELSE 'unknown'
END AS MajorVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion
,case
when @@version like '%2022%' then 1
when @@version like '%2019%' then 1
when @@version like '%2017%' then 1
when @@version like '%2016%' and (@@version like '%(sp2)%' or @@version like '%(sp3)%') then 1
when @@version like '%enterprise%' then 1
else 0
end as backup_compression_available
,(select cast(value_in_use as bigint) FROM sys.configurations where name = 'backup compression default') as backup_compression_setting
,case
when case
when @@version like '%2022%' then 1
when @@version like '%2019%' then 1
when @@version like '%2017%' then 1
when @@version like '%2016%' and (@@version like '%(sp2)%' or @@version like '%(sp3)%') then 1
when @@version like '%enterprise%' then 1
else 0
end = 1
and (select cast(value_in_use as bigint) FROM sys.configurations where name = 'backup compression default') = 0 then 'enable default backup compression'
else 'no change'
end as enable_default_backup_compression
,(select cast(value_in_use as bigint) FROM sys.configurations where name = 'max server memory (MB)') as max_server_memory_mb_setting
,(select [total_physical_memory_kb] / 1024 FROM [master].[sys].[dm_os_sys_memory]) as [server_physical_memory_mb]
,cast((select cast(value_in_use as bigint) FROM sys.configurations where name = 'max server memory (MB)')
/ (select [total_physical_memory_kb] / 1024.0 FROM [master].[sys].[dm_os_sys_memory]) * 100 as numeric(32, 1))
as pct_max_mem_to_server_ram
,case
when (select cast(value_in_use as bigint) FROM sys.configurations where name = 'max server memory (MB)')
/ (select [total_physical_memory_kb] / 1024.0 FROM [master].[sys].[dm_os_sys_memory]) * 100 between 85 and 95 then 'no change'
else 'Adjust max_memory'
end as max_server_memory_setting_needs_adjustment