0

I'm on SQL-Server 12.0.5203.0

There is a query covering multiple databases. I think It is not useful to post the actual query (as it is rather complex and confidential, just think of a query like

SELECT t1.Column1
      ,t2.Column2
      ,t3.Column3
FROM db1.schema.TableName1 t1 with (nolock)
JOIN db1.schema.TableName2 t2 with (nolock) ON SomeCriteria
JOIN db2.schema.TableName3 t3 with (nolock) ON SomeCriteria  
JOIN db3.schema.TableName4 t4 with (nolock) ON SomeCriteria
WHERE SomeCriteria  
  AND t4.ColumnA = (SELECT DISTINCT ColumnX
                      FROM db2.schema.TableName5 with (nolock) 
                      WHERE ColumnY = 2902)

Please: This is not my query... Do not discuss the usage of WITH(NOLOCK) or GROUP BY against DISTINCT, thx :-D

Users reported time-outs due to run durations of more than a minute. I could not reproduce this, as exactly the same query in exactly the same environment brought back exactly the same result in less than a second.

Then - by setting all possible configurations to the same values - I encountered something strange: The difference depends on the database in use.

This is reproducable: If there is USE master; it is 1 second, with USE db1; (or any other) it is horribly slow.

Some general observations

  • All used tables are fully qualified with database.schema.table and aliased
  • All tables are called with WITH(NOLOCK)
  • The profiler for the fast run shows CPU(30), Reads(20000), Duration(30)
  • slow (same SSMS, just two windows): CPU(11000), Reads(13M!!!), Duration(12000)
  • The execution plans are extremely different
    • slow: Starts with an index seek returning 2.6M rows
      Combines this with another index seek pushing this to 27M rows (estimnated 45!)
      Filters this down to 1626 rows, which is the count of the final result
    • fast: starting with tiny sets of some 100 rows, Never more than 8000 rows

My questions

  • What is going on here? Why is the database, where I'm coming from, so important?

Lessons learned and a solution

What I did not know: Query plans are stored with each database separately, hence the context database can be very important. I cannot really grasp the advantage of this concept... Why not better store the plans in a central place, one per action? But this is a different question...

Using a query against sys.dm_exec_cached_plans and dm_exec-query_plan() with query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"Some specific part of the query")]')=1 I found a couple of stored plans. After removal everything was fast and fine.

But the next day the bad behavior was back.

The solution: After a thorough look into the best plan I re-organised all JOINs to this order of execution and use OPTION(FORCE ORDER). This seems to solve the issue.

  • 1
    SQL auth login? Any differences between permissions/roles on different databases? What happens if you clear the plan cache and then query from the slow database (or add OPTION (RECOMPILE))? (My guess is that you have a cached plan for each database context, and you happened to get - and are now stuck with - a slow one when you were connected to the slow database.) – Aaron Bertrand Apr 9 '18 at 14:17
  • @AaronBertrand, There is no difference wheter the login is SQL or Windows. Both windows run the same query under the same user against the same environment. The only difference is the used database. (I hope I did not miss anything though) – Shnugo Apr 9 '18 at 14:19
  • @AaronBertrand to avoid any misinterpretation: Just one single window, one single SELECT. With USE master; less than a second, with USE OtherDb; more than a minute. Nothing else changed... The reason seems to be, that in one case the optimizer finds a well performing route and in the other case it runs a terrible road... – Shnugo Apr 9 '18 at 14:24
  • Those can still have different plans associated - it doesn't matter if it's in the same window or not. Did you try any of the things I suggested? – Aaron Bertrand Apr 9 '18 at 14:34
  • @AaronBertrand Might be I get this wrong... Everything is running against the same instance of SQL-Server, there is no "slow" or "fast" database... I thought, that plans are one per sql-server or are there different plans for different databases onm the same server? – Shnugo Apr 9 '18 at 14:39
1

At a high level, database context is one of the keys that make a cached plan unique (there are other things, like certain session settings, the query text obviously, etc).

Therefore, it is quite possible that the first time this query was run in one database, it compiled (and is now stuck with) a very different plan than when it was compiled for some other database.

There are many reasons why a plan could be different, depending what happened between the first compilation and any subsequent compilations, but now that you have two plans, it is irrelevant that you are issuing the USE commands in the same window. You're still going to use the plan that was compiled for that database. Quick repro:

USE AdventureWorks;
GO
SELECT TOP (1) Monkey = BusinessEntityID 
  FROM AdventureWorks2012.Person.Person;
GO
USE OtherDB;
GO
SELECT TOP (1) Monkey = BusinessEntityID 
  FROM AdventureWorks2012.Person.Person;
GO
SELECT * FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%Monkey%';

You get three rows - ignore the first, because that is just the query I ran to verify (which is cached before execution, so adds itself to the resultset), and keep in mind if you run a zoo database or work at SurveyMonkey, you may have to filter further:

enter image description here

Clearly you can see we got a plan for AdventureWorks and a plan for OtherDB, even though this is the same instance of SQL Server. Again, I can't go back in history and tell you why or when you got two different plans, but it's obvious to me that this is a possible explanation. Easy for you to verify, of course.

So, one more time, please try issuing the query with OPTION (RECOMPILE) when you USE <slowdatabase>;, or otherwise evicting those specific plans from the cache and trying again:

DBCC FREEPROCCACHE(0x...); -- where 0x... is the plan_handle
  • If someone tells you something that doesn't jive with your assumptions, give them the benefit of the doubt and try it. Point taken.. Just trying to understand this... Added OPTION (RECOMPILE). No difference... Still a huge difference between USE master and USE Other. This is a naked SELECT (not any kind of pre-compiled object). I even changed the query's text to enforce a new plan. Same behavior... – Shnugo Apr 9 '18 at 14:58
  • Did you look at the actual plans yet? Are they different? – Aaron Bertrand Apr 9 '18 at 14:59
  • As written in the question (hope I understand you correctly): Executed with "include actual execution plan" I get very different plans. Just checked the sys.dm_exec_cached_plans and dm_exec-query_plan() with query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"SELECT goord.Transaktions_ID")]')=1 and get 17 rows back (all type "Adhoc"). Shoudl I try to throw them away? – Shnugo Apr 9 '18 at 15:04
  • Yes, you can use DBCC FREEPROCCACHE(0x...); where 0x... is the plan_handle. – Aaron Bertrand Apr 9 '18 at 15:07
  • After deleting all related plans it seems to be much faster now in all cases. I have to test this in the deep. Thank you very much! I have learned a ton today... – Shnugo Apr 9 '18 at 15:21

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.