Lanencia alta ao fazer conexão com Azure

Question

Boa tarde,

fizemos a migração de um banco de dados para o Azure e temos um problema:

1. Latência de cinco minutos para abrir uma tabela específica do banco de dados SQL Server 16.0.5688 tanto na rede de desenvolvimento quanto na rede de produção. As demais tabelas abrem em tempos próximos a 30 segundos. Em outras redes (fora da infraestrutura local) os tempo de abertura das tabelas é aceitável e em torno de 13 segundos para a mais demorada e de 1 segundo para as demais.

A tabela em questão é pequena, cerca de 79.000 registros porém contém muitas colunas.

A string de conexão usada é:

Provider=MSOLEDBSQL.1;Password="--------";Persist Security Info=True;User ID="--------";Initial Catalog="--------";Data Source="--------".database.windows.net;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Initial File Name="";Use Encryption for Data=True;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=True;Application Intent=READWRITE;MultiSubnetFailover=False;Use FMTONLY=False;Authentication="";Access Token="";TransparentNetworkIPResolution=True;Connect Retry Count=1;Connect Retry Interval=10

Além do MSOLEDBSQL.1 também tentamos o SQLOLEDB.

Agradeço a quem puder ajudar.

Answer 1

Hi @Sergio Giudicelli,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

This forum is supporting English only, please post your question in English, so that we can avoid misunderstanding. Apologized for any inconvenience. 

I understand that you're facing issues with a specific table in your SQL Server database after migrating to Azure. Here are a few steps you can take to troubleshoot and potentially resolve this issue:

1. Ensure that the table in question has appropriate indexes. Given that it has many columns, a lack of proper indexing can significantly slow down access times.
2. Analyze the queries being run on this table. Use SQL Server’s Query Store or Azure SQL Database’s Query Performance Insight to identify any slow-running queries and optimize them.
3. Since the issue is specific to your local infrastructure, check for any network latency or bandwidth issues between your local network and Azure. Tools like Azure Network Watcher can help diagnose network performance issues.
4. Review the database configuration settings. Ensure that the database is configured optimally for your workload. This includes checking the compatibility level, max degree of parallelism, and other performance-related settings.
5. The connection string parameters can also impact performance. For example, setting MultiSubnetFailover=True can help improve failover times in multi-subnet environments. Ensure that the parameters are set correctly for your specific scenario.
6. Verify that the Azure SQL Database tier you are using is appropriate for your workload. Sometimes, upgrading to a higher tier can resolve performance issues.
7. Monitor the resource utilization (CPU, memory, I/O) of your Azure SQL Database. High resource utilization can lead to performance bottlenecks. Azure SQL Database provides built-in monitoring tools to help with this.
8. Regularly perform database maintenance tasks such as updating statistics, rebuilding indexes, and checking for fragmentation. These tasks can help improve query performance.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.