none
Improve performance of SQL databases in Elastic pool RRS feed

  • Question

  • I have this situation: http://stackoverflow.com/questions/41762046/performance-azure-elastic-pool-databases

    And I'm looking in improving the performance of our application. We think it has to do with adding data to the Elastic SQL Shard database.

    I'm looking if upgrading our pricing tier will solve (some) of the issues.
    When I look at the 'Database Resource Utilization' chart I see some spikes of 60-80% for eDTU and CPU. 
    The database which is causing the spikes is 260MB.

    We now use 'Basic'. If I understand it correctly moving to S0/S1 will give us more eDTU's and CPU. I'm a correct? Will this improve our performance?
    We only have spikes sometimes, most of the time the databases are doing nothing.

    In our workflow we start with creating a shard database in C# code. Next we fill this database with a lot of data.
    Can we configure to only increase the new database with a higher pricing tier and decrease it again after some time (for example a month)?

    If we can only change the tier of the whole Elastic pool, can we easily automate the tier to be increased during working hours and decrease during the night?

    Thanks,

    Paul

    Friday, January 20, 2017 12:28 PM

Answers

  • A Standard pool provides more performance than a Basic pool both in terms of higher DTU limits of the pool itself as well as higher DTU limits for the databases within the pool.  Importantly, the DTU max per database in a Basic pool is limited to 5 DTUs whereas the DTU max per database in a Standard pool can range anywhere between 10 DTUs and 100 DTUs (and even higher for Premium pools).

    You can create a new Standard pool and then move databases into it from the Basic pool.  The following link explains how to move databases into another pool: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool-manage-powershell (There is drop down menu in the link to documentation for doing this via other APIs such as T-SQL or via the portal).

    There is a SQL DB update planned that will allow a Basic pool to be resized directly to a Standard pool (and vice versa and to/from Premium pools too) in order to avoid the extra step of moving databases individually between pools.  Note that resizing the DTUs for pool is already possible today if the service tier (Basic, Standard, or Premium) of the pool doesn't change. For example, a 100 DTU Standard pool can be resized to a 200 DTU Standard pool and vice versa.  To see how to do this, see the section "Change performance settings a pool" in the same link provided above.   

    Friday, January 20, 2017 6:53 PM

All replies

  • A Standard pool provides more performance than a Basic pool both in terms of higher DTU limits of the pool itself as well as higher DTU limits for the databases within the pool.  Importantly, the DTU max per database in a Basic pool is limited to 5 DTUs whereas the DTU max per database in a Standard pool can range anywhere between 10 DTUs and 100 DTUs (and even higher for Premium pools).

    You can create a new Standard pool and then move databases into it from the Basic pool.  The following link explains how to move databases into another pool: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool-manage-powershell (There is drop down menu in the link to documentation for doing this via other APIs such as T-SQL or via the portal).

    There is a SQL DB update planned that will allow a Basic pool to be resized directly to a Standard pool (and vice versa and to/from Premium pools too) in order to avoid the extra step of moving databases individually between pools.  Note that resizing the DTUs for pool is already possible today if the service tier (Basic, Standard, or Premium) of the pool doesn't change. For example, a 100 DTU Standard pool can be resized to a 200 DTU Standard pool and vice versa.  To see how to do this, see the section "Change performance settings a pool" in the same link provided above.   

    Friday, January 20, 2017 6:53 PM
  • There is a SQL DB update planned that will allow a Basic pool to be resized directly to a Standard pool (and vice versa and to/from Premium pools too) in order to avoid the extra step of moving databases individually between pools.

    Excellent, it's been giving me a headache to copy my P4 down to an S3 every day.

    Josh

    Friday, January 20, 2017 8:22 PM
  • Thanks MorganO for your answer.

    Will the SQL DB update also make it possible to resize it automated on hours using the Portal?
    Increase on a fixed time of the day and decrease on a different time of the day/week?

    Or how do we automate this? Are their any examples?

    Saturday, January 21, 2017 3:12 PM
  • Will the SQL DB update also make it possible to resize it automated on hours using the Portal?
    Increase on a fixed time of the day and decrease on a different time of the day/week?

    Or how do we automate this? Are their any examples?

    I ended up writing a little C# console app to copy my production database from P4 to P1, and then change the service level from P1 to S3, and I run it as a webjob.  It's easy enough to change the service level within a class, but it is asynchronous, you make the call and it returns right away but it doesn't actually complete for five or ten minutes.  And you need to be sure that your application doesn't "glitch" when this happens, as it involves killing all connections to the old level before redirecting them to the new.

    Now, I'm running standard service, not an elastic group, perhaps things are different in an elastic group.

    One more thing that would help is if we had the equivalent of SQL Agent, where you could easily schedule and run a TSQL statement directly.  Rumor has it this will finally be implemented for Azure this year.

    Josh

    Saturday, January 21, 2017 6:00 PM
  • We've just moved our databases from a Basic pool to a Standard pool and our application is doing its work in half the time now! Making my customer very happy.
    Thursday, January 26, 2017 12:21 PM