I’m getting an error when trying to connect a project to a tenant, which I suspect is due to the very large scale that we’re running at (multiple thousands of projects, hundreds of environments, dozens of tenants).
To reproduce the error:
Click “Tenants” in the top menu
Select a tenant
Click “Connect Project”
Select a project from the list
Select an environment from the list
Click “Add connection”
The following error then appears (@ids_56 to @ids_1951 do appear in the error message but I’ve truncated them below for brevity):
Connect to a Project
Exception occured while executing a reader for SELECT [Id] from [IdsInUse] WITH (NOLOCK) WHERE [Id] IN (@ids_0, @ids_1, @ids_2, @ids_3, @ids_4, @ids_5, @ids_6, @ids_7, @ids_8, @ids_9, @ids_10, @ids_11, @ids_12, @ids_13, @ids_14, @ids_15, @ids_16, @ids_17, @ids_18, @ids_19, @ids_20, @ids_21, @ids_22, @ids_23, @ids_24, @ids_25, @ids_26, @ids_27, @ids_28, @ids_29, @ids_30, @ids_31, @ids_32, @ids_33, @ids_34, @ids_35, @ids_36, @ids_37, @ids_38, @ids_39, @ids_40, @ids_41, @ids_42, @ids_43, @ids_44, @ids_45, @ids_46, @ids_47, @ids_48, @ids_49, @ids_50, @ids_51, @ids_52, @ids_53, @ids_54, @ids_55, ... etc ... @ids_1952, @ids_1953, @ids_1954, @ids_1955, @ids_1956, @ids_1957, @ids_1958, @ids_1959, @ids_1960, @ids_1961, @ids_1962, @ids_1963, @ids_1964, @ids_1965, @ids_1966, @ids_1967, @ids_1968, @ids_1969, @ids_1970, @ids_1971, @ids_1972)
SQL Error 8623 - The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
This specific tenant has about 300 projects already connected. Most of them have the same set of about 10-12 environments, which totals around 2000 environment instances listed on the page and that number is very close to the @ids_1972 in the error message which might be significant.
This is preventing us from connecting any more projects to our tenant, which is blocking some upcoming production releases.
Could you please advise?
Thanks,
Mike
P.S. As a bonus, there’s a typo in the error message as well - “occured” -> “occurred”.
I think this is happening when we attempt to validate that the tenant’s project/environment connections are all valid. Because you have so many it is causing the query to refuse.
This doesn’t happen in our current PROD version (2018.1.3) so we’d potentially be introducing this issue into our PROD environment if it’s not already fixed in 2018.3.10. We’ll test the latest version in our lab and let you know what we find.
The variable set issue hasn’t been fixed in 2018.3.10.
The underlying SQL parameters error is an ongoing problem for us and rears its head in different places. I was going to figure out a permanent fix for the problem but it will take some time. Considering it is blocking you from upgrading I can just fix the issue Leslie has reported and worry about a more permanent solution later.
