Background:
I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's. For example, one db has more than 10k VIEW's and 2+ million SYNONYM's.
General Problem:
Queries involving sys.objects (and system tables in general) tend to be slow. Queries involving sys.synonyms are glacial. I am wondering what I can do to improve performance.
Specific Example
This command is run by a third party tool. It is slow in both the app, and in SSMS:
exec sp_tables_rowset;2 NULL,NULL
My Question:
How can I make this run faster?
What I've Tried:
If I SET STATISTICS IO ON I get this output:
(2201538 row(s) affected)
Table 'sysobjrdb'. Scan count 1, logical
reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan
count 1, logical reads 53926, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I have been able to update statistics on the underlying system tables. This has worked in my SQL 2008 R2 or newer environments:
UPDATE STATISTICS sys.sysobjrdb WITH FULLSCAN
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN
I have also been able to perform index maintenance. This works in my SQL 2012 or newer environments. For instance running sp_help 'sys.sysschobjs' identifies the indexes on the table, and from there I create and run these commands:
ALTER INDEX clst ON sys.sysschobjs REORGANIZE
ALTER INDEX nc1 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc2 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc3 ON sys.sysschobjs REORGANIZE
Updating stats and reorganizing indexes helps, but not by much.
Best Answer
If you have not already done so, you could gain performance by moving the primary data file to a separate set of spindles from the rest of the data (see Files and Filegroups Architecture and SQL Server: filegroup for system tables only?).