none
Slow SSMS for SQL Azure RRS feed

  • Question

  • We have experienced extreme slow responses when editing tables in SSMS table designer. We have seen this on multiple machines and configurations so it seems to be a bug in SSMS.

    To reproduce it do the following:

    1. Connect to a SQL Azure v12 Database in SSMS provider 13.0.15900.1
    2. Edit a table in the designer
    3. Type a new column table name then select a different type.

    The performance of even typing a column name is extremely slow (any where between 5-10 seconds after every key press) and probably longer when hitting the drop down for the data type.

    We do not see this issue when editing other databases on other non-azure sql servers. 

    Friday, October 14, 2016 3:05 AM

Answers

  • The work around we found is to actually close the properties window on the side of the design view and it seems to resolve the issue. It makes no sense why this solves it, but it works. 
    • Marked as answer by jmarbutt Tuesday, May 23, 2017 7:37 PM
    Tuesday, May 23, 2017 7:37 PM

All replies

  • Which service tier and performance level is your SQL DB? When you are reproducing the issue, can you see anything in the Monitoring section in Overview in your SQL Database in portal.azure.com?
    Monday, October 17, 2016 12:26 AM
  • We have tried it on a basic database and a s1 and seen the same issues. The portal does show the dtu peg out. But ssms is not even usable on basic and not any different on the s1.
    Monday, October 17, 2016 4:15 PM
  • When the designer is open, SSMS is constantly firing off these 2 queries:

    SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition
    DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

    Looks like a similar issue that was reported here:

    https://connect.microsoft.com/SQLServer/feedback/details/708296/ssms-2012-generates-stray-queries-on-single-keypress

    Note that the first comment in that thread indicates an identical statement to one of the above.

    Tuesday, November 1, 2016 2:03 AM
  • Well, offhand I know this: (1) Many SSMS functions are very slow when connected to Azure, (2) SSMS is often firing off a lot of expensive queries to the connected database, as anyone can see when you happen to have profiler running without a good filter, and (3) This is apparently why Microsoft has completely disabled Intellisense in SSMS when connected to Azure.

    A bit painful that this is all still happening in the newest versions of Azure and SSMS, I'm just using Azure - and SQL 2016 for that matter - for the first time, and had hoped that rough edges like this would have been fixed long since.  I guess it does call for Microsoft to (finally) fix a lot of stuff that has been pretty crude for a long time.  With all the fuss about Azure, you'd think it would have some priority, too.

    Josh

    Tuesday, November 1, 2016 6:05 AM
  • We have gone round and round with these issues, SSMS has basically become useless to us. The details can be found on this connect:

    https://connect.microsoft.com/SQLServer/feedback/details/3109934/slow-ssms-for-sql-azure

    But the biggest part we tracked down is when you are in any of the designers it calls these 2 queries thousands of times:

    SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition
    DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

    https://connect.microsoft.com/SQLServer/feedback/details/708296/ssms-2012-generates-stray-queries-on-single-keypress

    Note that the first comment in that thread indicates an identical statement to one of the above.

    Wednesday, February 15, 2017 5:21 PM
  • Hello,

    Please provide me a way to contact you. I will forward this thread to the SSMS team.

    Thank you for referring to the Microsoft Connect item and thank you in advance for providing a way to contact you.



    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, February 15, 2017 5:29 PM
  • Alberto I have emailed you
    Wednesday, February 15, 2017 5:55 PM
  • Hello Jonathan,

    I just shared this thread and your findings with the SSMS team and SQL Azure team.

    Please note they are always involved in projects and deadlines and allow them some time to work with this issue.

    Thank you for reporting this issue and thank you for visting Azure forums!



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, February 15, 2017 7:19 PM
  • I'm investigating a solution for this. In the short term, you can stop the endless engine edition queries by closing the Properties window. Every time you change something in the designer that window refreshes, and due to how Azure connections are handled we issue that edition query to make sure it goes to the right place.


    This posting is provided "as is" and confers no rights.

    Thursday, February 16, 2017 10:47 PM
  • Well obviously that stops the process but doesn't solve the issue. We are aware that we can do that to stop it. But we would like to be able to use the properties windows without issues. 

    Friday, February 17, 2017 1:15 AM
  • We have the same issues.

    We based in South Africa, and if I connect to an azure sql server with sms its basically unable in the editor.
    Tuesday, May 23, 2017 7:29 PM
  • The work around we found is to actually close the properties window on the side of the design view and it seems to resolve the issue. It makes no sense why this solves it, but it works. 
    • Marked as answer by jmarbutt Tuesday, May 23, 2017 7:37 PM
    Tuesday, May 23, 2017 7:37 PM
  • It worked for me, closing the properties window, and the designer performance is good.
    Tuesday, August 7, 2018 12:21 PM
  • I don't even have my properties window open :-(
    Thursday, November 14, 2019 7:43 PM