Refresh cache only on demand ?

Hello,

I'd like to know if is it possible to refresh the cache only on demand and not when I connect to a SQL Server with SSMS ?

I optimized some BD Queries and even disabled some others.
But when I connect to SQL Server outside the local network (Production servers), S.A. can takes 45-60 seconds to refresh the cache (versus 4-5 seconds in local network).This is due to a large amount objects in databases (1000 tables, 6000 stored procedures, ....). And that's not necessary because objects are updated only 2-3 times per year, and each server has the same structure.

Thanks.

As for the manual refreshing, this button sitting at the top right corner of the SA Database Explorer might invoke refreshing of the cache on demand.

Regarding not refreshing it on connect, I'm not sure how that could be done. I doubt (though it could happen) that SA stores that cache anywhere locally it could restore it from, other than rebuilding it from the server upon connecting to it. I can think of a few nasty ways by ravaging and mutilating the existing DB queries so that they would retrieve their results in some well-indexed tables instead of querying the system tables/views and only update those tables using the original queries when they are considered outdated. That could speed things up a bit, but the performance improvement might not be worth the effort. Furthermore, that table would still be remote (not local) as I don't think you could write to anything else than the server (and possibly the linked servers on that server) when executing the code in DB Queries. It's also not a very good idea to store such things on a production server.

But the idea is intriguing, indeed. The data that cache consists of could be stored for hundreds of servers and databases neatly in a local SQLite database and accessed quite fast (though still relatively slow when compared to storing it in the memory). Provided the users are willing to risk working with the outdated cache. Which could also be remedied by frequent updates of the mentioned cache. Which in turn would re-establish the original problem. Hell, I'm so good at this :)

Just an idea. You can replace code for the Objects query with structurally compatible SELECT * FROM my_ojects_cache_table. Create a scheduled task using SQL Assistant's Scheduled Tasks (it will run on your computer) or using SQL Agent (it will run on the server) to refresh my_ojects_cache_table table periodically, perhaps once a day.That refresh will not delay anything. And the query against static my_ojects_cache_table will be very fast. You can also always kick off cache table refresh manually if you need to force its refresh.

Thanks for all suggestions.
I'll try the query "SELECT * FROM my_objects_cache_table".
Thanks

Hello,

It works great !
Thanks for the suggestion SysOp !
You save my life, or at least my patience ! :-)

For those are interested, here what I did :

1) Save the DB Query "Objects" into a table for each databases you want to improve the speed cache, with 2 new columns : schema_id and db_name
2) In SQL Assistant, "BD Options" tab, change the DB Query "Objects" like this :

	Code:
	IF EXISTS(SELECT 1 FROM MYDB.sys.tables WHERE name = 'SQLAssistant_Objects') AND '$DB_NAME$' in ( 'db1' , 'db2' ) BEGIN select s.name , s.[type] , s.[object_id], s.create_date, s.modify_date from MYDB.dbo.SQLAssistant_Objects s where s.[schema_id] = :SCHEMA_ID and s.[db_name] = '$DB_NAME$' END ELSE BEGIN -- original query SELECT DISTINCT o.name, .... .... END

3) Deploy a new job to refresh the table

Last edited by stephh on Wed Aug 18, 2021 7:55 am; edited 1 time in total

Great. Thank you.

If there is a large number of databases, it might be a good idea to create an index on MYDB.dbo.SQLAssistant_Objects ([db_name], [schema_id])