Troubleshooting IntelliSense in SQL Server Management Studio 2012

 

Problem

IntelliSense can be a very powerful ally for the T-SQL developer. It can significantly reduce keystrokes, prevent you from guessing at object names, make sure you spell them correctly (and use the right case), and can help you understand the interface to several programmable entities such as stored procedures and built-in functions. In Management Studio 2012, there are some enhancements to IntelliSense that will make it even more powerful - including more intelligent caching, and partial keyword matching (so you no longer have to remember what letter the waits DMV *starts* with - just type "waits" and it will narrow it down).

I have seen several cases in forums, newsgroups and on twitter where folks complain that IntelliSense is "not working." Now, "not working" can cover a wide variety of things. For the purpose of this tip, we're going to restrict that to not behaving as designed, as opposed to not behaving as desired (for example, if you don't like that pressing the space bar or tab auto-completes for you, that's a different discussion). I thought it would be useful to list out the common symptoms and how you can try to resolve them. Note that most of these items are also applicable to previous versions.

Solution

There are a couple of different symptoms that can lead you to believe that IntelliSense is "not working." One is where the IntelliSense features (auto-complete / list members / tool-tips / underlining invalid references) do not function at all. Another is where the completion list appears, but it does not contain the object(s) you're trying to use.

• IntelliSense does not function at all

  There are several potential reasons why IntelliSense may not be working at all; some are more obvious than others.

  • Make sure that IntelliSense is enabled for Management Studio under Tools > Options > Text Editor > Transact-SQL > IntelliSense:
    
    
    
  • Make sure that IntelliSense is enabled for the current query window by checking the Query > IntelliSense Enabled menu option (it should be enabled):
    

    ...or by checking that the toolbar icon is enabled:

    
    
    
  • Since IntelliSense does not work in SQLCMD mode, ensure that you haven't enabled this by checking the Query > SQLCMD Mode menu option (it should NOT be enabled):
    
    
    
  • Check that your script isn't too large. To prevent performance issues, SSMS will not try to parse a script that is larger than the size set in Tools > Options > Text Editor > Transact-SQL > IntelliSense > Maximum script size. You can adjust this setting if you are working with very large files, or consider breaking them up into smaller scripts:
    
    
    
  • Check that your cursor is in a position where IntelliSense is actually supported. It may not be for a couple of reasons:
    • There are certain parts of T-SQL grammar that IntelliSense doesn't yet understand. In Books Online they document the syntax that is supported; unfortunately, there isn't an authoritative list of syntax that is not supported. One example is when creating a check constraint; copy the following code, put your cursor between SELECT and FROM, and try to list members using Ctrl+J:

      CREATE TABLE dbo.foo
      (
       i INT CHECK (i IN (SELECT <put cursor here> FROM sys.objects))
      );

      If you copy the SELECT query alone to another query window and try the same experiment, the completion list will appear (unless you are affected by one of the other issues in this tip).
      

    • Depending on your version of SSMS, IntelliSense may not be able to parse correctly because there are errors further up in the batch. Where feasible, you can add a GO before the current line to see if that temporarily resolves the issue, otherwise you will need to go up and fix the other errors. I haven't noticed this to be an issue in SQL Server 2012 - as long as previous statements are terminated (or the beginning of the current statement is obvious and unambiguous), most IntelliSense functions don't care if there are errors outside of the current statement.
       
    • Your cursor may be inside a comment or a string literal, where IntelliSense parsing simply doesn't dare to venture.
    
    
  • Verify that the connection to your server is active. You may have been disconnected, but haven't noticed because you are not actively running queries. You can try to re-establish the connection by running a query as simple as:

    SELECT 1;

    At first you may see an error message about your connection being disconnected or forcibly closed, depending on your version of Management Studio. You just need to try one more time to remind SSMS that, even though the server was temporarily disconnected, it is still there.
    

  • Verify that the destination server is running an appropriate version of SQL Server; IntelliSense does not function against SQL Server 2005 or previous versions. (You can read a lot of background on this in Connect Item #341872.) You can check the version of the destination server using the following query in the same window:

    SELECT SERVERPROPERTY('ProductVersion');

    If this query yields a build number starting with "8." or "9.", then IntelliSense is not working because the target version is not supported.
    

  • Verify that the destination server is not running SQL Azure which, like SQL Server 2005, is not currently supported by IntelliSense. You can check by running the following query:

    SELECT SERVERPROPERTY('Edition');

    If the result is "SQL Azure" then that is why IntelliSense is not working.

  • This may sound silly, but confirm that you are in fact using Management Studio 2008 or greater. I have seen a couple of people complain that IntelliSense is not working, only to discover that they were in fact running SSMS 2005. You can verify the version in Help > About.

  • Make sure that your client tools are patched to a version at least as high as the server you're trying to connect to. Ideally, your client tools should always be greater than or equal to the highest version you need to manage. If you are also running Visual Studio 2010 on your machine, there were some issues early on that broke IntelliSense, so make sure that Visual Studio is patched to Service Pack 1 and that client tools are updated to the latest service pack for the version of SQL Server you are using.

  • For SQL Server 2012, there is a bug with Contained Databases, where IntelliSense does not function completely for a "contained" database user (see Connect Item #717063). Some of the IntelliSense features will function fine (for example, syntax error highlighting), but none of the completion lists, invalid object/column highlighting, or tooltip helpers will work. As of the time of writing, I haven't received official confirmation from Microsoft about this bug, never mind when it will be fixed, so it is something to keep in mind if you are using the Contained Databases feature. If this affects you, hopefully you can switch to a server-level login until the issue is resolved.

  • If you are using 3rd-party add-ins, try disabling or uninstalling them in the odd event that they are interfering with IntelliSense. This is especially true for plug-ins that are part of a beta or preview release and/or interact with the text editor directly.

  • And finally, you may just need to be patient. Check your network speed, the general health of the server, and be understanding if your metadata is quite large - if you are running SAP, for example, there's a lot more data to bring across than AdventureWorks. When SSMS is attempting to get metadata from the destination server, it may time out before it starts receiving results. This timeout is hard-coded to two seconds in SSMS 2012, but in previous versions, it would wait for the database connection to time out (which could be 30 seconds or more). It will continue trying in the background, so you may not see the drop-down appear right away, but it could appear after successive attempts at pressing Ctrl+J. You can force it to try to reload by pressing Ctrl+Shift+R or the menu option Edit > IntelliSense > Refresh Local Cache. Once metadata is retrieved in the background, the data is cached in local memory. If the connection is working and none of the above factors are in play, the cache will eventually be populated and you should be able to use all of the functionality. But again, on SSMS 2008 or SSMS 2008 R2, this may take a little bit longer because of the more relaxed timeout.

• Some objects or columns don't show up, or are incorrectly marked as invalid

  There are a number of reasons why the completion list may not actually be complete or that an object or column name is underlined and marked as incorrect. One is quite common, but several others are possible too.

  • By far the most common scenario is that your local, in-memory cache is stale. This can be more prevalent if you switch databases often using the USE command, or if the database is changing in other query windows or by other users. You can refresh the cache by pressing Ctrl+Shift+R or by selecting the menu option Edit > IntelliSense > Refresh Local Cache. As above, you may need to wait for the cache to fully load, depending on the size of your metadata and other resources involved.

  • (Updated October 2014.) The object you're trying to use might be a synonym. Synonym support was not added until SQL Server 2012 (see Connect Item #331633) but it does not appear to work for everyone (see Connect Item #744975 (link: http://connect.microsoft.com/SQLServer/feedback/details/744975/ssms-intellisense-does-not-recognize-synonyms-still-broken)), and they don't have any plans to fix that. It also is not expected to ever be back-ported to SQL Server 2008 or SQL Server 2008 R2, especially since those releases are now officially out of mainstream support.

  • There are some elements in the sys schema that do not show up even though they exist. My assumption here is that some subset of system objects must be checked against some list outside of a simple metadata retrieval. Based on a bug (see Connect Item #621445) filed by Jonathan Kehayias (@SQLPoolBoy), some of the new extended events DMVs were moved from the dbo schema to the sys schema. One example is sys.trace_xe_event_map. However, if you try to get IntelliSense to auto-complete this DMV, it does not show up:
    

    These don't show up under the dbo schema in auto-complete either, in case you were wondering.

  • The object you're trying to access might actually be in a different database. Hopefully this isn't something that trips you up, but I wanted to list it for completeness.

  • You may have been denied permissions on the object or it may reside in a schema to which you don't have access. To test this, try accessing it as a more privileged login.

  • And like the last point in the previous section, you may just be experiencing performance due to network or other resources, or sheer metadata size. For a large enough schema, the cache will get populated in a "lazy" way - so for example if you are dealing with 20,000 tables and you've only referenced 50 of them so far, those 50 should have detailed information. The other 19,950 may already be represented in the cache, but they may just have basic details such as table name - until they are fully loaded, the list members functionality may be missing column names and other details.