Identity Broker Service fails to start (timeout) when removing connectors/adapters

Assigned to Tony.

See how this currently works. It may be worth just executing a parameterised sql command that deletes where partition = id

Could the index being maintained be taking up time? Look at dropping the clustered index before the query and recreate after the delete.

Alternatively, could the delete be using a an index when the index may not be the best idea? See http://msdn.microsoft.com/en-us/library/ms187373.aspx for instructions on setting INDEX(0?

To determine the cause of lengthy deletions, cascade deletes were turned on/off

and the clustered/non-clustered indexes were removed.

From these results CASCADE should be left in-tact, and the root cause of the problem appears to be one-to-many of the non-clustered indexes.

The following combinations of indices were tested to find the root cause of the problem:

The test involved determining the total execution time of removing a particular set of indices, and then deleting a particular partition from the IDB database (with 300,000 entities) and then re-adding the indices at the end of the query.

The offending indexes appear to be those on the EntityValue value type columns, and furthermore simply removing indexes en masse does not appear to improve performance, as can be seen by the second test.

This behaviour would appear to reflect that described here; specifically:

"Performance gains are achieved in select operations because the query optimizer can locate all the required column data within the index; the table or clustered index is not accessed. However, having too many included columns may increase the time that is required to perform insert, update, or delete operations to the underlying table or indexed view because of increased index maintenance."

If this is the case, then these indices may be dramatically affecting the performance of Insert, Update and Delete operation in IdentityBroker. I am currently testing the implications of removing/re-instantiating these indices in IDB-150.

Finally, the performance of the final test (~6.5 minutes) does not reflect the best times achieved in the tests mentioned previously -> There may still be a non-clustered index that is affecting performance.

The following is a subset of the overall tests to be performed in conjunction with IDB-150.

Test 0: (Base)

Description: 300,000 Deletes only, with no more Partitions remaining.

Test 1:

Description: 300,000 Deletes, with 300,000 entities in existence against a separate partition.

Test 2:

Description: 300,000 Deletes, with 600,000 entities in existence against a separate partition.

Note: The incorrect indices were removed in Test 2.

Expected outcome: Currently unknown
Preferable outcome: Linear decrease in performance, minor and predictable decline.
Potential edge-case requirements:

• If an exponential decrease in performance is encountered, then the respective test will need to be re-run with the indices left in-tact to compare the rates of decline.

From the data that's been collected so far, we can make the following declarations:

1. That cascading deletes are not to blame
2. That some of the non-clustered indexes are, but not all.
3. That the dta_index_EntityValue_7_181575685_K13_K12_K2_1_3_4_5_6_8_9_11, IX_EntityValue_IntValue and IX_EntityValue_StringValue are some of the non-clustered indexes that directly affect the performance of deletions (and in potentia Updates and Inserts).
4. That from the execution times of a default IdentityBroker database and one with the previously mentioned offending indices removed, it would appear that the optimal configuration with regards to Deletions (and again in potentia Updates and Inserts) is the latter.

The following tests were run to determine the effects of removing the dta_index_EntityValue_7_181575685_K13_K12_K2_1_3_4_5_6_8_9_11 on the performance of SELECT statements. Early tests were run against simple SELECT statements, which showed that removing this index was actually beneficial to the overall performance of the SELECT statement, however these statements did not reflect those being executed by IdentityBroker and as such were not particular applicable.

A slightly updated copy of a query run by IdentityBroker - as seen below - exhibited the following times:

