madeiradata / madeiratoolbox

Open-source Repository of Useful Scripts and Solutions for Microsoft Data Platform Administrators

This Repository is DEPRECATED

This was the official Toolbox repository for Madeira's consultant team, for "as-is" solutions and tools/scripts that the team publishes.

Since March 2024, this repository has been CLOSED to further changes, in favor of SPLITTING it into 3 new repositories separated by fields of specialization:

• Database Administrators: MadeiraToolbox\microsoft-dbas-club
• Data Engineers: MadeiraToolbox\microsoft-data-engineers-club
• Data Analysts: MadeiraToolbox\microsoft-data-analysts-club

Please subscribe to those new repositories instead ♥.

Please visit our official website at:

www.madeiradata.com

For more community resources, visit our official community web pages:

• Microsoft Data Engineers Club
• Microsoft Data Anslysts Club
• Microsoft DBAs Club
• Microsoft Data Leaders Club

License

All Madeira Toolbox materials in this repository are released under the MIT license.

madeiratoolbox's Issues

Add missing checks to the Condensed BP Check script

We have a "condensed check" script of best practices that we're usually using for the SQL Server Review:

https://github.com/MadeiraData/MadeiraToolbox/blob/8f478c3166d8e3f0284bbea12a69f33afda7716d/Best%20Practices%20Checklists/Best%20Practices%20Condensed%20Check.sql

However, it's incomplete, and missing several checks, most of which exist in other scripts:

• High VLF Count
• Foreign Keys without corresponding indexes
• Heap Tables
• Invalid owner for system schema role or database
• Outdated Statistics
• Redundant Indexes
• Orphaned jobs
• Additional checks from Azure-SQL-Tips
• Table and Index High Unused Space

Feel free to also add more checks and list them here.

Best practices not suitable for Azure SQL

Hi,
Is there any plan (or other script) that can check best practices for Azure SQL?
I've tried using this but it failed as AzureSql doesn't have msdb access

The REVOKE simulation feature is too dangerous to run on production systems

Relevant script: PUBLIC role with excessive permissions.sql

Problem: The REVOKE simulation feature is too dangerous to run on production systems.

Suggested solution: Optionally utilize the DBCC CLONEDATABASE command to create a schema-only clone of the database(s) in question, set them to READ_WRITE, and perform the REVOKE simulation in them to discover affected users.

Add support for COLUMNSTORE and COLUMNSTORE_ARCHIVE compression type checks

MadeiraToolbox/Utility Scripts/ultimate_compression_savings_estimation_whole_database.sql

Line 427 in f7bd4a4

CROSS JOIN (VALUES('PAGE'),('ROW')) AS ct(CompressionType) -- check both ROW and PAGE compression for each

MadeiraToolbox/Utility Scripts/ultimate_compression_savings_estimation_whole_database.sql

Line 306 in f7bd4a4

INNER JOIN sys.partitions AS p WITH(NOLOCK) ON t.object_id = p.object_id AND p.data_compression = 0

Limitations and Restrictions

Prior to SQL Server 2019, this procedure did not apply to columnstore indexes, and therefore did not accept the data compression parameters COLUMNSTORE and COLUMNSTORE_ARCHIVE. Starting with SQL Server 2019, columnstore indexes can be used both as a source object for estimation, and as a requested compression type.

IMPORTANT
When Memory-Optimized TempDB Metadata is enabled in sql-server-2019, creation of columnstore indexes on temporary tables is not supported. Because of this limitation, sp_estimate_data_compression_savings is not supported with the COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression parameters when Memory-Optimized TempDB Metadata is enabled.

Considerations for Columnstore Indexes

Starting with sql-server-2019, sp_estimate_compression_savings supports estimating both columnstore and columnstore archive compression. Unlike page and row compression, applying columnstore compression to an object requires creating a new columnstore index. For this reason, when using the COLUMNSTORE and COLUMNSTORE_ARCHIVE options of this procedure, the type of the source object provided to the procedure determines the type of columnstore index used for the compressed size estimate. The following table illustrates the reference objects used to estimate compression savings for each source object type when the @data_compression parameter is set to either COLUMNSTORE or COLUMNSTORE_ARCHIVE.

Source Object	Reference Object
Heap	Clustered columnstore index
Clustered index	Clustered columnstore index
Nonclustered index	Nonclustered columnstore index (including the key columns and any included columns of the provided nonclustered index, as well as the partition column of the table, if any)
Nonclustered columnstore index	Nonclustered columnstore index (including the same columns as the provided nonclustered columnstore index)
Clustered columnstore index	Clustered columnstore index

NOTE
When estimating columnstore compression from a rowstore source object (clustered index, nonclustered index or heap), if there are any columns in the source object that have a data type that is not supported in a columnstore index, sp_estimate_compression_savings will fail with an error.

Similarly, when the @data_compression parameter is set to NONE, ROW, or PAGE and the source object is a columnstore index, the following table outlines the reference objects used.

Source Object	Reference Object
Clustered columnstore index	Heap
Nonclustered columnstore index	Nonclustered index (including the columns contained in the nonclustered columnstore index as key columns, and the partition column of the table, if any, as an included column)

NOTE
When estimating rowstore compression (NONE, ROW or PAGE) from a columnstore source object, be sure that the source index does not contain more than 32 columns as this is the limit supported in a rowstore (nonclustered) index.

Resources:

• https://docs.microsoft.com/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-ver15#limitations-and-restrictions
• https://docs.microsoft.com/sql/relational-databases/indexes/columnstore-indexes-design-guidance
• https://docs.microsoft.com/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics

Add Where Clause to Find overlapped statistics + DROP statement.sql

Great script but it recommends removing statistics that are not completely overlapping. If the "overlapped" statistic is a filtered index then the script will recommend removing the auto generated statistic but the statistic will then be auto-recreated again.
Adding " AND S.has_filter = 0" to the last where clause fixes this in a "sledgehammer" way.

MaxDOP_Configuration_Check.sql gives me an incorrect result

Hi
I try some run on different server and found that the result isn't correct.

I got:
@@ServerName: MSSQL01
@EffectiveMaxDOP: 2 (YES TRUE)
@NumaNodeCount: 1 (NO I GOT 2 NUMA... see the image)

SELECT * FROM sys.dm_os_nodes

but from SSMS I got a single one?

Which is the correct?
I see you count distinct on this...

And also from server log:

• SQL Server detected 4 sockets with 4 cores per socket and 4 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
• Automatic soft-NUMA was enabled because SQL Server has detected hardware NUMA nodes with greater than 8 physical cores.

@LogicalProcessorPerNumaNodeCount: 16 (NO ARE 8... see the above)

MaxDOP is already within recommended range: 8 (NO IT's 2)

I'm little confused,
Thanks Alen