-1

I tried to use Azure SQL Database with Tableau for visualizing data. First issue was that Tableau takes a huge time on connecting to db on start (about 10-30 minutes). After connecting it works fast. I provided some investigations and determined that the same situation happens when I try to expand tables list of database in Sql Server Management studio and then I run SELECT * FROM INFORMATION_SCHEMA.TABLES and have execution time ~10 min. Count of tables in db = 63. Tests was performed at single connection to db, so there was'nt any processes that can lock server and all another queries works fine. Any ideas what exactly can affect so bad to performance?

Artem Kurianov
  • 64
  • 8
  • See which performance Tier your azure sql database. It could be very less. – Venkataraman R Jun 27 '19 at 08:17
  • It is S1(20 DTU) instance and this tier is enough for me to run analytic queries on more than 5M rows, but it is not enough for simple reception of the system information? – Artem Kurianov Jun 27 '19 at 08:21
  • you can monitor the database for both queries and see what is happening. https://learn.microsoft.com/en-us/azure/sql-database/sql-database-monitor-tune-overview – Venkataraman R Jun 27 '19 at 08:26
  • sure, firs of all I took a look to resources consumption but it was normal, maximum is ~40-50%. All long running queries is my really heavy analytic selects and only one of this long by duration queries was giant query that try to get info about db from sys views. But that query does not take affect to CPU, data and log IO. It tooks only long time for executing – Artem Kurianov Jun 27 '19 at 08:49
  • Suggest you post the query plans/output from set statistics profile on. The information schema views tend to have some overhead to convert from the SQL Server system tables into the format in those views. You may need a larger reservation size to make it compile and execute fast enough for your application to be effective for you – Conor Cunningham MSFT Jun 27 '19 at 15:58
  • Do you get the same experience when using `SELECT * FROM sys.tables` or `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES`? – Lukasz Szozda Jun 29 '19 at 19:26
  • @LukaszSzozda, yes. And next day after copying db to new instance I have the same problem – Artem Kurianov Jul 02 '19 at 09:20

1 Answers1

0

There could be many reasons for slowness:

  • Your Azure SQL Database could be in lower performance tier
  • You might be located very far from your Azure SQL data center. Try to move the Azure SQL to a data center closer to your location

Please refer to link for any issues related to network latency: https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/10/02/how-to-troubleshoot-slow-performance-after-moving-to-azure-due-to-network-latency/

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • I have about 50-70 ms latency to my db instance. Also I have another db on the same Azure SQL Server with 75 tables and selecting tables list from INFORMATION_SCHEMA at that db took less then 1s. Also all another queries on user tables in db with troubles at INFORMATION_SCHEMA have a very nice performance – Artem Kurianov Jun 27 '19 at 08:26