1

I have a query that is intermittently running very slowly in SQL Azure

  • Runs in < 1 second in SSMS
  • Is submitted through Entity Framework through the application
  • Is NOT calling a stored procedure
  • Is passed parameters
  • Using DMVs determined that it runs and then enters a "Suspended" state
  • The wait type is indicated as IO_COMPLETION pointing towards waiting for a resource
  • This is in Azure so the normal server related monitoring is not available
  • Execution plan has been reviewed with no scans indicated
  • Issue will be occurring one day...all day, then with no changes to the database be cleared up the next morning
  • Once the issue was occurring then I couldn't login to the database for several seconds. Once I could the issue was corrected and the query performed as expected.

Any ideas on what could be causing this or other things that can be checked would be greatly appreciated!

Thanks in advance!

Jim
  • 11
  • 1
  • is your Azure DB a Web/Business edition or the new service tier? If new service tier - which level - Basic, Standard, Premium? What happens if you scale to a bigger service tier? – astaykov Jan 13 '15 at 22:44
  • I'd recommend putting some logging / telemetry around the EF calls you are making. Stopwatch before / after call and log the parameters being passed to EF to see if a common set of parameters is causing Azure SQL DB to slow down. Also maybe log the SQL that was fired at the DB. See: http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework – Simon W Jan 13 '15 at 23:15
  • The Azure DB is the Web Edition. Have not tried the higher service tier yet. Will look into that. Thanks! – Jim Jan 14 '15 at 15:58

0 Answers0