Details
-
Technical task
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
This is primarily a fix for OAK-8918, which was intended to warn users of a configuration (when using Microsoft SQL Server) that can lead to degraded performance. Unfortunately, the code committed for that issue does not actually check for this configuration directly, and hence can produce false positives and negatives. This can be especially confusing to users to receive the warning when it is coincidentally true, make the recommended changes, but then continue to receive the warning message.
The issue is that the changes made in OAK-8918 check the default collation of the database, yet that particular collation is only a factor when the tables – DATASTORE_DATA and DATASTORE_META – are created (because the COLLATE clause is not specified in the CREATE TABLE statements, but that's being fixed via OAK-8963). Once the tables exist, then the default collation of the database is no longer a factor since it's the collation of the ID columns that affects performance. Thus, the check performed in OAK-8918 can be incorrect in two scenarios:
- No warning message is produced if the database is using a non-"SQL_" collation yet the ID columns are set to a "SQL_" collation (which should produce the warning message).
- A warning message is produced if the database is using a "SQL_" collation yet the ID columns are not set to a "SQL_" collation (which should not produce the warning message). And this will be the case once users make the recommended change.
This can be fixed by checking the collation of each ID column (instead of checking the collation of the database):
SELECT col.[collation_name] FROM sys.columns col WHERE col.[object_id] = OBJECT_ID(?) AND col.[name] = N'ID';
where the "?" is set to "tableName". Just call it twice, once for each table.
