jocapc / qpi Goto Github PK

"nvarhar" rather than "nvarchar" is in the SQL code for the CREATE OR ALTER VIEW qpi.recommendations for Serverless SQL Pools. This causes the view to not return any recommendations if the View columns are nvarchar.

Isn't there a typo in snippet prefix in your extension ?

There is the snippet with prefix "qpi:snapshot wait stats" calling "exec qpi.snapshot_wait_stats;" and second snippet with the same prefix "qpi:snapshot wait stats" calling "exec qpi.snapshot_perf_counters;".

I think the second prefix should be something like "qpi:snapshot perf counters".

Column names in each view or function must be unique. Column name 'resourceGroup' in view or function 'diagnostics' is specified more than once.

error in line 387
CREATE OR ALTER VIEW qpi.diagnostics ...

file:
https://raw.githubusercontent.com/JocaPC/qpi/master/build/synapse-sql/qpi.sql

suggested fix, edit 408 by adding details.resourceGroup as resourceGroupDetails,

I'm attempting to use the Azure SQL (non-managed) script and I'm getting an error:

Invalid object name 'sys.master_files'.

This is not supported in Azure SQL - is there a workaround available?

I see the code uses GETDATE. Would it not be better to use GETUTCDATE instead ? That way you are safe from daylight saving time issues.

I'm a SQL newbie, so I hope it's not my mistake. I'm on Azure SQL Managed Instance.

1> EXEC qpi.snapshot_file_stats;
2> go
Msg 8115, Level 16, State 2, Server mssqlmi-stg.c3890485fa89.database.windows.net, Procedure qpi.snapshot_file_stats, Line 5
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.

Regards

Hi there,

just realized there is a small errror in the view qpi.volumes, used_gb should be total_gb and total_gb should be used_gb while substracting available_bytes from total_bytes.

CREATE VIEW qpi.volumes
AS
SELECT volume_mount_point,
total_gb = CAST(MIN(total_bytes / 1024. / 1024 / 1024) AS NUMERIC(10,1)),
available_gb = CAST(MIN(available_bytes / 1024. / 1024 / 1024) AS NUMERIC(10,1)),
used_gb = CAST(MIN((total_bytes-available_bytes) / 1024. / 1024 / 1024) AS NUMERIC(10,1))
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
GROUP BY volume_mount_point;
GO