1

Topic: All and or problems with procedure compilation

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)
Sep 23 2016 4:56:29 PM
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600:)
I watch a puzzle
1. There is a procedure.
2. Suddenly, this procedure began to hang on start. I.e.  does nothing and . Hour three I waited. Typical time of execution - 10 seconds
3. And the impression -  is not launched. I.e. hangs at a compilation stage.
4. Started to understand.  contents.
5. It was launched.
6. A method of division of a segment in halves calculated

with f as (select * from TourML.SpoFiles)
, fl as (select * from f where is_directory = 0 and file_type = N'xml ' and name = @name)
, fo as (select * from f where is_directory = 1 and name = @dir and parent_path_locator is null)
, x as (select path_locator = isnull (fl.path_locator
, fo.path_locator. GetDescendant ((select max (path_locator) from f where parent_path_locator = fo.path_locator), null)
)
, name = @name
, file_stream = cast (@XML as varbinary (max))
, creation_time = @now
from fo left outer join fl on fl.parent_path_locator = fo.path_locator
)
merge f using x on f.path_locator = x.path_locator
when not matched then insert (path_locator, name, file_stream, creation_time) values (path_locator, name, file_stream, creation_time)
when matched and (f.file_stream <> x.file_stream) then update set file_stream = x.file_stream, creation_time = x.creation_time
;

7. You make comments - works. You remove the comment - hangs.
8. This  in test start  is not executed.
9. I.e. problems in compilation.
10. A method scientific  clarified

-- We hang (i.e. not  at all)
when matched and (f.file_stream <> x.file_stream) then update set file_stream = x.file_stream, creation_time = x.creation_time
-- We work (i.e. we are launched)
when matched then update set file_stream = x.file_stream, creation_time = x.creation_time

11. Once again I remind: GIVEN  In TEST START it is not executed.
What for a miracle?

2

Re: All and or problems with procedure compilation

! Caution
It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

3

Re: All and or problems with procedure compilation

Massa52 wrote:

! Caution
It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.

And now state by the own words.
For what relation has "ON clause" to quite documented . To the filter in "when matched"?
If you read the documentation "when matched"  it is possible to use some times with different filters.

4

Re: All and or problems with procedure compilation

What during time  for this session in sys.dm_os_waiting_tasks?

5

Re: All and or problems with procedure compilation

invm wrote:

That during time  for this session in sys.dm_os_waiting_tasks?

Did not look, but  I will look...

6

Re: All and or problems with procedure compilation

 waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
0x000000083988E558 164 0 0 PREEMPTIVE_OS_FILEOPS NULL NULL NULL NULL NULL 

7

Re: All and or problems with procedure compilation

And the server version what? In early implementations merge was very much  - it is remembered, I on 2008R2 somehow ran on absolutely not clear, and nothing an error speaking by the message at attempt  tables in which there was a calculated field (certainly, in  there were no attempts this field to change). Probably, and the aforesaid from the same area.

8

Re: All and or problems with procedure compilation

Additional messages from fields!
If to fulfill
select * from sys.dm_os_waiting_tasks where session_id = 164
That a line that appears, disappears...
I.e. it is not necessary - it does something.

9

Re: All and or problems with procedure compilation

Very Pavlovny wrote:

And the server version what? In early implementations merge was very much  - it is remembered, I on 2008R2 somehow ran on absolutely not clear, and nothing an error speaking by the message at attempt  tables in which there was a calculated field (certainly, in  there were no attempts this field to change). Probably, and the aforesaid from the same area.

aleks222 wrote:

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)
Sep 23 2016 4:56:29 PM
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600:)

10

Re: All and or problems with procedure compilation

preemptive_os_fileops - Waiting at operations with file system.
It is necessary to understand with readings from filestrem.

11

Re: All and or problems with procedure compilation

invm wrote:

preemptive_os_fileops - waiting at operations with file system.
It is necessary to understand with readings from filestrem.

About the wisest!
Business does not reach reading.
I on the white wrote to Russian:  it is not launched. I.e. hangs at a compilation stage.

12

Re: All and or problems with procedure compilation

For greater clearness:
We put at the very beginning of procedure, TO all operators
return
- And it hangs at start.
If to make mentioned above change - works instantly.

