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
@SQLeekLabels: Azure, TSQL