search

microsoft / azure-sql-tips

A T-SQL script for improving database configuration and design in Azure SQL DB.
https://aka.ms/sqldbtips
MIT License
144 stars 51 forks source link

Tips inside "groups" cannot be toggled independently #49

Closed EitanBlumin closed 3 years ago

EitanBlumin commented 3 years ago

On the one hand, it's possible to toggle the execution of specific tips on or off, via the @TipDefinition table.

On the other hand, some of these tips are "grouped" together and it's enough for just one of them to be enabled for execution to cause the whole group to be checked.

This could cause unexpected behavior when toggling specific tips on and off.

I believe the "root cause" of the problem would be the VALUES constructor used in queries such as this:

https://github.com/microsoft/azure-sql-tips/blob/b59bbabfc558076f43769d82359301c78a5a6dab/sqldb-tips/get-sqldb-tips.sql#L596

As an alternative, you should probably use a subquery that retrieves only the tips from @TipDefinition which are enabled for execution.

dimitri-furman commented 3 years ago

Thanks @EitanBlumin. Yes, this behavior can be unexpected. Ungrouping tips into their own queries would solve this, but at the expense of bulkier code and possibly slower perf. Checking exclusions within the tip query itself is probably a better option, though that adds some redundancy and complexity. Not yet sure if that's warranted. Let us consider this for a bit and come up with a solution. Thanks again for reporting this.

EitanBlumin commented 3 years ago

Understandable. Although you already have a PRIMARY KEY defined on the @TipDefinition table on the tip_id column, so the performance impact probably wouldn't be too bad.