Improve performance against sys tables in SQL Server

Question

We're experiencing performance issues in our SQL Server application (written in PHP, but we experience the same timings when running the queries in the management studio, so I don't think that's releveant).

The offending query is as follow:

SELECT
    c.name, t.name AS type, c.is_nullable, c.is_identity, 
    object_definition(c.default_object_id) AS default_value, 
    c.precision, c.scale, c.max_length, c.collation_name,
    CASE WHEN p.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_primary, 
    CASE WHEN u.column_id IS NOT NULL THEN 1 ELSE 0 END AS is_unique 
FROM
    sys.columns AS c
    LEFT JOIN sys.types AS t
        ON c.user_type_id = t.user_type_id
    LEFT JOIN (
        SELECT DISTINCT
            ic.object_id, ic.column_id
            FROM sys.indexes ix
            JOIN sys.index_columns ic
                ON ix.object_id = ic.object_id
                AND ix.index_id = ic.index_id
            WHERE is_primary_key = 1
        ) AS p
        ON p.object_id = c.object_id AND p.column_id = c.column_id
    LEFT JOIN (
        SELECT DISTINCT
            ic.object_id, ic.column_id
            FROM sys.indexes ix
            JOIN sys.index_columns ic
                ON ix.object_id = ic.object_id
                AND ix.index_id = ic.index_id
            WHERE is_unique = 1
        ) AS u
        ON u.object_id = c.object_id AND u.column_id = c.column_id
WHERE
    c.object_id = object_id('tblTestTable');

Testing locally on SQL Server 2014 Express we get a first-run time of about 0.3s with subsequent runs between 0.1s and 0.2s. On our production server, running the full version of SQL Server 2014, the performance is worse!

I would expect a query such as this (using the system tables) to be running much faster, e.g. in the 0.01 -> 0.05 range, which is the kind of performance we get for similar queries against our own user tables.

1. Should I expect a query like this to be slow?
2. If yes, is there an alternative, faster method of getting this information? If no, what should we do to optimise it?

I notice that these system views don't appear to have indexes. Is that a factor?

Also of relevance is that this query originally used the INFORMATION_SCHEMA views, but these were at least twice as slow as the current performance we are getting using sys (though possibly that was because the sub-selects were in the field list rather than in the join).

Note that the timings come from the properties window in the management studio, and are consistent with what I get if I microtime() the query execution from within PHP.

UPDATE #1

I have constructed a query using our user data tables, which is basically the same structure as the one above (or as close as I could get).

This query runs at about 0.14s on first run, and then between 0.015 and 0.07 on subsequent runs. This is the kind of performance I would be expecting for the sys queries. Therefore, this appears to be an issue specific to the sys tables, rather than a general server configuration issue.

I can post the query here if it would be helpful, but will hold-off for now in case it's just spammy.

UPDATE #2

As requested, here is the stats output with SET STATISTICS TIME|IO ON for the original query, from a cold cache.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(56 row(s) affected)
Table 'sysiscols'. Scan count 112, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 112, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 448, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 0, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysscalartypes'. Scan count 0, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 210 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Answer 1

System views do have indexes (the underlying system catalog tables have indexes, actually). First and foremost, start by identifying the slowness cause. Read How to analyse SQL Server performance. I would somehow doubt this to be root caused to an indexing issue (ie. driven by size-of-data in metadata views). It is much more probable you are experience blocking, for various reasons. Scanning system views is still subject to locks, and DDL operations (create/alter/drop) will block scans until the DDL commits.

Also, just apply some common sense query optimizations. You are filtering by c.object_id = object_id('tblTestTable'); the result, but perhaps the inner queries (SELECT DISTINCT ...FROM sys.indexes ix JOIN sys.index_columns ic) cannot push down this predicate. Try forcing it, ie. add the WHERE object_id = object_id('tblTestTable') clause to the inner queries as well.

Answer 2

Based on various comments in this thread and in my own research, I have concluded the following:

Q1. Should I expect a query like this to be slow?

Answer: Apparently, yes.

I reduced the query to the following, which is still really slow, by my standards (i.e. 0.11 to 0.17 seconds on a warm cache, instead of 0.0x seconds, which I would expect):

SELECT
    c.name, c.is_nullable, c.is_identity,
    c.precision, c.scale, c.max_length, c.collation_name
FROM
    sys.columns AS c;

If the straight query, with no joins or calculated fields, is this slow then I can only conclude that it is a limitation of SQL Server rather than something strange that I am doing.

---

Q2. If yes, is there an alternative, faster method of getting this information? If no, what should we do to optimise it?

Answer: It appears that there is no faster method, nor are there any meaningful optimisations to be made (assuming that all columns are required).

Here is a quote from Remus Rusanu in a comment to one of the other answers:

Are you saying that these are complex views and that therefore I should therefore expect them to be slow?

I'm saying that you will get slower response than compared to a table of similar structure as the view. The underlying tables are optimized for metadata maintenance operations (find by Id and by name) and to prevent DDL excessive locking or even deadlocks. Performance of querying of the views on top of the system tables has to be 'good enough', not the main goal.

Based on this, as well as some experiments of my own, it seems unlikely that there are any further optimisations that could be made to extract this information in a faster way, either through alternative query formulation or via different lookup mechanisms.

---

The solution, in my case

So, given the above, the only solution for our use-case is to cache the table schema locally, via the caching mechanisms provided by our framework. This requires that we run the re-cache script every time we update the DB schema, and runs the risk of the application blowing-up massively if schema changes are made without this script being run. However, it has removed this performance bottle-neck from our application, simply by removing the need to run the query during normal use.