CODEWITHSUNDEEP
azureazure-sql-database
Thomas Woelfer
Thomas Woelfer

Reputation: 623

Very simple SQL-Query times out on azure sql database with small table

I have an azure sql db. It's a 'Standard S0 10DTU' instance. i know this is not great for performance, but i don't think what i see can be explained by the 10 DTU only. The db has almost no traffic, as it is only used for logging our nightly build results. When the tests are not running, nobody is using the db, apart from the query that fails.

The query is this "select top(10) * from db-name where id=ID". (ID beeing a variable of type int.) There are only 2500 records in the table. Aprox.5 match.

The first query attempt always times out. (timeout is thirty seconds). the 2nd of 3rd query, when done from the azure-db web-interface in azure, will take from 4-8 seconds. (which is quite long for 2500 records and an "int" compare). When the query is done from my c# app, it will sometimes work after several atempts, but mostly time out.

i have lots of stuff in S0/10DTU databases and i don't have any other problems - only with that specific query in that table.

Any ideas what might be causing this?

Here is the actual code:

using (var connection = new SqlConnection(SelectConnectionForm.ConnectionString))
{
   connection.Open();
   var command = new SqlCommand( "SELECT TOP(10) CAST(severity AS int )AS severity,text FROM [dbo].[ItemReports] WHERE idItem=" +
                  testcase.Id, connection);
   var reader = command.ExecuteReader();

   while (reader.Read())
   {
               var node = (string) reader["text"]);
      // Do Stuff with node    
   }

   reader.Close();
   connection.Close();
}

// "testcase.id" is an "int".

Upvotes: 1

Views: 192

Answers (1)

Thomas Woelfer
Thomas Woelfer

Reputation: 623

So someone somewhere else mentioned a "High water mark problem". I could not find any recent documentation concerning this. However, my table did indeed have a lot of writes and deletes (> 16.000.000, as it were). I assume, this is what 'high water mark' refers to.

What i did was drop and recreate the table and the problem went away.

Upvotes: 1

Related Questions