Troubleshooting SQL Server 2014 Indexes (Part 3)
If you have problems with SQL Server 2014 indexes, review this troubleshooting checklist
to find potential solutions.
1. You can get the error 666.
This is the error message text: “The maximum system-generated unique value for a
duplicate group was exceeded for index with partition ID %I64d.” To resolve this
problem, you can drop and re-create the index.
2. You can get the error 689.
This is the error message text: “Operation not allowed because of pending cleanup
of online index build.” To resolve this problem, you should wait for cleanup to
complete and re-run the operation.
3. The error 1917 occurs during create, rebuild or drop an index on a local
temporary table.
This is the error message text: “Cannot create, rebuild or drop an index on a
local temporary table online.” To resolve this problem, you should perform the
index operation offline.
4. Access violation may occur when you query against a table that contains
columnstore indexes in SQL Server 2014.
This is SQL Server 2014 bug. This bug was first fixed in Cumulative Update package 8
for SQL Server 2014. You can download the Cumulative Update package 8 for
SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
5. After you perform an Insert or Delete operation on the indexed view base table,
the view returns incorrect results.
This problem occurs when the indexed view uses a scalar SUM clause. This bug was
first fixed in Cumulative Update package 8 for SQL Server 2014. You can download
the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
6. You can find out that memory is paged out when columnstore index query consumes
lots of memory in SQL Server 2014.
This problem occurs when the query consumes lots of memory and current committed
memory is larger than the “max server memory” setting. This bug was first fixed in
Cumulative Update package 8 for SQL Server 2014. You can download the Cumulative
Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
7. Access violation occurs when a missing task for a background clear operation
tries to delete a missing index entry.
This problem occurs when you enable the AUTO_CLOSE option of a database. This bug
was first fixed in Cumulative Update package 8 for SQL Server 2014. You can download
the Cumulative Update package 8 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3067836
To work around this problem, you can disable the AUTO_CLOSE option of the database.
To resolve this problem, you should restart the instance of SQL Server 2014.
8. Index corruption may occur when you build a columnstore index with parallelism
on a partitioned table in SQL Server 2014.
This problem occurs when a partitioned table on a database uses compatibility level
120 and you build a columnstore index on this table with DOP > 1. This bug was first
fixed in Cumulative Update package 9 for SQL Server 2014. You can download the
Cumulative Update package 9 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949
To work around this problem, you can set the database compatibility level to 110 or
run columnstore index build at DOP=1.
9. The error 22832 occurs when you try to enable “change data capture” on a table
that uses a unique index with more than one included column.
This is the error message text: “Could not update the metadata that indicates table
table_name is enabled for Change Data Capture.” This bug was first fixed in Cumulative
Update package 9 for SQL Server 2014. You can download the Cumulative Update package 9
for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3075949
10. The error occurs when you change the type of column in a table that has
clustered columnstore index.
This is the error message text: “ALTER TABLE statement failed because a secondary
dictionary reached the maximum size limit.” This bug was first fixed in Cumulative
Update package 10 for SQL Server 2014. You can download the Cumulative Update
package 10 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3094220
To resolve this problem, you can drop the columnstore index, altering the column and
then creating a new columnstore index.
11. When you run a query against a partitioned table that has a columnstore index,
a “Non-yielding Scheduler” condition may occur.
In this case, SQL Server 2014 generates a memory dump file. This bug was first fixed
in Cumulative Update package 11 for SQL Server 2014. You can download the Cumulative
Update package 11 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3106659