Just another day, I received an email from a user who has over 350 thousand tables in a single database. He was my earlier customer of Comprehensive Database Performance Health Check. He was facing indeed a very strange issue where his metadata table was locking up and we fixed that by disabling IntelliSense in SQL Server Management Studio.
Due to the nature of the business, my client had over 350,000 tables in a single database. Every single time he opened SQL Server Management Studio (SSMS), IntelliSense had to query every single table’s metadata. This actually led to a poor performance for the entire application as well as lots of locks in the database. After carefully investigating we realized that the query which is running every time when SSMS is opened is actually running by Intelli Sense. We immediately proceeded to disable IntelliSense, which fixed our performance problems. Let us see how you can turn off IntelliSense for SSMS.
Open SSMS – Click on Tools, Options, Expand Text Editor, Transact – SQL, and then uncheck the box next to Intellisense.
That’s it. We are done. Once this was fixed, we did not see the problem at all in an application. Let me know if you have ever faced such problems and what was the solution.
Reference: Pinal Dave (https://blog.sqlauthority.com)
It is easy. Just put on login trigger that raises error when app name like %intellisense%. In SSMS 18 even turning it off in settings enables intellisense on new connection. One of many bugs of new SSMS.
Thanks for sharing this information with us….
What do you mean by application? Is it front-end application or you are referring to SSMS?
IntelliSense is one of my favorite features. If your db has that many tables it might be time to partition.
Isn’t this on a per client basis?
350,000 tables in one db? Inquiring minds gots to know…
How we can disable this for all users at server level instead of asking each user to disable it?
Great, that was really bugging me. Thanks.