locked
Database Timing out on Azure RRS feed

  • Question

  • User-2097295820 posted

    I been testing my app for a while. But have been noticing that some functionalities just tend to fail sometimes. Sometimes when I test the same functionalities that fails would later work. When I test on the localhost of my system this functionalities work. So looking deeply I can conclude that this failure happens when there is a database timeout.

     I use entity framework and stored procedures. I am now thinking that I should perform some steps to make my app function better.

    I am running on shard hosting 1 instance. I did this because I want to see how the app would perform on a low grade server. It works well most times. But some times I get to discover some pages wont just show.

    Step 1: increase timeout of running of sql store procedure and EF context.

    Step 2: Trying to activate Logging of errors. I did this but still dont get to see any errors logged.

    Question below:

      <add name="xxxxxxxxxxx" providerName="System.Data.SqlClient" connectionString="xxxxxxxxxxxx;Initial Catalog=xxxxxxxxxxxxx;User ID=xxxxxxxxx; Password=xxxxxxxxxxxxxx" /> 

    This is the connection string below. I want to add Connection Timeout = 60; were exactly should it be for azure. Also How many seconds should I add for a product evironment. I learn that the default is 30 s.

    Question 2:

    I logged into my azure portal and I enable diagonistic tools to log errors. Unfortunately I can tend to see this errors log anywere. Please were is the error file .txt that logs errors stored. I saw information on Trace.WriteLine("hello error here");

    Am suppose to replace my Debug.WriteLine("hello error here"); in my code with Trace.WriteLine.  I added debug in my catch(){ .... } block

    Or how is this used.

    Thanks I am opened to advice

    Monday, July 25, 2016 12:45 PM

All replies

  • User1070236303 posted

    Azure sql server performance is just terrible. Many reported that any new tiers below P0/P1 is just useless for any production database.

    This is one of the most voted feedback in azure user voice, which is "completed". MS just ignore any complaint.

    https://feedback.azure.com/forums/217321-sql-database/suggestions/6848339-please-reconsider-the-new-db-pricing-tiers

    The biggest problem which I found that when a query using 100% DTU, any other query/connection hangs, which makes our sites a single threaded application (!)

    I can easily hit 100% DTU in a 14Mb (yes mega byte) test database making a simple xml select query (on Basic and S0-Sx levels)

    Good luck with sql azure...

    Monday, July 25, 2016 1:07 PM
  • User-2097295820 posted

    Thanks alot, I really appreciate.

    I forgot to mention that my app is running on a database of

    STANDARD

    PERFORMANCE S0 10 DTUs

    Max size : 250 GB

    I am now tinking of upgrading it. My app is a search directory with business packages. Should I just upgrade to premium plan for better performance. I read your link.

    Monday, July 25, 2016 1:45 PM
  • User1070236303 posted

    If you are on a v12 server (based on sql server 2016), you can turn on query store, which may help fine tune your queries. You can upgrade your server to v12.

    Query store: https://msdn.microsoft.com/en-us/library/dn817826.aspx

    The biggest bottleneck for me below P0 is using temporary tables/table variables.

    One of my sp used a table variable to filter out some rows in a query and runs 2min in basic. Once I rid of the table variable the sp finished in 1min(!). The table variable had only 100 rows, db size <15Mb. The same query run in 100Msec on my dev machine with 1Gb database.

    The biggest problem with the DTU performance indicator, that we have no information about the real reserved memory/cpu power for the instances.

    The alternatives are using caching in the web app, using sql server in virtual machines, and finally switch to amazon rds :(

    Tuesday, July 26, 2016 8:51 AM
  • User-2097295820 posted

    Thanks for your response. I think I would want to go with using sql in virtual servers. That would give me the feel of my local system sql server. But are there any turn offs to this approach. what about when there is maintainance on the online servers. If you have any further advice on this approach I would appreciate it.

    Thanks alot for your time.

    Tuesday, July 26, 2016 1:31 PM
  • User1070236303 posted

    I'm not yet used sql in azure vm.

    The main idea of using sql vm is that you get more performance for the same price than the managed sql azure. Another advantage of vm sql is you have full control. Wanted ever to create and download a real backup from sql azure? You can't do this (same with amazon rds).

    With vm sql you need to do all maintanance manually (backups etc.)

    Here is the tutorial: https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-portal-sql-server-provision/

    Anyway if I ever need to move sql vm because of sql azure is poor performed (or too pricey), I won't do that. Instead the best option imho to rent a physical hardware from a hosting company, install iis and sql web edition, and ditch azure completely.

    Wednesday, July 27, 2016 10:28 AM
  • User-2097295820 posted

    Wooow bro, I see you *** hate the Azure stuff. Hmm a friend once migrated to Amazon and I was wondering why he did it. You make the second person giving me this advice.

    Wednesday, July 27, 2016 10:57 AM