4

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.

Improve this question
10
  • 4
    Upload the actual execution plans to brentozar.com/pastetheplan. If the queries use the same objects, I'd expect the same plan as long as the session SET options are the same.
    – Dan Guzman
    Jan 4, 2018 at 13:23
  • The XML of the Query Plan is actually 19421 rows so cannot copy/paste
    – Kevin
    Jan 4, 2018 at 14:45
  • 3
    Why can't you copy+paste? Do you get an error when you try? Failing that post the query and details of the tables involved (specifically the keys and indexes defined). There is no way to properly answer "why is this query slow" without seeing the query (or, preferably for complex queries, a minimal cut-down version that exhibits the same problem) and table+index details or the query plan. Also when you say "on the same server" do you mean in the same instance, or do you have two instances and are connecting them with a linked server setup? That can make a significant difference.
    – David Spillett
    Jan 4, 2018 at 15:00
  • 3
    try running the query with option(recompile) .. also what is the compatibility level of the db that is working fine and the one that is not working fine ? Might be CE issue.
    – Kin Shah
    Jan 4, 2018 at 15:11
  • 1
    Start with data base permission,then check index,update statistics.Above all,what is estimated number of rows in slow db.is there any diff ?
    – KumarHarsh
    Jan 5, 2018 at 11:59

1 Answer 1

Reset to default
4

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://docs.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database

Thanks all for the help!

Improve this answer
2
  • 1
    Glad that it helped !
    – Kin Shah
    Jan 5, 2018 at 16:27
  • Kevin, even though its your answer, you can mark this as the accepted answer to close the question
    – LowlyDBA - John M
    Jan 5, 2018 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.