Tuesday, April 10, 2012

Synonyms causing editor slowness

So, today we've been tasked with a problem with slowness in the Query editor for one of our users.

She uses the "Design Query in Editor" option of SSMS.  The editor was taking twenty seconds to appear every time she does this on a certain database.  On some of the other databases the Designer was extremely fast.

In researching this we discovered a couple of known bugs.  The first was due to the number of synonyms used in the database.  In this particular case, we have over 2200 synonyms.  It appears, according to Microsoft that this is a known issue with a large number of synonyms.  After some testing, the number of synonyms in a database dramatically affected the speed of the designer.  As the number of synonyms increased the time increased dramatically.


The second issue was that none of the synonyms actually showed up in the Designer.  The designer has four tabs, tables and synonyms being two of them.  The table tabs works just fine, but the synonym tab is empty.  Again, after consulting the Microsoft site; we have found another known issue.

Unfortunately, for us these don't appear to be issues they are planning on resolving anytime soon.


The following items were found on the Microsoft site.

Slowness in Query editor due to synonyms  (known bug.  Won’t be fixed):

Synonyms not showing in Query editor… Synonyms tab  (Known Bug.  Won’t be fixed)