Why one T-SQL function get different performance running in same server, using same tables but call-CodePudding

I have the follow situation, I have one server running SQL Server 2017 with different databases (DB1, DB2, DB3), each one has different use and different tables. I have created a function in DB1 called DBFTN1 where it use tables allocated only in DB1.

When I run this function in SSMS over one instance of DB1 the result is 4 seconds, returning around 4K records, for reference the command that I am using is similar to

SELECT * FROM DBFTN1('20220801');

When I run the same function using one instance in DB2

SELECT * FROM DB1.DBFTN1('20220801');

the result is similar around 4 seconds and returns the same records. But when I run the same function from DB3 the performance is very slow, the first records appear in around 25 seconds and it needs around 15 minutes to download the 4k records, the command used is similar

SELECT * FROM DB1.DBFTN1('20220801');

What is happening and how can I get similar performance over any instance?

Is important to mention that the server doesn't have another process running when I run the test.

I have tried run the following sentence over the DB3 instance but the result continue being the same:

EXEC sp_updatestats

EXEC sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")';

CHECKPOINT 
DBCC DROPCLEANBUFFERS 

DBCC FREEPROCCACHE

Thank in advance for your help.

CodePudding user response：

Without the complete code of your UDF it is difficult to answer such a question...

Please put the complete code and all the execution plan. There is a tool in SSMS to compare two different execution plans.

I suspect that you have some differences of config parameters between your databases. There is two levels of configuration that can modify an execution plan :

the database parameters
the scoped database parameters

Just have a look over these values with some queries :

SELECT * FROM sys.databases;

SELECT "DB1" AS DBAME, * FROM DB1.sys.database_scoped_configurations
UNION ALL
SELECT "DB2" AS DBAME, * FROM DB1.sys.database_scoped_configurations
UNION ALL
SELECT "DB3" AS DBAME, * FROM DB1.sys.database_scoped_configurations;