Helpful Queries – General – Welcome to our SQL World!

Query to Determine Indexes Size

The following query returns all indexes and their size (in MB) on your database. You can uncomment the where clause to determine the indexes size on any particular object.

SELECT
       OBJECT_SCHEMA_NAME(i.object_id) AS 'SchemaName',
       OBJECT_NAME(i.object_id) AS 'TableName',
       i.name AS 'IndexName',
       i.type_desc AS 'IndexType',
       CASE
              WHEN (i.[is_primary_key]) = 1 THEN 'PRIMARY KEY'
              WHEN (i.[is_unique]) = 1 THEN 'UNIQUE'
              WHEN (i.[is_unique]) = 0 THEN 'NON-UNIQUE'
       ELSE '' END AS 'ConstraintType',
       CONVERT(DECIMAL(10,2),(SUM(a.total_pages) * 8.00) / 1024.00)  AS 'Size_MB'
FROM
       sys.indexes i
       INNER JOIN sys.partitions p
              ON i.object_id = p.object_id AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a
              ON p.partition_id = a.container_id
WHERE
       OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
       AND i.name IS NOT NULL
       --AND object_name(i.Object_ID) = 'TAT_TIME'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc, i.[is_primary_key], i.[is_unique]
ORDER BY 1,2,6 desc

Let’s see what the total indexes size is for each table. You can uncomment the where clause to determine the total indexes size on any particular object.

SELECT
       OBJECT_SCHEMA_NAME(i.object_id) AS 'SchemaName',
       OBJECT_NAME(i.object_id) AS 'TableName',
       CONVERT(DECIMAL(10,2),(SUM(a.total_pages) * 8.00) / 1024.00)  AS 'Size_MB'
FROM
       sys.indexes i
       INNER JOIN sys.partitions p
              ON i.object_id = p.object_id AND i.index_id = p.index_id
       INNER JOIN sys.allocation_units a
              ON p.partition_id = a.container_id
WHERE
       OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
       AND i.name IS NOT NULL
       --AND object_name(i.Object_ID) = 'TAT_TIME'
GROUP BY i.object_id
ORDER BY 1,2

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql?view=sql-server-2017

Helpful Queries - General

Query Store Options

This query returns Query Store Options for the current database.

SQL Query

SELECT actual_state_desc, desired_state_desc,
       current_storage_size_mb, [max_storage_size_mb],
          query_capture_mode_desc, size_based_cleanup_mode_desc
FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql

Helpful Queries - General

Database-scoped Configurations

This query returns database scoped configuration values for the current database.

SQL Query

SELECT configuration_id, name, [value] AS [value_for_primary], value_for_secondary
FROM sys.database_scoped_configurations WITH (NOLOCK) OPTION (RECOMPILE);

Output

References

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

Helpful Queries - General

Host Information

This query returns host information. It provides you major OS version, Service Pack, Edition, and language info for the operating system.

SQL Query

SELECT host_platform, host_distribution, host_release,
       host_service_pack_level, host_sku, os_language_version
FROM sys.dm_os_host_info WITH (NOLOCK) OPTION (RECOMPILE); -- New in SQL Server 2017

Output

host_platform – The type of operating system: Windows or Linux.
host_distribution – Description of the operating system.
host_release – Microsoft Windows operating system release (version number).

6.3 is either Windows 8.1, Windows 10 or Windows Server 2012 R2, Windows Server 2016.

6.2 is either Windows 8 or Windows Server 2012

6.1 is either Windows 7 or Windows Server 2008 R2

6.0 is either Windows Vista or Windows Server 2008

For a list of values and descriptions, see Operating System Version (Windows).

For Linux, returns an empty string.

host_service_pack_level – Service pack level of the Windows operating system.
For Linux, returns an empty string.
host_sku – Windows Stock Keeping Unit (SKU) ID.

4 is Enterprise Edition

7 is Standard Server Edition

8 is Datacenter Server Edition

10 is Enterprise Server Edition

48 is Professional Edition

For Linux, returns NULL.

os_language_version – Windows locale identifier (LCID) of the operating system. For a list of LCID values and descriptions, see Locale IDs Assigned by Microsoft. For example, 1033 is for US-English.