The Azure tier performance varies widely to meet the needs and budget of everyone. But, if you have a database that only needs high performance during a batch, you can scale up and down to bring down the average cost of the database per day.
To get the current level of your database:
Select databasepropertyex(db_name(),‘ServiceObjective’)
To change the level:
Alter Database <Your_DB>
modify (Service_Objective=‘S3’, Edition=‘Standard’)
Edition is one of Basic, Standard, or Premium.
Service_Objetive is S0, S1, S3, P1, P2, P6 etc.
If you would like to wait for the change, you can set up a loop. I have noticed a loss of connection when the level change is complete. This may be to close the logs and complete the change. Just be careful if you are trying to continue within a job. You may have to schedule the increase 1 hour before the batch and then lower the service level at the end of your batch dynamically.
Here is an example of moving from a P1 to S3 with a 5 minute loop:
Alter Database <Your_DB>
modify (Service_Objective=‘S3’, Edition=‘Standard’)
Declare@AzureLevel sql_variant = (select databasepropertyex(db_name(),‘ServiceObjective’));
While@AzureLevel = ‘P1’
Begin
WAITFOR DELAY ’00:05:00′ — Wait 5 minutes
set@AzureLevel = (select databasepropertyex(db_name(),‘ServiceObjective’))
end
Print(‘Now at S3 level’)
If you have a database that only needs a higher service level for a few hours a day, give this a try and it could save you some money each month.
Bob Blackburn
@SQLeek
Related Content
- Tags:
- TSQL
Share on 
