Describe the bug
The TipID 1510 is being flagged and reaching a limit but that is not true.

There are two problems

1st one - the comparison:

https://github.com/microsoft/azure-sql-tips/blob/main/sqldb-tips/get-sqldb-tips.sql#L2011

To Reproduce
Steps to reproduce the behavior:

1. Create a sequence (seen in real life)

CREATE SEQUENCE dbo.testSequence
    AS BIGINT
    START WITH 10000
    INCREMENT BY 1
    MINVALUE -9223372036854775808
    MAXVALUE 9999999999;  

2. use some values

SELECT NEXT VALUE FOR dbo.testSequence
GO 100

3. Run the 1520 tip with default configured ration 0.2 (meaning we want to be warned if there is less than 20% available, correct?)

The sequence is flagged with following message:
schema: [dbo], sequence: [testSequence], data type: bigint, start value: 10,000, current value: 10,099, increment: 1, range: -9,223,372,036,854,780,000 to 9,999,999,999, exhausted: No

This happens because using the current calculation the output is 1.08420107626582E-09 (for better readability is 0.000000001084201) which is lower than the 0.2 configured for @IdentitySequenceRangeExhaustionThresholdRatioand, therefore, it appears on the radar.

Expected behavior
It should not flag this sequence.

The calculation may need a 1-"calculation" < 0.2 where 1-"calculation" is the remaining.

In this example, this would translate into 0.999999998915799 which isn't < 0.2 and therefore wouldn't be flagged.

Current:

WHERE -- less than x% of the maximum sequence range remains
      CASE WHEN increment > 0 THEN (maximum_value - current_value) / (maximum_value - minimum_value)
           WHEN increment < 0 THEN (minimum_value - current_value) / (minimum_value - maximum_value)
      END < @IdentitySequenceRangeExhaustionThresholdRatio

Suggestion:

WHERE -- less than x% of the maximum sequence range remains
      (1- CASE WHEN increment > 0 THEN (maximum_value - current_value) / (maximum_value - minimum_value)
           WHEN increment < 0 THEN (minimum_value - current_value) / (minimum_value - maximum_value)
      END) < @IdentitySequenceRangeExhaustionThresholdRatio

2nd one - The calculation

The current calculation isn't looking to the start_value neither to the increment value the sequence.

Continuing with the previous example, the division is done by subtracting max/min value (maximum_value - minimum_value)

If the sequence starts on the minimum value I would agree, but in our example, it starts on 10000 wouldn't be this one the one to consider as minimum_value? Even if it cycles this is the value that will be used.

That said we should have:
StillAvailable: (maximum_value - start_value - current_value)
divided by
TotalAvailable: (maximum_value - start_value)

And that will be a value of: 0.99999899009899 where the current calculation is 0.999999998915799. This gives a difference of 0.000001008816809 more values that actually won't be available.

However, we are still missing the increment variable.
If it increments by 10000 the number of StillAvailable and TotalAvailable will be 10000 times less.

So putting it all together I would suggest (please anyone that confirm my calculations 😄 )
old:

CASE WHEN increment > 0 THEN (maximum_value - current_value) / (maximum_value - minimum_value)

new:

CASE WHEN increment > 0 THEN ((maximum_value - start_value - current_value) / increment) / ((maximum_value - start_value) / increment)

Final question:
What if is_cycling is true? Should this ever be flagged?

Database info
Single database

Additional context
Maybe it is worth double check the TipID 1510 too.

Use JSON for the details column to make the content easier to be read both by human or machines

We can contribute by adding support to Azure SQL Tips by adding a specific commandlet to the dbatools.io

For "ROW or PAGE compression opportunities may exist":

• It does not seem worth the time to analyze small indexes, or to compress them once analyzed.
• It seems like compressing indexes with a high ratio of writes to reads is likely to cause more CPU overhead for the regular workload than it would be worth.

