[11.5.355 Std] - Fetching schema objects is too frequent

[11.5.355 Std] - Fetching schema objects is too frequent

We have a fixed database schema so it does not change. Whenever we change databases then SQL Assistant will lock up the user interface and flash a pop-up message:
Fetching <databaseName> schema objects.

The delay is about 30 seconds and really painful because it happens whenever we switch databases and as I mentioned in our case is completely pointless.

Is there a way to only refresh the objects on demand and stop it automatically doing this?

In the settings I applied the setting "DB Options: Automatic Cache Refresh = No" but it makes no difference. It still blocks me and freezes the UI every time I move to a different database.

I see there is a hotkey defined for "Refresh Cache" which appears to do what we are looking for, we just need it to stop also automatically doing a cache refresh by itself.

(spelling error in tooltip!)

Hi.

How big is your schema?

Hi Brendon,

The issue seems to be in a different place. Fetching schema object names
from the catalogue shouldn't take that long, normally it takes anywhere from
several milliseconds to a couple of seconds, so that shouldn't be a
bottleneck. There are some ways to optimize the catalogue queries, but let's
troubleshoot first why you're experiencing such a bad performance result.

How many objects do you have in your databases roughly?

Do you use NamedPipes of TCP/IP connection? NamedPipes are typically much
slower with remote databases.

Is the performance result reproducible in SQL Assistant SQL Editor?
If you don't have Professional Edition featuring the professional IDE, you
can still test it with the light SQL Editor version available in the
Standard Edition). We need to see if the issue is specific to connection
type and source application.

Sincerely,
Technical Support at SoftTree Technologies, Inc.

I used the SQL Profiler to capture the metadata query used by SQL Assistant. It is quite long so is copied below at the end. It returns 12,131 rows, most of which are stored procedure names but there are lots of tables, triggers and functions as well.
Can we configure the metadata query to remove some of the types that we don't need?

When SQL Assistant runs it the query takes 56 seconds. When I copy the query text into MS Server Management Studio it takes just 2 seconds to run.
Do you know why the SQL Assistant is so much slower?

One difference is that the SQL Assistant runs with event class = RPC but when I do it myself the class is SQL:Batch.

Some information about RPC v Batch is here: https://dba.stackexchange.com/questions/34484/difference-between-sql-batch-statement-and-rpc

I guess SQL Assistant's call is flagged as RPC because it uses "sp_executesql" but I don't know why my version of the same call is flagged as BATCH when I run it from the Management Studio script editor. I don't know if this explains the difference in speed.

If we can get the SQL Assistant's call to run at 2 seconds then that would be OK and a great improvement on currently circa 1 minute!

Code:

exec sp_executesql N'SELECT DISTINCT
o.name,
CASE WHEN o.type = ''SN'' THEN ''^'' +
CASE CAST(OBJECTPROPERTYEX(o.object_id,''BaseType'') AS VARCHAR)
WHEN ''AF'' THEN ''F''
WHEN ''FN'' THEN ''F''
WHEN ''FS'' THEN ''F''
WHEN ''FT'' THEN ''U''
WHEN ''IF'' THEN ''U''
WHEN ''IT'' THEN ''T''
WHEN ''P'' THEN ''P''
WHEN ''PC'' THEN ''P''
WHEN ''S'' THEN ''T''
WHEN ''SQ'' THEN ''I''
WHEN ''TF'' THEN ''U''
WHEN ''TT'' THEN ''K''
WHEN ''U'' THEN ''T''
WHEN ''V'' THEN ''V''
WHEN ''X'' THEN ''P''
ELSE '' ''
END
ELSE
CASE
   WHEN p.[object_id] IS NOT NULL THEN ''TP''
   ELSE o.TYPE
END END,
o.object_id,
o.create_date,
o.modify_date
from
[Eurodat507_EUNZCH].sys.all_objects o WITH (NOLOCK)
LEFT JOIN [Eurodat507_EUNZCH].sys.partitions AS p WITH (NOLOCK)
   ON o.[type] = ''U'' AND p.[object_id] = o.[object_id] AND p.partition_number = 2
where
type in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'',''SN'',''PC'',''FT'',''FS'',''TR'',''SO'')
and (
   o.schema_id = @b0
   or (
      1 = @b1
      and user_name() = ''dbo''
      and o.schema_id = 4
      and o.name in (''sysobjects'',''sysindexes'',''syscolumns'',''systypes'',''syscomments'',
      ''sysfiles1'',''syspermissions'',''sysusers'',''sysproperties'',
      ''sysdepends'',''sysreferences'',''sysfulltextcatalogs'',''sysindexkeys'',''sysforeignkeys'',
      ''sysmembers'',''sysprotects'',''sysfulltextnotify'',''sysfiles'',''sysfilegroups'')
      )
   )
and not (o.name = ''dtproperties'')
and not (o.name like ''dt%'' and o.type = ''P'')

union all

SELECT t.NAME + COALESCE(CHAR(0) + t1.name
+ case
when t1.name in (''varchar'', ''char'', ''binary'', ''varbinary'')
   then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length) end + '')''
when t1.name in (''nvarchar'', ''nchar'')
   then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length / 2) end + '')''
when t1.name in (''decimal'', ''numeric'')
   then ''('' + convert(varchar, t1.[precision]) + '','' + convert(varchar, t1.scale) + '')''
