locked
performance gains of using azure sql database vs standard sql server 2012 database RRS feed

  • Question

  • Is there any performance gains of using azure sql database vs standard sql server 2012 database?

    We use stored procedures and have multiple tables and we run etl jobs and also bring this data in sharepoint 2013.

    Is there any real performance gains? I mean really real in terms of seconds reduced in each stored procedure(from 15 seconds to 3 seconds etc..)

    http://siliconangle.com/blog/2016/11/11/microsoft-soups-up-azure-sql-with-new-in-memory-capabilities/


    MCTS Sharepoint 2010, MCAD dotnet, MCPDEA, SharePoint Lead


    Tuesday, December 20, 2016 12:12 AM

All replies

  • It depends on the service tier you select. For a list of service tier's, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers

    The main benefit of the Azure SQL DB's is that you do not have to manage the hardware as it is database as service. It reduces the amount of administration work as you do not have to manage the VM, or OS, upgrades etc. 
    Tuesday, December 20, 2016 12:18 AM
  • Yes - with SQL DB you now have access to Columnstore indexes and In-Memory technology which you don't get in SQL Server 2012 Std Edition.

    If you are able to create your stored procedures as native compiled with In-Memory tables then these should be quicker.  How much quicker ... depends on your specific database.


    Martin Cairney SQL Server MVP

    Tuesday, December 20, 2016 2:18 AM
  • Thanks Nick. We are not looking for these benefits. What I specifically want is whether using azure sql significantly increases page performance/query time in different stored procs(premium tier)

    https://azure.microsoft.com/en-us/blog/azure-sql-database-in-memory-performance/


    MCTS Sharepoint 2010, MCAD dotnet, MCPDEA, SharePoint Lead


    Tuesday, December 20, 2016 2:18 AM
  • If we upgrade to sql server 2016 from sql 2012 and use in memory tables will it improve our stored procs performance significantly?

    MCTS Sharepoint 2010, MCAD dotnet, MCPDEA, SharePoint Lead

    Tuesday, December 20, 2016 3:09 AM
  • Hello,

    "Performance tasks like increasing maximum database sizes is literally 2-3 mouse-clicks instead of months of planning and provisioning, and scaling up for as much extra load as you can imagine is just a case of selecting the right performance level for your database & saving the change. "

    Source: https://blogs.msdn.microsoft.com/sambetts/2015/04/10/hosting-sharepoint-content-databases-in-sql-azure/

    The following tests may be useful.

    https://cbailiss.wordpress.com/2015/01/31/azure-sql-database-v12-ga-performance-inc-cpu-benchmaring/  



    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, December 20, 2016 3:17 AM
  • Hello,

    "Order processing benchmark (scale factor 100, with 400 clients) on P15 (the highest tier at the time of writing): 75,000 transactions per second (TPS) with In-Memory OLTP, compared with 6,800 TPS with traditional tables and stored procedures, which translates to 11X performance gain with In-Memory OLTP"

    Source: https://azure.microsoft.com/en-us/blog/azure-sql-database-in-memory-performance/

    "The addition of In-Memory OLTP tables and native-compiled stored procedures on Azure SQL Database for a few key operations immediately reduced our overall DTU consumption by seventy percent."

    Source: https://azure.microsoft.com/en-us/blog/azure-sql-database-in-memory-performance/



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, December 20, 2016 3:34 AM
  • It depends on the service tier you select. For a list of service tier's, see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers

    The main benefit of the Azure SQL DB's is that you do not have to manage the hardware as it is database as service. It reduces the amount of administration work as you do not have to manage the VM, or OS, upgrades etc. 

    As Nick says.

    As to the P15, take a look at the price there, in making your decision.

    And a rewrite to use the in-memory tables is much more work than you might think.

    For raw performance your best bet is still (a) spending on hardware, and (b) good tuning.   Not necessarily in that order.

    Out of curiosity what do you have now, in RAM, in cores, in SAN/SSD, in gigabytes of data.

    Josh

    ps - there are further issues if you need to move massive amounts of data into and out of Azure as ETL.
    • Edited by JRStern Tuesday, December 20, 2016 4:29 AM
    Tuesday, December 20, 2016 4:27 AM
  • Thanks JRStern, Alberto.

    We have sharepoint 2013 and sql server 2012, currently all pages load in almost 10+ seconds and we are looking for incredible performance for one of our apps and with heavy OOB js in sharepoint its not possible to have very fast loading customized pages with web parts that brings data from sp list and SQL stored proc.

    I was trying to see if in memory will reduce sql time but as you said if there is a big change needed we can not take that route. Trying to see how to get incredible performance in client facing apps based on either sharepoint or something else..

    The expectation is all pages should load in less than 3 sec and just one splist.getitems sometimes take 2 seconds.


    MCTS Sharepoint 2010, MCAD dotnet, MCPDEA, SharePoint Lead


    Tuesday, December 20, 2016 11:17 AM
  • I've never dealt much with sharepoint at the database level, but I see a lot of discussion about it, that it specifies a certain database configuration and you aren't allowed to really try to tune it up.

    It may not be the best environment from what I read, but it is what it is and I guess you have to be extra brave to start fiddling with it.

    That is slightly encouraging, it does mean that conventional tuning might take you a long way, if it is allowed.  

    What is the status of sharepoint on Azure?  Have to ask about that someplace.

    Josh

    Wednesday, December 21, 2016 1:09 AM
  • Amit

    Feel your pain :-)

    http://dimantdatabasesolutions.blogspot.co.il/2008/06/how-do-you-maintain-sharepoint.html


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 21, 2016 6:02 AM
  • I am going to start exploring in memory tables in sql server 2016 and compare my results with current SQL 2012 stored procs, hope all goes well. It worked for me in azure sql premium tier but I need to do extensive testing with sql 2016 on premise to make sure perf. is improved.



    MCTS Sharepoint 2010, MCAD dotnet, MCPDEA, SharePoint Lead



    Wednesday, December 21, 2016 1:45 PM