SQL SERVER – Disable IntelliSense in SQL Server Management Studio

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)