13

Re: All and or problems with procedure compilation

When  - can all variants even if in a current dial-up of parameters the given branch is not used are fulfilled.
I.e.  at compilation it "comes" into this branch.
And yes like in  it is possible to look, if  really hangs up on compilation.

14

Re: All and or problems with procedure compilation

aleks222;
Try to reduce an amount of asterisks by square kilometer. And as a whole self-referensov somehow . Thus that you begin and you finish "constants".

15

Re: All and or problems with procedure compilation

aleks222;
sp_recompile Too hangs?

16

Re: All and or problems with procedure compilation

So, it is pure for the sake of experiment
What will be, if so to try?

when matched and exists (select f.file_stream except select x.file_stream) then update set file_stream = x.file_stream, creation_time = x.creation_time

17

Re: All and or problems with procedure compilation

aleks222 wrote:

[business does not reach reading.
I on the white wrote to Russian:  it is not launched. I.e. hangs at a compilation stage.

Means in compile process reaches even any operation with file system.
Miracles there is no also a waiting for no reason does not arise.
It is possible to take Process Monitor and to try to clarify a place .

18

Re: All and or problems with procedure compilation

dies irae wrote:

so, it is pure for the sake of experiment
What will be, if so to try?

when matched and exists (select f.file_stream except select x.file_stream) then update set file_stream = x.file_stream, creation_time = x.creation_time

The same effect.

19

Re: All and or problems with procedure compilation

invm wrote:

1. Means in compile process reaches even any operation with file system.
Miracles there is no also a waiting for no reason does not arise.
2. It is possible to take Process Monitor and to try to clarify a place .

1. Well, certainly... Strange current, and here inequality compilation? It is direct at once file system it was necessary...
2. It is possible. But a lack of time. To me and without an inequality descends.

20

Re: All and or problems with procedure compilation

aleks222 wrote:

merge f using x on f.path_locator = x.path_locator
when not matched then insert (path_locator, name, file_stream, creation_time) values (path_locator, name, file_stream,
creation_time)

It is impossible to do insert values. Does not understand such the compiler in normal way.  it is necessary to be perverted.
Taki to do select from and to whom it is now easy ...

21

Re: All and or problems with procedure compilation

Andy_OLAP;
From what ?
Where under the link something about "insert-values does not not work"?

22

Re: All and or problems with procedure compilation

procedure wrote:

Andy_OLAP;
From what ?
Where under the link something about "insert-values does not not work"?

I  quite kosher resulted the link. If you think that query compilation is a task in which in Redmond the American professors I will strongly disappoint you are engaged. If there will be a possibility - find Haljako, give to drink it properly and ask its personal impressions about a command of Hindus (Arvind, Rahesh, Shiva, Sanzhaj) if you think that Morgan forces them to use normal decisions at the analysis of text T-SQL of request is a superfluous optimism.

23

Re: All and or problems with procedure compilation

There is a procedure;
In the link about path_locator. It is not necessary to think that it is possible to write with impunity any T-SQL the code. The easier - the better.

24

Re: All and or problems with procedure compilation

aleks222 wrote:

I watch a puzzle
10. A method scientific  clarified
[code]
-- We hang (i.e. not  at all)
when matched and (f.file_stream <> x.file_stream) then update set file_stream = x.file_stream, creation_time = x.creation_time
-- We work (i.e. we are launched)
when matched then update set file_stream = x.file_stream, creation_time = x.creation_time

Well because the compiler tries to go on the way Full Outer Join. And it is impossible to it. Almost month already solve the task with FileTable, the colleague. Deliver at least CU2 for SP2, renew the version to 11.0.5548.0. See, how life becomes easier, without sarcasm and jokes, sincerely I advise.

25

Re: All and or problems with procedure compilation

Andy_OLAP wrote:

it is passed...
Well because the compiler tries to go on the way Full Outer Join. And it is impossible to it. Almost month already solve the task with FileTable, the colleague. Deliver at least CU2 for SP2, renew the version to 11.0.5548.0. See, how life becomes easier, without sarcasm and jokes, sincerely I advise.

Remove it . Though... It can and the Cheburashka?
. , do not learn me to live. Learn to read is better.