1

Topic: [SQL Server] Plan guide for temporary tables

Hello. Faced a problem when on a SQL Server 2008R2  request INSERT WITH (TABLOCK) kills itself on DEAD LOCK'. Thought to make plan guide with MAXDOP (1), but the request uses the temporary table and each time with a new name (the request thus does not change). Whether probably to parametrize a table name? If is not present, whether there is any variant to affect this request without global job MAXDOP? (The Request forms 1, 1 assure what to modify him from outside 1 to add OPTION (MAXDOP 1) it is impossible)

2

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> Hello. S> faced a problem when on a SQL Server 2008R2  request INSERT WITH (TABLOCK) kills itself on DEAD LOCK'. You can  put the graph?

3

Re: [SQL Server] Plan guide for temporary tables

Hello, _ABC _, you wrote: S>> faced a problem when on a SQL Server 2008R2  request INSERT WITH (TABLOCK) kills itself on DEAD LOCK'. _AB> you Can  put the graph? http://files.rsdn.org/103613/1C-Deadlock.zip I had affairs with  a little, therefore easily I can be mistaken in the reasons. But  all at performance of this request, always.

4

Re: [SQL Server] Plan guide for temporary tables

5

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> I had affairs with  a little, therefore easily I can be mistaken in the reasons. But  all at performance of this request, always. The plan looked? Perhaps, it can be optimized indexes, or to rewrite normally? And now what MAX DOP costs at you?

6

Re: [SQL Server] Plan guide for temporary tables

7

Re: [SQL Server] Plan guide for temporary tables

S> faced a problem when on a SQL Server 2008R2  request INSERT WITH (TABLOCK) kills itself on DEAD LOCK'. Anybody did not kill itself(himself), you simply are not able to read  At you  went through 2 different connections, and not through the temporary table and through dbo. _ Documents506 1 fulfilled INSERT INTO dbo. _ Document506 (_IDRRef,_Marked,_Date_Time,_NumberPrefix,_Number,_Posted,_Fld14332RRef,_Fld26341RRef,_Fld26332,_Fld26325,_Fld14338RRef,_Fld14344RRef,_Fld26330,_Fld26326,_Fld26327,_Fld26340RRef,_Fld14333_TYPE,_Fld14333_RTRef,_Fld14333_RRRef,_Fld14342,_Fld14343,_Fld26324,_Fld26335,_Fld26331,_Fld26329,_Fld29222RRef,_Fld14335,_Fld26336,_Fld14334,_Fld26328,_Fld14339RRef,_Fld26333,_Fld26334,_Fld26339,_Fld26337,_Fld26338,_Fld14340,_Fld14336,_Fld14341RRef,_Fld14337,_Fld22664,_Fld27288,_Fld28824,_Fld29223,_Fld30259,_Fld30940RRef,_Fld30941RRef,_Fld31152,_Fld31153,_Fld31154,_Fld31155,_Fld31156,_Fld31157,_Fld31158,_Fld32392,_Fld33478) 2 INSERT INTO #tt15 WITH (TABLOCK)... No plan  is necessary to you, switch basis in RCSI ALTER DATABASE [UPP] SET READ_COMMITTED_SNAPSHOT ON

8

Re: [SQL Server] Plan guide for temporary tables

Hello, Sammo, you wrote: S> In remaining cases - means 1 - let define in what place there is a lock and let understand - that there becomes. For example,  collection of residuals from document carrying out. For such council it is already possible to cut off hands. Slowly. On a phalanx. And so to the bottom, whence they also grow avalon/2.0.3

9

Re: [SQL Server] Plan guide for temporary tables

Hello, rm822, you wrote: R> Anybody did not kill itself(himself), you simply are not able to read  Well, such report is difficult enough for reading, for the sake of justice. It is too encumbered. In remaining you are right is a conflict on _Document506 R> any plan  to you is not necessary, switch basis in RCSI Khm... You it... Did not advise so categorically. Since, at first 1 (as, however, and other applications) not always without serious consequences endures switching-on of this mode. From server side 1 the particular treatment of control of locks (from what version 1 it is supported, I do not remember) should be included. The zaporchennye data where is worse, than . Secondly, 1 at server restart 1 returns basis in mode RC if the mode of controlled locks has not been included. It is much better and more safe to rewrite request. Change the order of reversal to tables 1 can.

10

Re: [SQL Server] Plan guide for temporary tables

11

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> Not, actually setting MAXDOP=1 did not help. The problem was in the request, all dared its rewriting. So I also want to understand, in what there was a problem. Show request in 1S th type before and after.

12

Re: [SQL Server] Plan guide for temporary tables

DC> For such council it is already possible to cut off hands. Slowly. On a phalanx. And so to the bottom, whence they also grow I meant the new policy on check of residuals from recommendations 1, i.e. not in carrying out presence of residuals is checked, and the result is led and looks. It has been made including for reduction of locks.

13

Re: [SQL Server] Plan guide for temporary tables

Hello, Sammo, you wrote: S> DC> For such council it is already possible to cut off hands. Slowly. On a phalanx. And so to the bottom, whence they also grow S> I meant the new policy on check of residuals from recommendations 1, i.e. not in carrying out presence of residuals is checked, and the result is led and looks. It has been made including for reduction of locks. You absolutely misunderstood this new policy. avalon/2.0.3

14

Re: [SQL Server] Plan guide for temporary tables

Hello, wildwind, you wrote: W> Hello, Somescout, you wrote: S>> Not, actually setting MAXDOP=1 did not help. The problem was in the request, all dared its rewriting. W> so I also want to understand, in what there was a problem. Show request in 1S th type before and after. http://files.rsdn.org/103613/1CQuery.zip

15

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> http://files.rsdn.org/103613/1CQuery.zip I Notepad ++ could not  this coding.

16

Re: [SQL Server] Plan guide for temporary tables

Hello, wildwind, you wrote: W> Hello, Somescout, you wrote: S>> http://files.rsdn.org/103613/1CQuery.zip W> I Notepad ++ could not  this coding. Strange (if it not sarcasm), like it normal Win1251. These files even normal notepad understands.

17

Re: [SQL Server] Plan guide for temporary tables

Hello, _ABC _, you wrote: _AB> Hello, Somescout, you wrote: S>> I had affairs with  a little, therefore easily I can be mistaken in the reasons. But  all at performance of this request, always. _AB> the Plan looked? Perhaps, it can be optimized indexes, or to rewrite normally? _AB> and now what MAX DOP costs at you? Change MAX DOP did not help, the request rewriting helped.

18

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> Change MAX DOP did not help, the request rewriting helped. Apropos I already wrote a rewriting of request. But the question remained actual. To it the question on is added what processors stand on the DBMS server.

19

Re: [SQL Server] Plan guide for temporary tables

20

Re: [SQL Server] Plan guide for temporary tables

Hello, _ABC _, you wrote: _AB> Hello, Somescout, you wrote: S>> Change MAX DOP did not help, the request rewriting helped. _AB> apropos I already wrote a rewriting of request. But the question remained actual. About the plan? No, did not look - the problem that in these requests earlier created temporary tables are often used, therefore to capture entirely the plan with all requests heavy. Plus these tables are kindly cleared 1 on termination of handling that complicates the analysis even more. _AB> to it the question on is added what processors stand on the DBMS server. 4xXeon E-8880v3, 1 RAM.

21

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> About the plan? About value Max DOP at server level.

22

Re: [SQL Server] Plan guide for temporary tables

Hello, _ABC _, you wrote: _AB> Hello, Somescout, you wrote: S>> About the plan? _AB> about value Max DOP at server level. MaxDOP=1 tried to put, did not help.

23

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: _AB>> About value Max DOP at server level. S> MaxDOP=1 tried to put, did not help. Really so difficult directly to answer directly brought attention to the question?

24

Re: [SQL Server] Plan guide for temporary tables

Hello, _ABC _, you wrote: _AB> Hello, Somescout, you wrote: _AB>>> About value Max DOP at server level. S>> MaxDOP=1 tried to put, did not help. _AB> really so difficult directly to answer directly brought attention to the question? Was 0. Tried with 16 and 1.

25

Re: [SQL Server] Plan guide for temporary tables

Hello, Somescout, you wrote: S> Was 0. Tried with 16 and 1. As curiosity and the general development. Here that is not the question specific request, and in normal adjustment of the server as a whole. Why refused following best practices? There was any reason to leave max DOP in 0?