Sql-server – find a compatible version of SSMS for SQL Server 2016 CTP

You can no longer use SOAP/HTTP endpoints. This isn't a SQL Server 2016 problem, since this would not have worked in 2014 either. From the documentation on CREATE ENDPOINT:

NOTE: Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

It has been deprecated for at least 8 years (this is the oldest version of the current document I could find):

• Deprecated Database Engine Features in SQL Server 2008

If you look at that document, you will see that "The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option" was deprecated at that time. Their suggestion for a replacement is to "Use Windows Communications Foundation (WCF) or ASP.NET instead."

Arguably, there could be a better error message, like the following (emphasis mine):

Msg 7877, Level 16, State 1
SOAP/HTTP endpoints are no longer supported in this version of SQL Server.

But your workarounds remain the same:

1. Continue using an older version of SQL Server.
2. Find another source for this information that doesn't require this type of information.
3. As the docs suggest, don't try to make SQL Server perform this work (it's not a web browser) - e.g. create an application or service that stores the data in SQL Server.

It might help to update statistics on the system tables that are used by the query. Based on your STATISTICS IO output you would run the following code:

UPDATE STATISTICS sys.syssingleobjrefs WITH FULLSCAN;
UPDATE STATISTICS sys.sysidxstats WITH FULLSCAN;
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysobjvalues WITH FULLSCAN;
UPDATE STATISTICS sys.syscolpars WITH FULLSCAN;
UPDATE STATISTICS sys.sysclsobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysiscols WITH FULLSCAN;
UPDATE STATISTICS sys.sysscalartypes WITH FULLSCAN;

"Statistics on system tables and query performance" contains a reference to this technique.

Depending on your database updating statistics may not be sufficient. I created a test database with 10000 simple tables with one column each:

DECLARE 
@j INT = 0,
@create_table VARCHAR(1000);
BEGIN
    WHILE @j < 10000
    BEGIN
        SET @create_table = 'CREATE TABLE X_TABLE_' + CAST(@j AS VARCHAR) + ' (ID INT NULL)';

        EXEC (@create_table);

        SET @j = @j + 1;
    END;
END;

The query that you have should return 10k rows but it still hadn't finished after one minute. It had only returned 651 rows so it was on track to finish in 15 minutes.

One change that made a big difference on my database was enabling the legacy cardinality estimator. The query returned all 10000 rows in one second after adding the following hint:

OPTION (RECOMPILE, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') );

As you said you cannot modify the code so that isn't immediately helpful. However, you can enable trace flag 9481 at the session level to get the legacy CE. It's possible to enable trace flag 9481 for a particular user as described in "Wanting your non-sysadmin users to enable certain trace flags without changing your app?". However, I do not know what other effects this would have on your application.

If it turns out that the legacy CE also improves performance in your database a less disruptive option may be to create a plan guide for this query. A plan guide will force a query to have a particular execution plan. You could capture a good enough execution play using the legacy CE and force future query executions to use that same plan even without TF 9481 turned on. You should be aware that many people in the industry do not speak favorably of plan guides.