A couple of days ago, during the great SQL Unplugged live show, I have asked a couple of questions the leadership trio consisting of Shawn Bice, Nigel Ellis, and Rohan Kumar and was lucky enough to have them answered.

The last answer came during the last seconds of the show when Nigel Ellis has mentioned that Azure SQLDatabase Premium Editions are not limited to the 1 core. He actually explained that ultimately we as SQLDatabase “consumers” should not care about the number of cores or anything else Hardware-related, and it make a lot of sense in a perfect world.

The thing is that the world we are living is not perfect, and performance tuning is one of the most requested jobs in the database world, where any detail can lead to better understanding of the System and ultimately to the better performance.

I decided to confirm the information shared by Nigel in the show and went checking on the different premium editions of Azure SQLDatabase.

I confess that my most “expensive” experiences with Azure SQLDatabase were limited with P1 Edition of the Premium and I have never touched P2 or P3 editions.

Checking on the available cores is easy in SQL Server – it is enough to count the available Schedulers which are online. This can be done through the DMV sys.dm_os_schedulers by running the following query:

select *
   from sys.dm_os_schedulers
   where status = 'VISIBLE ONLINE' and is_online = 1

I decided to create a new Premium Database on Azure SQLDatabase and simply issue this query on different editions, analysing the results.

For creating a test database on an existing Azure SQLDatabase server you will need to execute a very simple command:

CREATE DATABASE Test
(
  EDITION = 'Premium',
  SERVICE_OBJECTIVE = 'P1'
);

Notice, that after the process is finished you will need to establish connection to your database directly, you cannot use the command USE TEST; in your current connection to make the switch to your database.

I made the necessary switch by changing my existing window connection in SSMS and here I was running the test code in my P1 Edition of the Azure SQLDatabase:

select *
   from sys.dm_os_schedulers
   where status = 'VISIBLE ONLINE' and is_online = 1

This edition as expected and previously tested has only 1 core, as any other non-premium editions – which is what I was expecting.

I wanted to see if the numbers mentioned by Nigel were correct and so I opened the properties window of my Test database and changed the edition from P1 to P2. After clicking on OK and waiting for some seconds I was ready to test my P2 Database.

Funny note: at the properties window the property is called “Current Service Level Objective”. Objective. Goal. Expectation. Not a guarantee.

And so I ran the test code on the P2 edition and here are my results:
Just like Nigel mentioned for P2 – there are 2 cores (schedulers) available, which is really great and definitely need for providing all those DTUs that Microsoft is always talking about.

Going an extra step further and checking on P3, as Nigel mentioned there should be 8 cores – I ran my test query:

select *
   from sys.dm_os_schedulers
   where status = 'VISIBLE ONLINE' and is_online = 1

Amazing – as responded in the show: here we are with 8 cores all available for work.

The final table with the core numbers for the current 19.03.2015 Azure SQLDatabase situation is: