Query against a view under master database is much slower than query directly under specific database

Question

I am not sure whether there exists a general answer before I give more details.

For exmaple: I have a view named vw_View

I tried the following two queries to get the result:

1. Under master database select * From [test].[dbo].[vw_View]

2. Under test database select * From [dbo].[vw_View]

Could anyone tell me why query against the same query but from master database is much slower than query against from the other databases, I even tried the others by:

Use [db]    --any other databases not master database
select * From [test].[dbo].[vw_View]

I have checked the actual execution plan, the join order differs but why it will change since I have already specify [test].[dbo].[vw_View] when under master

Just out of curiosity, thanks in advance.

Answer 1

Have the same issue - executing a view from master goes infinitely long, but executing the same view "under any other user database on this server" goes only 8 sec. I have an environment where we just migrated to SQL Server 2017 and "all other databases" have Compatibility Level = 2008 (or 2012)

So I did a few tests:

1. If I create a new DB with default Compatibility Level = 2017 and run the query it executes infinitely long
2. If I change Compatibility Level to 2008 and reconnect - 8 sec
3. If I change Compatibility Level back to 2017 - long run again

And the final thing we have noticed about the query itself - the query is using CHARINDEX function and if I comment it out the query executes equally 8 sec for both compatibility levels.

So... it looks like we have an mixed issue with CHARINDEX function execution on legacy database under Compatibility Level = 2017 context.

The solution is (if you can call it this way...) - to execute legacy queries under (the same) legacy execution context.