Is there an easy way to tell what pricing tier my Azure SQL DB/DW is using T-SQL?

3

April 22, 2020 by Kenneth Fisher

I ran into an interesting problem today. I needed to find out the pricing tier of an Azure SQL DB but while I have access to to the DB via SSMS I don’t have access via the Portal. So I needed something I could use via T-SQL. I did some research, and found something that said it worked but didn’t look right to me. So I asked on twitter.

Note: I love #sqlhelp. Joey D’Antoni (blog|twitter) answered me within a couple of minutes. Unfortunately he referenced the same thing I’d already found. Fortunately after looking at it again I found what I’d been doing wrong. First the DMV.

sys.database_service_objectives

The reason I ran into a problem was that I was looking at an Azure SQL DW, which I’m not really used to. So DW400c didn’t mean anything to me. On the other hand when I ran it on a DB I started getting the results I expected. Now, if you run this while connected to your Azure SQL DB you get just the information for the current DB.

On the other hand if you run it in master you get all of the databases (Or just the ones you have access to, I’m not sure and haven’t tested. Although BOL does say all.) Now, it also uses the database_id so since I’m getting back more than just my current DB I tied in sys.databases

SELECT d.name, dso.*
FROM sys.database_service_objectives dso
JOIN sys.databases d
	ON dso.database_id = d.database_id
ORDER BY d.name;

You’ll notice that the service_objective is the pricing tier while edition is the service tier, with the elastic_pool_name being the .. you guessed it .. name of the elastic pool if there is one.

3 thoughts on “Is there an easy way to tell what pricing tier my Azure SQL DB/DW is using T-SQL?

  1. […] Kenneth Fisher needs to know a server’s current pricing tier: […]

  2. Tim @ Hexham Data says:

    As a non-Azure user I read this and thought there was a bit missing. I had to google to see if Azure pricing is per database or per database server. As I’m pretty sure it’s per database that clarifies things for me.

    • Sorry about that. The post is defintely targeted at someone who uses Azure SQL DB and has a specific need to know the pricing tier. And yes, the pricing tier is per DB although I have to admit I’m not sure how that works when you have an elastic pool.

Leave a Reply to Tim @ Hexham Data Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: