1

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?

BrianAtkins
  • 1,279
  • 9
  • 17
Mr M
  • 13
  • 4
  • The connection string is in the format 'Data Source=xx; Network Library=DBMSSOCN; Initial Catalog=xx; UID=xx; pwd=xx'. The timeout is on the Fill command – Mr M Jun 04 '15 at 19:12

1 Answers1

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

Jan Engelsberg
  • 1,067
  • 6
  • 8