0

Why does a complex SQL query run worse with a Use statement and implicit dB references than with a Use master statement and full references to the user dB?

I'm using SQL Server Std 64-bit Version 13.0.4466.4 running on Windows Server 2012 R2. This is an "academic" question raised by one of my users, not an impediment to production.

By "complex" I mean several WITH clauses and a CROSS APPLY, simplified query structure below. By "worse" I mean 3 min. vs. 1 sec for 239 Rows, repeatably. The "plain" Exec Plan for fast query will not show, however, the Exec Plan w/ Live Query Stats runs for both, analysis further below. Tanx in advance for any light shed on this!

USE Master versus USE <userdb>;
DECLARE @chartID INTEGER = 65; 

WITH 

with1 AS 
(  SELECT stuff FROM <userdb>.schema1.userauxtable ),

with2 AS 
(  SELECT lotsastuff  FROM <userdb>.dbo.<views w/ JOINS> ), 

with3 AS
(  SELECT allstuff FROM with2 WHERE TheDate IN (SELECT MAX(TheDate) FROM with2 GROUP BY <field>, CAST(TheDate AS DATE)) ),

with4 AS 
(  SELECT morestuff  FROM with1 WHERE with1.ChartID = @chartID )

SELECT finalstuff FROM with3 
  CROSS APPLY ( SELECT littelstuff  FROM with4 WHERE 
  with3.TheDate BETWEEN with4.PreDate AND with4.AfterDate 
  AND with4.MainID = with3.MainID ) as AvgCross

The Exec Plan w/ Live Query Stats for slow query has ~41% Cost ea. (83% total) in two ops:

a) Deep under the 5th Step (of 15) Hash match (Inner Join) Hash Keys Build ... 41% Cost to Index Scan (non-clustered) of ...

b) Very deep under the 4th Step (of 15) Nested Loops (Left Semi Join) -- 42% Cost to near-identical Index Scan per (1) except addition of (... AND datediff(day,Date1,getdate() ) to Predicate.

While the Exec Plan w/ Live Query Stats for fast query shows an 83% Cost in a Columnstore Idx Scan (non-clustered) of quite deep under the 9th Step (of 12) Hash match (Inner Join) Hash Keys Build .

It would seem that the difference is in the Columnstore Idx, but why does the Use master stmt send the Execution down that road?

Improve this question
3
  • see stackoverflow.com/questions/17904642/… Commented Dec 4, 2019 at 21:07
  • Alex, tanx for the Stack Overflow article link. a) The reason our shop heavily prefers to use implicit dB references is so that we can rapidly and accurately move code across our various test regions and then to the live region. b) This particular piece of code is schema-qualified (i.e: two-part names). c) Tried running the Use <userdb> implicit dB reference-version a few times in a row and got the near-exact same slow performance, that is, no benefit from lack of compilation overhead in subsequent tries. Commented Dec 6, 2019 at 22:31
  • no problem, it reminded me of another thing: bear in mind that running the same code in different boxes might yield performance differences even if the boxes are the same hardware-wise. For example: if you have queries without order by or order by without being deterministic, the order is not GUARANTEED and SQL might start doing things their way. Commented Dec 6, 2019 at 23:31

1 Answer 1

Reset to default
1

There may be several possible reasons for this kind of behaviour; however, in order to identify them all, you will need people like Paul Randall or Kalen Delaney to answer this.

With my limited knowledge and understanding of MS SQL Server, I can think of at least 2 possible causes.

1. (Most plausible one) The queries are actually different

If, as you are saying, the query text is sufficiently lengthy and complex, it is completely possible to miss a single object (table, view, user-defined function, etc.) when adding database qualifiers and leave it with no DB prefix. Now, if an object by that name somehow ended up in both the master and your UserDB databases then different objects will be picked up depending on the current database context, the data might be different, indices and their fragmentation, even data types... well, you get the idea.

This way, queries become different depending on the database context, and there is no point comparing their performance.

2. Compatibility level of user database

Back in the heyday of the 2005 version, I had a database with its compatibility level set to 80, so that ANSI SQL-89 outer joins generated by some antiquated ORM in legacy client apps would keep working. Most of the tasty new stuff worked too, with one notable exception however: the pivot keyword.

A query with PIVOT, when executed in the context of that database, threw an error saying the keyword is not recognised. However, when I switched the context to master and prefixed everything with user database's name, it ran perfectly fine.

Of course, this is not exactly your case, but it's a good demonstration of what I'm talking about. There are lots of internal SQL Server components, invisible to the naked eye, that affect the execution plan, performance and sometimes even results (or your ability to retrieve them, as in the example above) that depend on settings such as database' compatibility level, trace flags and other similar things.

As a possible cause, I can think of the new cardinality estimator which was introduced in SQL Server 2014. The version of the SQL Server instance you mentioned corresponds to 2016 SP1 CU7, however it is still possible that:

  • your user database may be in compatibility with 2012 version (for example, if it was restored from 2012 backup and nobody bothered to check its settings after that), or
  • trace flag 9481 is set either for the session or for the entire SQL Server instance, or
  • database scoped configuration option LEGACY_CARDINALITY_ESTIMATION is set for the database, etc.

(Thankfully, SQL Server doesn't allow to change compatibility level of the master database, so it's always of the latest supported level. Which is probably good, as no one can screw the database engine itself - not this way, at least.)

I'm pretty sure that I have only scratched the surface of the subject, so while checking the aforementioned places definitely wouldn't hurt, what you need to do is to identify the actual cause of the difference (if it's not #1 above, that is). This can be done by looking at actual execution plans of the queries (forget the estimated ones, they are worthless) with a tool other than vanilla SSMS. As an example, SentryOne Plan Explorer might be a good thing to begin with. Even without that, saving plans in .sqlplan files and opening them with any XML-capable viewer/editor will show you much more, including possible leads that might explain the difference you observe.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Roger Wolf, tanx for the suggestion to check for "overriding" explicit dB references mixed in w/ the implicit references, however, there are none. And, per Alex' article ref, this particular piece of code is schema-qualified (i.e: two-part names).
@T-Bone, good, but the suggestion to investigate execution plans in more detail still stands. There must be a reason behind this performance difference, after all.
Roger, plz also note the following: compatibility_level of <userdb> is 100, however, the Legacy Cardinality Estimation is OFF. The compatibility_level of master is 130. The <userdb> compatibility_level of 100 is mostly due to us being careful as we nudge our originally-SQLServer7 dB forward. Per one of the articles, I added OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); to the end of the fast (master/explicit dB references) query and it still ran fast.
Also, tanx for the tip on saving Exec Plans as .sqlplan files and opening them in XML. I'll give that a try, but fear that I may only find what I already see in the Columnstore Idx prep, but not how that was called for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.