question

ChuckHamilton-7817 avatar image
0 Votes"
ChuckHamilton-7817 asked Cathyji-msft commented

SQL 2017 never generates execution plan

Why would SQL 2017 never generate an execution plan for a query? The query is against a view. If I run the query that defines the view, it completes in under a minute. If I query the view it never completes and I observe the following...

  1. dm_exec_requests shows no plan handle

  2. sp_whoiactive shows no plan handle or execution plan

  3. If I try to watch live plan execution all I get is a message that says "Waiting for execution plan".

  4. The session's logical reads is not increasing but CPU usage is.

Is this a bug? If so I've not been able to find it online.

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

You are running a very old patch level. It may have nothing to do with your issue, but I would highly suggest you install the current CU.

https://docs.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level

What you describe is almost always due to a very complicated join pattern, views to views to views to tables or using the same tables multiple times.

The current patch may help.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChuckHamilton-7817 avatar image
0 Votes"
ChuckHamilton-7817 answered ErlandSommarskog commented

Its hard to believe it could take hours to generate an execution plan and not see wait on the query compile gateways. I wish I could apply the latest patch but its a production system and cant be taken down.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you see any waits for this session?

0 Votes 0 ·

The wait type is always null as is the plan_id in dm_exec_requests.

If its compiling a plan I would expect it to be waiting on a RESOURCE_SEMAPHORE* type wait. i think the behavior must have changed in recent versions.

0 Votes 0 ·

I've should have been more precise. As Cathy said, you should look in sys.dm_exec_session_wait_stats, filtered by the session_id. Try the compile on a new connection, so that all waits you see are related to the connection.

If it is not waiting, maybe it is burning CPU like crazy?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft commented

Hi @GabeCampos-0034,

Why would SQL 2017 never generate an execution plan for a query?

Could you share us the query that you executed?

If I query the view it never completes

Did you try to use sys.dm_exec_session_wait_stats query to check what is the session wait for?


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Its a simple query against a view with about 10 joins in it - both inner and outer joins. If I use the parenthesis technique to force the join order it creates an execution plan in < 1 second and completes in about a minute.

The parenthesis technique was something I recently discovered to simulate the FORCE ORDER hint within a view's definition...

SELECT ...
FROM (
t1
join T2 on ....
join T3 on ...
)
WHERE ...

I guess that limits the choices the optimizer has to decide between and lets it generate a plan.


0 Votes 0 ·

Its a simple query against a view with about 10 joins in it - both inner and outer joins.

That does not count as a simple query. The view is just a macro.

The parenthesis technique was something I recently discovered to simulate the FORCE ORDER hint within a view's definition...

I am not aware of that using parentheses implies FORCE ORDER. But it could lead to that the internal query tree gets a different shape, and which can lead to SQL Server trying different transformations if the parens are not there. But, ideally, they should have no effect at all.

But obviously the parentheses serves as a workaround here. At least for the moment.

0 Votes 0 ·

I found it on Brent Ozar's site.

0 Votes 0 ·

Hi @ChuckHamilton-7817,

Could you share us the result of sys.dm_exec_session_wait_stats query?

0 Votes 0 ·