I am upgrading a legacy azure site to use one of the current SQL azure tiers from the old web tier.
The site works fine on the website and locally but when I switch from web to basic or standard I get timeout errors.
The code that is timing out is legacy:
Conn = new SqlConnection(StrConn);
Cmd = new SqlDataAdapter(_sql, Conn);
dtSet = new DataSet();
dtTable = new DataTable();
dtTable = dtSet.Tables;
Cmd = null;
Conn = null;
Connection strings are unchanged.
I can access the basic SQL database fine from Visual Studio.
Best How To :
The Basic and Standard service tiers have different performance characteristics than the Web service tier. Specifically in Web you can get up to Premium P2 performance if the machine that the DB is hosted on is not busy.
When you run the above code connect to your database and query the view sys.dm_db_resouce_stats to see if you max out the performance available to your database.
To work around this you can either:
- Increase the command timeout if you can tolerate to wait for the result a little longer
- Scale up to a higher performance level which gives you more resources so that the query returns faster
Also you should check if you can optimize the query or schema to reduce the execution time. For example make sure you have appropriate indexes in place. This article has performance tuning guidance to get your started.