SQL Server Process Memory Information

This query returns SQL Server Process Address space information.

SQL Query

SELECT physical_memory_in_use_kb/1024 AS [SQL Server Memory Usage (MB)],
       large_page_allocations_kb, locked_page_allocations_kb, page_fault_count,
          memory_utilization_percentage, available_commit_limit_kb,
          process_physical_memory_low, process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

Output

The value 0 for process_physical_memory_low and process_virtual_memory_low indicates that you are not under internal memory pressure. If locked_page_allocations_kb value is greater than zero, then Lock Pages in Memory is enabled.

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-process-memory-transact-sql

 

SQL Server Instance-Level Configuration Values

This query returns SQL Server Instance-Level Configuration Values.

SQL Query

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

Output

Let’s focus on these settings:-

• Automatic soft-NUMA disabled (should be 0 in most cases)
• Backup checksum default (should be 1)
• Backup compression default (should be 1 in most cases)
• Clr enabled (only enable if it is needed)
• Cost threshold for parallelism (depends on your workload)
• Lightweight pooling (should be zero)
• Max degree of parallelism (depends on your workload and hardware)
• Max server memory (MB) (set to an appropriate value, not the default)
• Optimize for ad hoc workloads (should be 1)
• Priority boost (should be zero)
• Remote admin connections (should be 1)

New configuration options for SQL Server 2016

• Allow PolyBase export (Allow INSERT into a Hadoop external table)
• Automatic soft-NUMA disabled (Automatic soft-NUMA is enabled by default)
• External scripts enabled (Allows execution of external scripts, for R Services)
• Hadoop connectivity (Configure SQL Server to connect to external Hadoop or Microsoft Azure storage blob data sources through PolyBase)
• PolyBase network encryption (Configure SQL Server to encrypt control and data channels when using PolyBase)
• Remote data archive (Allow the use of the REMOTE_DATA_ARCHIVE data access for Stretch databases)

New configuration options for SQL Server 2017

• Clr strict security (is enabled by default)

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-configurations-transact-sql

 

Shrink the Transaction Log File

This article describes how to shrink a data or log file of a database in SQL Server by using SQL Server Management Studio or Transact-SQL.

Transaction log growth can occur for a few different reasons. Long running transactions, incorrect recovery model configuration and lack of log backups can grow the log.

Typically, truncation occurs automatically under the simple recovery model when database is backed up and under the full recovery model when the transaction log is backed up.

Log truncation frees up space in the log file so the transaction log can reuse it. Let’s see two ways to shrink the log file.

Shrink the Log in SQL Server Management Studio

To shrink the log in SSMS, right click the database, choose Tasks -> Shrink -> Files:

On the Shrink File window, select the File type to Log. You can also choose to either release unused space, reorganize pages before releasing unused space, or empty file by migrating the data to other files in the same file group:

DBCC SHRINKFILE

Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same file group, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

If the database is in FULL recovery model you could set it to SIMPLE, run DBCC SHRINKFILE, and set back to FULL.

Provide the logical name of the log file and the target size in MB you need to shrink.

USE [VMCRT8030ITE]
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE VMCRT8030ITE 
SET RECOVERY SIMPLE; 
GO 
-- Shrink the truncated log file to 1 MB. 
DBCC SHRINKFILE (log01, 1); 
GO 
-- Reset the database recovery model. 
ALTER DATABASE VMCRT8030ITE 
SET RECOVERY FULL; 
GO

You can find the logical name of the log file of your database by using the following query:

USE [VMCRT8030ITE]
GO 
SELECT name FROM sys.master_files WHERE type_desc = 'LOG' and database_id = (select DB_ID())

Security and Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

References

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql

 

SQL Server Properties

This query returns selected SQL server properties. This gives you a lot of useful information about your instance of SQL Server, such as the ProcessID for SQL Server and your collation etc.

SQL Query

SELECT SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('ServerName') AS [ServerName], 
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],  -- What servicing branch (RTM/SP/CU)
SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
SERVERPROPERTY('ProductBuild') AS [ProductBuild],
SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],-- Is this a GDR or OD hotfix (NULL if on a CU build)
SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled], -- New for SQL Server 2016
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled];  -- New for SQL Server 2016

Output

References

https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql