SQL Server

Troubleshooting SQL Server 2014 Indexes (Part 1)

Troubleshooting SQL Server 2014 Indexes (Part 1)

If you have problems with SQL Server 2014 indexes, review this troubleshooting checklist
to find potential solutions.

1. Install the latest SQL Server 2014 service pack.

Because many SQL Server 2014 indexes bugs were fixed in SQL Server service packs, you
should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was service
pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694

2. You can get the error 602.

This error indicates that SQL Server could not find an entry for index in a database.
This error may occur if a stored procedure references a dropped table, or metadata is
corrupted. To resolve this problem, drop and recreate the stored procedure, or execute
the DBCC CHECKDB command.

3. The error 1505 occurs during the CREATE UNIQUE INDEX statement.

This problem occurs because a duplicate key was found for the index name. To resolve
this problem, run the CREATE UNIQUE INDEX statement with the new index name.

4. You can get the error 1775 when create a foreign key constraint.

This error indicates that you cannot create foreign key because it references object
whose PRIMARY KEY index is disabled. To resolve this problem, you can enable the
PRIMARY KEY index in the references object.

5. The error 1902 may occur when you create a clustered index.

This error indicates that you cannot create more than one clustered index on the table.
You must drop the existing clustered index before creating another.

6. When you run a complex UPDATE statement together with a NOLOCK hint against a table
in SQL Server 2014, nonclustered index corruption may occur.

In this case, the error 8646 will be logged in the SQL Server error log. This bug was
first fixed in Cumulative Update package 1 for SQL Server 2014. You can download the
Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693

7. The error 701 occurs when you try to create an index in a char, varchar, or
nvarchar type column in SQL Server 2014.

This is the error message text: “There is insufficient system memory in resource pool
‘default’ to run this query.” This bug was first fixed in Cumulative Update package 1
for SQL Server 2014. You can download the Cumulative Update package 1 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693

8. The error 35377 occurs when you build or rebuild clustered columnstore index with
maxdop larger than 1 through Multiple Active Result Sets (MARS) connection.

This bug was first fixed in Cumulative Update package 1 for SQL Server 2014.
You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
To work around this problem, you can set maxdop equal to 1.

9. Access violation occurs when you run CHECKTABLE against the tables with
clustered column store index.

This bug was first fixed in Cumulative Update package 1 for SQL Server 2014. You can
download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
To work around this problem, you should make sure that disk caching is set to off
and stripe sets are aligned on page size (8kb).

10. Access violation occurs when you run an update statistics statement on an index
of a no-partitioned table that has statistics_incremental flag set to ON.

In this case, no matter the table is empty or not. This is SQL Server 2014 bug.
This bug was first fixed in Cumulative Update package 1 for SQL Server 2014.
You can download the Cumulative Update package 1 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693

11. Performance decreases after an ALTER INDEX…ONLINE operation is aborted.

In this case, any DML operations that have to use index metadata either are blocked
or take a long time to be completed. This bug was first fixed in Cumulative Update
package 1 for SQL Server 2014. You can download the Cumulative Update package 1 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2931693
To work around this problem, you can restart the instance of SQL Server 2014 or
put the problematic database into offline state, and then bring it online again.

12. Access violation occurs when you insert data into a table that has a
clustered columnstore index.

The problem occurs because the clustered columnstore index does not support versioning.
This bug was first fixed in Cumulative Update package 2 for SQL Server 2014. You can
download the Cumulative Update package 2 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546

13. DBCC SHRINKDATABASE or DBCC SHRINKFILE cannot move pages that belong to the
nonclustered columnstore index.

In this case, free space may not be reclaimed. This bug was first fixed in Cumulative
Update package 2 for SQL Server 2014. You can download the Cumulative Update package 2
for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546

14. Data corruption may occur in clustered index when you run online index rebuild.

This problem occurs when the online index build query runs in parallel mode and
a deadlock error and a fatal error such as “lock timeout” occur in a specific order.
This bug was first fixed in Cumulative Update package 2 for SQL Server 2014.
You can download the Cumulative Update package 2 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/2967546
To work around this problem, you can run the online index build in serial mode
(DOP = 1).