5

I have SSMS 2014 installed on my desktop, and many 2012 and 2014 databases running in our data center.

So far I found one database (database A) doesn't have IntelliSense working, for exampletyping:

SELECT [put cursor here] FROM sys.objects

and moving cursor between select and from, pressing CTRL + J, I can see popup options for other databases, just not for database A. I have checked all options and refresh local cache, all don't work, and I am using an account which has sysadmin permission.

  • The compatibility level is 11.0
  • Version is

    Microsoft SQL Server 2012 - 11.0.5556.0 (X64) Oct 31 2014 16:50:24 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

I can log on to the db server which is in US and see IntelliSense working there, it just doesn't on my desktop (in Australia). Is it most likely a latency issue? is there a timeout limit or setting for this feature?

  • 2
    See the bit here that starts with, "And finally, you may just need to be patient." I suspect this particular database is a lot more objects, a lot of schemas, or has a very complex set of dependency chains. Once it finally all loads, you should be fine, as long as your network connectivity is somewhat reliable and there aren't a lot of schema changes happening. Leaving Management Studio open instead of opening/closing it a lot should relieve some of this too. – Aaron Bertrand Jan 29 '15 at 1:54
  • Thanks Aaron, it makes sense, but I still cannot make it working, it is very likely the poor network connectivity between US datacenter and AU Datacenter. – AlbertZOZ Feb 2 '15 at 4:26
  • You could also try mssql-cli to see if it performs any better being a bit more lightweight. ApexSQL Complete is also free and may be worth a try as well. I recall it working on larger databases but still being somewhat laggy when it listed items. – LowlyDBA Jan 26 '18 at 22:21
4

It might be latency, or it might be something else. Run a SQL Server Profiler trace on the remote DB, starting the trace just prior to connecting via SSMS remotely. IntelliSense works by running queries to get the meta-data, so you will be able to see if it is even attempting to work or not, and if so, potentially if it is running into any problems.

You should filter the ApplicationName column on LIKE %IntelliSense%, though the full name of the program is Microsoft SQL Server Management Studio - Transact-SQL IntelliSense. Events to select: SQL:BatchStarting, SQL:BatchCompleted, SQL:StmtStarting, SQL:StmtCompleted, SP:Starting, SP:Completed, SP:StmtStarting, SP:StmtCompleted, RPC:Starting, and RPC:Completed.

Try it first on a DB that works so you know what the normal / expected behavior should look like. Hitting Control + Shift + R to force a reload of the local cache should produce a decent amount of activity captured by your trace.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.