patternMinor
Poor Performance When Calling Query From Another DB on same Server
Viewed 0 times
fromsamequerypoorperformanceanotherwhenservercalling
Problem
I have 2 databases - both on the same server. Both databases also have their files on the same drives.
When I run a query on it's local DB, it runs in 3 secs.
However, when I run it from the second DB, it takes about 20mins! The only difference here is the Database name added to the FROM clause.
What can I look for to find why this performs so badly? Would statistics play a part?
The query is a SELECT one on a TVF. Unfortunately I cannot view the TVF as it is 3rd party query and I do not have access.
When I run a query on it's local DB, it runs in 3 secs.
However, when I run it from the second DB, it takes about 20mins! The only difference here is the Database name added to the FROM clause.
What can I look for to find why this performs so badly? Would statistics play a part?
The query is a SELECT one on a TVF. Unfortunately I cannot view the TVF as it is 3rd party query and I do not have access.
Solution
The issue was the Compatibility Level as suggested by @Kin
One database was 120, the other 100.
I updated to ensure both are 120 and now queries are quick on both databases!
https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database
Thanks all for the help!
One database was 120, the other 100.
I updated to ensure both are 120 and now queries are quick on both databases!
https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database
Thanks all for the help!
Context
StackExchange Database Administrators Q#194612, answer score: 4
Revisions (0)
No revisions yet.