SELECT [t0].[EntityId], [t0].[PartitionId], [t0].[ObjectClassId], [t0].[DN], [t6].[EntityValueId], [t6].[EntityId] AS [EntityId2], [t6].[ValueType], [t6].[BigIntValue], [t6].[BitValue], [t6].[FloatValue], [t6].[ImageValue], [t6].[IntValue], [t6].[StringValue], [t6].[TextValue], [t6].[UniqueIdentifierValue], [t6].[PartitionId] AS [PartitionId2], [t6].[CollectionKeyId], [t6].[ValueOrder], [t6].[DecimalValue], [t7].[Caption], [t7].[CollectionKeyId] AS [CollectionKeyId2], (
    SELECT COUNT(*)
    FROM [dbo].[EntityValue] AS [t8]
    INNER JOIN [dbo].[CollectionKey] AS [t9] ON [t9].[CollectionKeyId] = [t8].[CollectionKeyId]
    WHERE ([t8].[EntityId] = [t0].[EntityId]) AND ([t8].[PartitionId] = [t0].[PartitionId])
    ) AS [value], [t2].[test], [t2].[ObjectClassId] AS [ObjectClassId2], [t2].[ClassName], [t2].[PartitionId] AS [PartitionId3]
FROM [dbo].[Entity] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[ObjectClassId], [t1].[ClassName], [t1].[PartitionId]
    FROM [dbo].[ObjectClass] AS [t1]
    ) AS [t2] ON [t2].[ObjectClassId] = [t0].[ObjectClassId]
CROSS JOIN ([dbo].[EntityValue] AS [t3]
    INNER JOIN [dbo].[CollectionKey] AS [t4] ON [t4].[CollectionKeyId] = [t3].[CollectionKeyId])
LEFT OUTER JOIN ([dbo].[EntityValue] AS [t6]
    INNER JOIN [dbo].[CollectionKey] AS [t7] ON [t7].[CollectionKeyId] = [t6].[CollectionKeyId]) ON ([t6].[EntityId] = [t0].[EntityId]) AND ([t6].[PartitionId] = [t0].[PartitionId])
WHERE ([t4].[Caption] = 'GUID') AND ([t0].[PartitionId] = 'C99043B6-D673-4CCB-ADCB-267F7C5674EE') AND ([t3].[EntityId] = 
[t0].[EntityId]) AND ([t3].[PartitionId] = [t0].[PartitionId]) 
ORDER BY [t0].[EntityId], [t0].[PartitionId], [t2].[ObjectClassId], [t3].[EntityValueId], [t4].[CollectionKeyId], [t6].[EntityValueId], [t7].[CollectionKeyId]

Attached sql execution plans of index select tests.

Removal of the listed indexes and addition of the indices introduced by the execution of the listed queries significantly improves the performance of both deletes and selects simultaneously:

Indices to keep:

• Entity.IX_EntityKey
• Entity.PK_Entity
• EntityValue.IX_EntityValueKey
• EntityValue.PK_EntityValue

To Add:

CREATE NONCLUSTERED INDEX [_dta_index_Entity_7_149575571__K3_K2_4_5] ON [dbo].[Entity] 
(
	[PartitionId] ASC,
	[EntityId] ASC
)
INCLUDE ( [ObjectClassId],
[DN]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_EntityValue_7_181575685__K13_K3_14] ON [dbo].[EntityValue] 
(
	[PartitionId] ASC,
	[EntityId] ASC
)
INCLUDE ( [CollectionKeyId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [_dta_index_EntityValue_7_181575685__K14_K13_K3_K2_4_5_6_7_9_10_12_15_16] ON [dbo].[EntityValue] 
(
	[CollectionKeyId] ASC,
	[PartitionId] ASC,
	[EntityId] ASC,
	[EntityValueId] ASC
)
INCLUDE ( [ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[IntValue],
[StringValue],
[UniqueIdentifierValue],
[ValueOrder],
[DecimalValue]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

These changes resulted in the following:

Description: 300,000 Delete, with 300,000 entities in existence against a separate partition.

• ~1 minute 30 seconds longer than removing the indices altogether
• A gain of approximately 12minutes 15seconds - as opposed to the indices being maintained in the current state of the IdentityBroker database.

Description: Select statement executed during IdentityBroker full-import

• As opposed to 5minutes 29seconds encountered with the previous index configuration - A gain of approximately 2minutes and 30seconds

If these benefits persist through further testing, it would appear that this is the ideal index configuration for the IdentityBroker database.

Reassigned for confirmation of completion

Closed.