else ''''
END, ''''),
COALESCE(o.type, ''TD''),
COALESCE(tt.type_table_object_id, t.user_type_id),
o.create_date, o.modify_date
FROM [Eurodat507_EUNZCH].sys.types t WITH (NOLOCK)
LEFT JOIN [Eurodat507_EUNZCH].sys.types t1 WITH (NOLOCK) ON t.system_type_id = t1.user_type_id
LEFT JOIN [Eurodat507_EUNZCH].sys.table_types tt WITH (NOLOCK) ON tt.name = t.name
LEFT JOIN [Eurodat507_EUNZCH].sys.objects o WITH (NOLOCK) ON tt.type_table_object_id = o.[object_id]
WHERE t.is_user_defined = 1 and t.schema_id = @b2

',N'@b0 int,@b1 int,@b2 int',@b0=1,@b1=1,@b2=1

[/code]

here is a screenshot from the SQL Profiler

SQL Assistant runs as RPC and takes 55840 milliseconds.

Running the same query from the Microsoft SQL Server Management Studio in BATCH mode takes just 1474 milliseconds.

I checked the "SQL Editor", also from SoftTree and bundled with SQL Assistant.
It has the same problems with refreshing the cache as in Microsoft SQL Server Management Studio. I can take between 40 and 90 seconds and runs as RPC call.

Screenshot:

Our database server configuration is using TCP/IP for network connections and Named Pipes are disabled.
I believe that is the recommended configuration.

2 seconds sounds to me like reasonable timing. SQL Assistant should be actually able to retrieve the result with 12,131 rows faster than SQL Server Management Studio (SSMS) maybe in under a second. Unlike SSMS it internally doesn't fetches result set row by row, it fetches large blocks of rows.

Please try the following. In SQL Assistant Options, set the filter at the bottom to Show options for SQL Server. Then activate DB Options tab and expand DB Queries section. Locate Context Context (MSSQL) query, and in the beginning of that query add the following line

SET ARITHABORT ON

Click Ok to save the changes and test if that makes a difference for SQL Intellisense. Please let us know how it goes. The next step depends on the result of that test.

Using ARITHABORT definitely helps! It gets the execution time down from 58 to just 14 seconds which is a palpably faster!

The effect is reproducible and I can revert it by removing ARITHABORT.

Running the query in MS SQL Server Management Studio is still faster though at exactly 2 seconds so I think there is still room for improvement.

Some screenshots from the profiler to show the timing effects:

Previously:

With ARITHABORT

Great. The fact that it makes a difference indicates some dependency on the database connection parameters that make the database choose inefficient execution plan. But also a worry that SQL Assistant opens a separate connection not shared with SSMS. Please give me a little while to research this and get back to you with specific steps.

Can you please check in SQL Assistant Options if Targets -> SQL Server Management Studio -> Advanced -> Register SQL Assistant add-on option is set to Yes?

Hi SysOp,
the add-on option was NOT registered in my system. I changed the setting to YES and on saving with OK there was a system UAC prompt to install it. I supplied this and then rebooted the machine.

One big, big difference now that the add-on is registered is that it only needs to refresh the cache once for each database as long as I keep MS SQL Server Management Studio open.
If I hop back and forwards between two databases then there is only a single cache refresh for each one the first time the SQL Assistant sees it and then if I keep swapping back and forth it doesn't constantly do a cache refreshes each subsequent time.
This is the behaviour I was looking for when starting this topic so that is great!

Now the speed of refreshing the cache is still problematic. The first time I open the application it does it for the default database in 1-2 seconds. For other databases it is either the same 1-2 seconds or occasionally 45 - 60 seconds.
It seems like there are two different modes it operates in; one is good and fast, the other incredibly slow and I can't predict which mode it will use each time.

Confusing!

You mentioned the properties of the connection are probably to blame. Here is a screenshot of the connection I am using:

As you can see it is just the default ADO.NET connection created automatically by SQL assistant.

So the core problem seems to be that the add-on needs to be registered for it to remember the cache of each database and I will keep observing how it behaves over the next few days.

Thanks, Brendon

Do you happen to have any linked servers that are remote to the connected SQL Server instance?

I had cache refresh/queries (occasionally) with linked servers. I think support helped me modify one of the cache queries to no longer scan linked server databases for their layouts. It made a massive difference.

Thank you for the details. The provided screenshot is for SQL Assistant. If the addon is installed correctly, you should not see SQL Assistant's connection prompts. It should transparently use connections you open in SQL Server Management Studio. If you get separate connection prompts, we need to work on fixing that issue. It can also explain sudden delays when switching between databases, which might be a result of expiring idle connections or something causing it internally to reconnect and that's where the extra time is spent. If you don't see separate connection prompts, and it's not because the "default" connection is selected, then we need to look into other factors. As Mindflux suggested, it could be caused by remote linked servers registered in the database server, and in this case we would want to filter them out and ignore in Intellisense if they slow down code editing and internal cache refreshes.

The connections window doesn't open automatically. I just went into SQL Assistant to show you my current settings. Normally the connections are managed silently in the background and there are no prompts.

We do have a linked server in a remote location so yes, please do explain how to modify the cache queries to exclude this.