SQL Configuration Values

When working with SQL Server, understanding its configuration settings is crucial for optimal performance and troubleshooting. The sys.configurations system catalog view provides a wealth of information about all the server-wide configuration options. Here's a quick way to view all of them:

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

This query gives you a comprehensive list, including the name of each configuration, its current value, the value actually in use, its minimum and maximum possible settings, a description, and whether it's dynamic (can be changed without restarting the server) or advanced (should only be changed by experienced administrators).

While reviewing all configurations is helpful, you'll often want to check specific settings that are critical for performance or common troubleshooting scenarios. Here are some examples of important configurations and their typical recommended values:

select * from  sys.configurations WITH (NOLOCK) where name like 'automatic soft-NUMA%'  --should be 0 in most cases
select * from  sys.configurations WITH (NOLOCK) where name like 'backup checksum%'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'backup compression default%'  --should be 1 in most cases
select * from  sys.configurations WITH (NOLOCK) where name like 'clr enabled%'  --only enable if it is needed
select * from  sys.configurations WITH (NOLOCK) where name like 'cost threshold for parallelism%'  --(depends on your workload)
select * from  sys.configurations WITH (NOLOCK) where name like 'lightweight%'  --should be zero
select * from  sys.configurations WITH (NOLOCK) where name like 'max degree of parallelism%'  --depends on your workload and hardware
select * from  sys.configurations WITH (NOLOCK) where name like 'max server memory%'  --set to an appropriate value, not the default
select * from  sys.configurations WITH (NOLOCK) where name like 'optimize for ad hoc workloads'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'priority boost%'  --should be zero
select * from  sys.configurations WITH (NOLOCK) where name like 'remote admin connections%'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'tempdb metadata memory-optimized%'  --0 by default, some workloads may benefit by enabling

Remember that the "best" setting for some of these options, like cost threshold for parallelism and max degree of parallelism, can vary significantly depending on your specific workload and server hardware. Always test changes in a development environment before applying them to production.

For more in-depth details on each configuration option, you can always refer to the official Microsoft documentation for sys.configurations.

SQL Information

Ever wonder about the specific build and version of your SQL Server instance? You can easily find out using this simple SQL query:

SELECT @@SERVERNAME AS [Server Name], @@VERSION AS [SQL Server and OS Version Info];

Speaking of versions, here's a detailed look at the various builds of **SQL Server 2019**, including their release dates and links to relevant Microsoft Knowledge Base (KB) articles:

Build	Description	Release Date
15.0.1000.34	CTP 2.0	9/24/2018
15.0.1100.94	CTP 2.1	11/7/2018
15.0.1200.24	CTP 2.2	12/6/2018
15.0.1300.359	CTP 2.3	3/1/2019
15.0.1400.75	CTP 2.4	3/26/2019
15.0.1500.28	CTP 2.5	4/23/2019
15.0.1600.8	CTP 3.0	5/22/2019
15.0.1700.37	CTP 3.1	6/26/2019
15.0.1800.32	CTP 3.2	7/24/2019
15.0.1900.25	RC1/RC1 Refresh	8/29/2019
15.0.2000.5	RTM	11/4/2019
15.0.2070.41	GDR1	11/4/2019

For more comprehensive information, you can refer to these official Microsoft resources:

• Servicing Update for SQL Server 2019 RTM
• How to determine the version, edition and update level of SQL Server and its components
• KB4518398 - SQL Server 2019 Build Versions

Get in-memory OLTP index usage

Understanding how your SQL Server indexes are performing is crucial for database optimization. The following SQL query leverages sys.dm_db_xtp_index_stats to provide valuable insights into your in-memory OLTP (Hekaton) index usage. This dynamic management view gives you statistics like the number of scans, retries, rows touched, and rows returned for each index.

SELECT OBJECT_NAME(i.[object_id]) AS [Object Name],
       i.index_id,
       i.[name] AS [Index Name],
       i.[type_desc],
       xis.scans_started,
       xis.scans_retries,
       xis.rows_touched,
       xis.rows_returned
FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON i.[object_id] = xis.[object_id]
                                          AND i.index_id = xis.index_id
ORDER BY OBJECT_NAME(i.[object_id])
OPTION (RECOMPILE);

This query helps you identify which indexes are being heavily utilized and which might be underperforming or not being used efficiently. For more detailed information on sys.dm_db_xtp_index_stats, you can refer to the official Microsoft documentation:

sys.dm_db_xtp_index_stats (Transact-SQL) - SQL Server | Microsoft Docs

You might also find these related articles helpful for further exploration of SQL Server memory management and performance:

• How to Get Any Memory Dumps from SQL Server
• Understanding SQL Server Memory Clerk Usage
• Checking for Memory Pressure on Your SQL Server
• What Does the Memory Grants Pending Value Mean in SQL Server?
• Exploring Resource Governor and Resource Pool Information

Find Your Database's Query Store Settings

SQL query that pulls data from the sys.database_query_store_options view.

This query is likely related to monitoring or troubleshooting the SQL Server Query Store settings.

Here's a quick breakdown of the query:

SELECT
  actual_state_desc,               -- Description of the current state of the Query Store
  desired_state_desc,              -- Description of the desired state of the Query Store
  interval_length_minutes,         -- The length of the interval in minutes for Query Store data collection
  current_storage_size_mb,         -- The current size of the Query Store in MB
  max_storage_size_mb,             -- The maximum storage size for the Query Store in MB
  query_capture_mode_desc,         -- The capture mode for query plans and runtime statistics
  size_based_cleanup_mode_desc     -- The cleanup mode used when managing Query Store size
FROM sys.database_query_store_options WITH (NOLOCK)
OPTION (RECOMPILE);

Key Points:

• WITH (NOLOCK): This hint tells SQL Server to perform a dirty read, meaning it doesn't acquire shared locks and can read uncommitted data. This can speed up queries but might return inconsistent results if data is being modified.
• OPTION (RECOMPILE): Forces SQL Server to recompile the query plan each time it's run, which could be useful for queries with varying parameters but may add overhead.