Timeout accesing SQL Azure on Basic/Standard tier - works fine on Web tier

Question

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:

        SqlConnection Conn;
        Conn = new SqlConnection(StrConn);
        Conn.Open();
        try
        {
            SqlDataAdapter Cmd;
            DataSet dtSet;
            DataTable dtTable;


            Cmd = new SqlDataAdapter(_sql, Conn);
            dtSet = new DataSet();
            Cmd.Fill(dtSet);
            dtTable = new DataTable();
            dtTable = dtSet.Tables[0];
            Cmd = null;
            return dtTable;
        }
        finally
        {
            Conn = null;
        }

Connection strings are unchanged.

I can access the basic SQL database fine from Visual Studio.

Any suggestions?

Answer 1

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:

1. Increase the command timeout if you can tolerate to wait for the result a little longer
2. 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.

Jan