How to query SLO of "serverless databases" using T-SQL?

Question

I have what I perceive as a "database" and can view it in SSMS. I can also see its SLO in SSMS.
It is serverless.

When I query SLO using T-SQL as below
SELECT d.name,
slo.*
FROM sys.databases d
JOIN sys.database_service_objectives slo
ON d.database_id = slo.database_id;

I DO NOT see serverless databases listed in the output grid.

1. SSMS has my serverless database under the Databases node. But sys.databases does not contain it. I must be querying the wrong table?
2. What is the T-SQL query to show the SLO of serverless database as displayed in SSMS as seen in snapshot below?

Thank you very much!

Answer 1

If you execute the following query:

SELECT slo_name FROM sys.dm_user_db_resource_governance  

You will get:

On the results, the prefix "SQLDB_GP_S_GEN5" means Azure SQL Database General Purpose (GP) Serverless (S), Gen 5. See other columns of the sys.dm_user_db_resource_governance for more details about the database.

If I use your query on the context of your serverless database like:

SELECT  d.name,     
     slo.*      
FROM sys.databases d     
JOIN sys.database_service_objectives slo      
ON d.database_id = slo.database_id;  

You get almost the same:

That "S" on the service objective column "GP_S_Gen5_1" means Serverless.

Please upgrade the SQL Server Management Studio to the latest version for a better Azure user experience.

Answer 2

Alberto,
Thank you very much for the diligent reply. I believe the query. However there is something in my environment or settings that suppress the serverless databases.
I have 4 databases per SSMS database node:

Yet the t-sql query only shows 1 and suppresses the serverless databases.

Answer 3

Alberto,
Thank you for your guidance. I see the serverless databases now with the SSMS upgrade.