12

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.

4
  • Ouch. I'm guessing you're doing some messed up type of multi tenant, keeping everyone's data in the same tables & filtering it with views & using synonyms to name them after the base object, on a large scale? Either way, I feel for you
    – Philᵀᴹ
    Mar 3, 2016 at 20:01
  • 2
    Multi tenant? Actually, no. It isn't. Pretty messed up, right? FWIW, it's my understanding that for every application user, there are 5 SYNONYMs created for every table. Lucky me.
    – Dave Mason
    Mar 3, 2016 at 21:43
  • Does removing permissions to some of those objects increase performance (so that there is less of them to potentially use?) I dont know if that is even an option on a user level. Mar 3, 2016 at 21:43
  • It would be interesting to see an execution plan on this. maybe you could post one from sql sentry plan explorer to answers.sqlperformance.com and link to it, unless there is a way to embed this here as well. I'd be interesting in looking at it May 10, 2016 at 23:14

1 Answer 1

1

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?).

2
  • I think that this is sound advice, however, it would be negated on impacting much if IO setup is not a standard physical server with attached disks, for instance virtual instance with SAN, or SSD drives would minimize the noticeable impact of separating primary data files to a different location, right? May 10, 2016 at 23:12
  • 1
    If you have control of the hardware (i.e. you're not being hosted by a third party), you can have different sets of spindles in a SAN (e.g. two or more separate RAID-10 volumes). If You're using (or hosted) with SSDs, then there are no spindles, and IO would be limited mainly by whatever is the bottleneck between the drives and the motherboard (i.e. SATA, RAID, or NIC card, cabling, routers/switches, SAN, SSDs speed), so you wouldn't gain anything by separating the files in that case. May 10, 2016 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.