Hello,

we use the Azure website and database for our Android app. The app makes requests to the Azure website and gets an answer. (The Website consists of PHP files which executes queries on the database.) The problem that I get is an error in the PHP error log of the website ("PHP Fatal error: Maximum execution time of 300 seconds exceeded in D:\ on line ). And we use a continously webjob (this inserts data in the database) which is running slowlier, too.

The problem only happens at night. It is always starting ca. 22:48 UTC and disappearing sometimes 8:30 UTC, sometimes 11:23 UTC, 7:00 UTC.

Why is the database running slow at this time?

The website and the database are in West Europe.

This problem happened already a few times earlier (with another DB): https://social.msdn.microsoft.com/Forums/azure/en-US/eee66382-40d2-4d27-9cda-218c39650f36/azure-database-at-times-slow

Thank you in advance

Hi,

Please have a look at this discussion about Azure SQL Database performance issue.

https://social.msdn.microsoft.com/Forums/en-US/a99c7ed9-d7aa-4ab2-802d-dd6f7360ea4e/east-us-database-very-slow?forum=ssdsgetstarted

Using what method are you transferring data to / from SQL Azure ?

Since you have a big database, I would suggest you to use SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/) for initial data transfer and for subsequent transfer use SQL Azure Data Sync Service, which will identify the changes and synchronize the changes automatically on specified schedules, so that you don't have to transfer the whole data.

Other downside of downloading whole data every day is you will be charged for that too.

To know more about SQL Azure Data Sync Service,

http://sqlxpertise.com/2011/06/06/sql-azure-sync-ctp2-how-to-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versa/

http://sqlxpertise.com/2011/06/07/sql-azure-sync-ctp2-how-to-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versacreate-sync-groups-and-schedules-part-2/

http://sqlxpertise.com/2011/06/08/sql-azure-sync-ctp2-synchronize-sql-azure-with-on-premise-sql-server-and-vice-versa-how-sync-framework-tracks-changes-in-databases-part-3/

Also test you internet connection speed when you face the issue using http://www.speakeasy.net/speedtest/ or whichever speed test site preferred by you, so you can verify the bandwidth and confirm whether the issue is in your end or in Azure end

Hope this helps.

Girish Prajwal

We don't transfer data from us to the Azure database. The Webjob which is executed is fetching data from somewhere as XML files and inserting into the Azure database (and deleting old data). This runs continuously because wet get data continuously. So everything which happens uses the Azure website and databases. But the SQL Azure Migration Wizard is for transferring data between databases.

• Edited by S. Pamskmalt Monday, January 26, 2015 1:44 PM typo

We don't transfer data from us to the Azure database. The Webjob which is executed is fetching data from somewhere as XML files and inserting into the Azure database (and deleting old data). This runs continuously because wet get data continuously. So everything which happens uses the Azure website and databases. But the SQL Azure Migration Wizard is for transferring data between databases.

• Edited by S. Pamskmalt Monday, January 26, 2015 1:44 PM typo

We don't transfer data from us to the Azure database. The Webjob which is executed is fetching data from somewhere as XML files and inserting into the Azure database (and deleting old data). This runs continuously because wet get data continuously. So everything which happens uses the Azure website and databases. But the SQL Azure Migration Wizard is for transferring data between databases.

• Edited by S. Pamskmalt Monday, January 26, 2015 1:44 PM typo

We don't transfer data from us to the Azure database. The Webjob which is executed is fetching data from somewhere as XML files and inserting into the Azure database (and deleting old data). This runs continuously because wet get data continuously. So everything which happens uses the Azure website and databases. But the SQL Azure Migration Wizard is for transferring data between databases.

• Edited by S. Pamskmalt Monday, January 26, 2015 1:44 PM typo

Did you check SQL logs for any errors?

Below error message shows that the queries are being timed out, can you check on the SQL Server for the remote query execution timeout, and what's it set to? You may need to increase that if it's set to 300s.

("PHP Fatal error: Maximum execution time of 300 seconds exceeded in D:\ on line ).

Hi,

Sounds like you might need to work on the queries/DB performance. Start by checking if all the services are in the same geographic region (that helps quite a bit). Then, use this query to check which are the most CPU intensive queries on your DB:

SELECT TOP 10 
total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
ORDER BY Avg_CPU_Time DESC

Once you found them, try to optimize them. You could also check for Missing Indexes:

SELECT CONVERT (varchar, getdate(), 126) AS runtime, 
       mig.index_group_handle, 
       mid.index_handle, 
       CONVERT (decimal (28,1), 
        migs.avg_total_user_cost * 
        migs.avg_user_impact * 
        (migs.user_seeks + migs.user_scans)) 
        AS improvement_measure, 
       'CREATE INDEX missing_index_' + 
       CONVERT (varchar, mig.index_group_handle) + 
       '_' + 
       CONVERT (varchar, mid.index_handle) + 
       ' ON ' + 
       mid.statement + 
       ' (' + ISNULL (mid.equality_columns,'') + 
       CASE WHEN mid.equality_columns IS NOT NULL 
            AND mid.inequality_columns IS NOT NULL 
        THEN ',' 
        ELSE '' 
        END + ISNULL (mid.inequality_columns, '') + 
        ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')',
                '') AS create_index_statement, 
        migs.*, 
    mid.database_id, mid.[object_id]
    FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs 
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid 
        ON mig.index_handle = mid.index_handle
    WHERE CONVERT (decimal (28,1), 
                   migs.avg_total_user_cost * 
               migs.avg_user_impact * 
              (migs.user_seeks + migs.user_scans)) > 10
    ORDER BY migs.avg_total_user_cost * 
             migs.avg_user_impact * 
         (migs.user_seeks + migs.user_scans) DESC

Also, SQL Sentry Plan Explorer (http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view)is a great free program to find improvement spots.

Most of the time, TimeOuts are due to long running queries, instead of just extending the timeout time, my advice would be to find and fix the root performance problems :)

Good luck!

Are you running the old Web or Business edition? If so, someone else may be running a scheduled job which runs from 22:48 to the following day.

"Azure Web and Business SQL databases run in a shared, multi-tenant environment without any reserved resource capacity for the database. The activity of other databases in your cluster can impact your performance. Resource availability at any given point depends heavily on other concurrent workloads running in the system. This can result in highly varying and unpredictable database application performance."