Search code examples
sql-serversql-server-2008t-sql

Just added a new index, do I have to freeproccache?

All, Do you have to run dbcc freeproccache after you add an index to the system to make the optimizer aware of it?

Thanks in advance.

Solution

  • No, the database engine will build and use an index as soon as you create it.

    You can see it in use by looking at a query plan for a query that selects a record based on the new index.

    For instance, if the index is created on UserID, run SELECT [t].[UserID] FROM [schema].[Table] [t] WHERE [t].[UserID] = 1; and check the query plan - you should see the index being used.