We are talking minutes+ on databases with many objects (30,000+ in sys.objects) when SQL Server Management Studio is "Not Responding" due to autocompletion - when SQL Assistant is trying to match what you have started to type.

This happens when the name matching method is set to "Name Contains Characters from Key String, Order by Best Match" (DB Options -> SQL Server -> Auto Complete...). It works OK if I change it to "Name Contains Characters from Key String, Order Alphabetically", the ordering makes all the difference here. But "Best Match" has it uses when you start to adapt and type keywords instead of names so it is a nice feature but not possible to use on our production databases.

Thank you very much for letting us know. Can you please confirm that you are observing this issue only when the name matching method is set to "Name Contains Characters from Key String, Order by Best Match" value?

Yes, as far as I can tell it is the "...Order by Best Match..." setting that is the culprit here. Switching to "..., Order Alphabetically" gives you an almost immediate autocomplete response while "Best Match" can take 30-60 seconds without any response whatsoever, SSMS flags the window as "Not Responding".

It stop directly when trying to find objects. "SELECT * FROM " and then stops when typing the space after "FROM".

Automatic syntax checking and performance analysis are already turned off - but thanks for the tip!

[This is on a hexacore Core i7-3930K 4.2GHz which is pretty snappy at other tasks, 16GB RAM]

Fri Sep 29, 2017 7:38 am

SysOpSite Admin

Joined: 26 Nov 2006Posts: 6750

Thank you for confirming this. I will need to open a ticket and have the development look into this. It sounds like a case of non-optimized code.