Adding parameters (@CompressionMinimumPageThreshold and CompressionMinimumReadPercentage for those numbers and filtering on them seem like a good addition that would not significantly slow down the query, as the data is available in sys.partitions and sys.dm_db_index_usage_stats.

Kalen Delaney has such code (although not parameterized) in her stored procedure usp_estimate_data_compression_savings. Her procedure is much more comprehensive and takes much longer to run, but it seems easy enough to add another CTE and a WHERE clause to yours.

In one of my databases, her procedure produces 58 rows of compression recommendations (after 15 minutes of execution time), while yours produces 478 rows of XML output.

That would allow easier consumption for end-users, which would lead to higher usage

What about adding a tip that will return queries with "suspicious" patterns like table or index scans, specific operators, etc?
I had a very raw DMV query i was using to get some of those... we could clean this up a bit and incorporate it, thoughts?

;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
CONVERT(bigint, datepart(yyyy,getdate())*10000000000)+(datepart(mm,getdate())*100000000)+(datepart(dd,getdate())*1000000)+(datepart(hh,getdate())*10000)+(datepart(mi,getdate())*100)+(datepart(ss,getdate())) as timestampKey ,
getdate() as eventdateUTC,
SUBSTRING(t.text,r.statement_start_offset/2+1,(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), t.text)) * 2 else r.statement_end_offset end -r.statement_start_offset)/2) as statement_text,
case when query_plan.exist('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Hash Match"]'
)=1 then 1 else 0 end as hash_match
, case when query_plan.exist('
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Table Scan"]'
)=1 then 1 else 0 end as table_scan
, case when query_plan.exist('
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = "Clustered Index Scan"]'
)=1 then 1 else 0 end as clustered_index_scan,
db_name() as [db_name] , *
FROM
sys.dm_exec_query_stats r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
cross apply sys.dm_exec_query_plan(r.plan_handle) p
WHERE t.text not like '%sys.%'

There are important files that Microsoft projects should all have that are not present in this repository. A pull request has been opened to add the missing file(s). When the pr is merged this issue will be closed automatically.

Microsoft teams can learn more about this effort and share feedback within the open source guidance available internally.

Merge this pull request

Would be great to be able to run something link

az sql db analyze 

We could also return missing indexes with something like (to clean up a lot):

SELECT CONVERT(bigint,datepart(yyyy,getdate())*10000000000)+(datepart(mm,getdate())*100000000)+(datepart(dd,getdate())*1000000)+(datepart(hh,getdate())*10000)+(datepart(mi,getdate())*100)+(datepart(ss,getdate())) [timestampKey]
,getdate() [eventdateUTC],
mig.index_group_handle, mid.index_handle,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.group_handle,
migs.unique_compiles,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.last_user_scan,
migs.avg_total_user_cost,
migs.avg_user_impact,
migs.system_seeks,
migs.system_scans,
migs.last_system_seek,
migs.last_system_scan,
migs.avg_total_system_cost,
migs.avg_system_impact,
db_name(mid.database_id) [db_name],
mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

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:

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

E.g. if some tips take long to execute, could make them conditional behind a parameter.

When I try to run the script I got the following errors:
Msg 102, Level 15, State 1, Line 483
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 662
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 738
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 762
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 834
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 900
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1092
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 1321
Incorrect syntax near 'WITHIN'.
Msg 102, Level 15, State 1, Line 1477
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1648
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 1684
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 2299
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 2641
Incorrect syntax near 'WITHIN'.

In many environments, an admin is not an admin of the whole Azure SQL server and only the DB; or a developer wants to validate the database level items only.

The script as-is fails if any system view is not accessible without allowing us to run checks against a database. Splitting these into two catch blocks and simply show the checks that failed due to permissions will still offer users the ability to see improvements/things to fix instead of not knowing any of them.

The parameter @CCICandidateMinSizeGB, based on its name, is in GB.
When it's used within the WHERE expression, it's divided by 1024 when compared against a value in MB:

      tos.table_size_mb > @CCICandidateMinSizeGB / 1024. -- consider sufficiently large tables only

However, dividing a number that represents GB by 1024 would represent TB, not MB.
In order to be represented in MB, it needs to be MULTIPLIED by 1024. Not divided by 1024.

Like this:

      tos.table_size_mb > @CCICandidateMinSizeGB * 1024. -- consider sufficiently large tables only

https://github.com/microsoft/azure-sql-tips/blob/main/sqldb-tips/get-sqldb-tips.sql#L3431

In many environments, an admin is not an admin of the whole Azure SQL server and only the DB; or a developer wants to validate the database level items only.

The script as-is fails if any system view is not accessible without allowing us to run checks against a database. Splitting these into two catch blocks and simply show the checks that failed due to permissions will still offer users the ability to see improvements/things to fix instead of not knowing any of them.

I ran the tips script on a few databases that I had not even used in some time and this tip showed up for each of them, including a serverless database that was previously paused.

Not sure if the intention of this script is to ALWAYS show the queries that consume most resources or actually show a problem.

In addition, if I wanted to drill into what these results show me it doesn't show query_id from the Query Store which is the default key to look at when looking at Top Resource Consuming Queries report