1

Topic: The session identifier locks itself

Good afternoon, respected !
The trouble came - did not note and as.
Essence in the following:
I have my basis in which many my stored procedures.
One of them address, both to bases on the same server, and to  to the server. Thus on the current server the request on all bases becomes quickly enough (3 minutes approximately).
All trouble begins, when he addresses to  to the server.
On it it comes under specially created , with all rights.
Till today's Monday all worked for me and collected. Approximately 30 minutes
And now the following began - waited for 5 hours.
Came on the second server and opened the activity monitor. In processes formed a heap of identical processes.
The part from them with state Raning, part Ranable, and is a lot of Suspended. (In the enclosed file a screen)
Thus the last are locked with the same identifier.
CPU time does a saltus and permanently supports great value.
Collects nothing.
In expectation of resources Buffer I/O a maximum.
Anything in adjustments of the server did not change.
Anybody too does not confess.
From noted changes:
1. Someone delivered Kaspersky the server 10 console (deleted, changed nothing);
2. For some reason in adjustments  servers changed  under what to come on it (too nobody confesses that made something)
Prompt where to dig please concerning an error...

2

Re: The session identifier locks itself

3

Re: The session identifier locks itself

Ejik43;
Versions of both sequels result,

select @@ version

On lock: at you parallel reading of the data from the table (CXPACKET wait), one of  waits for the termination of operation of the remaining. It looks as lock itself.

4

Re: The session identifier locks itself

komrad wrote:

Ejik43;
Versions of both sequels result,

select @@ version

On lock: at you parallel reading of the data from the table (CXPACKET wait), one of  waits for the termination of operation of the remaining. It looks as lock itself.

Well CXPACKET it not only parallelism, but  is similar, MAXDOP did not change? Statisticans here too can influence

5

Re: The session identifier locks itself

6

Re: The session identifier locks itself

Ejik43;
let out, but we will not put them certainly

7

Re: The session identifier locks itself

Ejik43;
If at you changed  in  the server check up its current rights in basis  servers
http://thomaslarock.com/2013/05/top-3-p … r-queries/

8

Re: The session identifier locks itself

TaPaK;
I would answer for them. Not .

9

Re: The session identifier locks itself

komrad;
Rechecked at  the rights sysadmin.
MAXDOP on the server with which I launch 12
On  0

10

Re: The session identifier locks itself

Ejik43 wrote:

komrad;
Rechecked at  the rights sysadmin.
MAXDOP on the server with which I launch 12
On  0

Well if so and provided that the same and configuration parameters of servers nobody touched requests I would look at statistics update on  to basis

11

Re: The session identifier locks itself

TaPaK wrote:

Ejik43;
let out, but we will not put them certainly

Presence service-paka mandatory to its setting?
At least, it is necessary to attend, and whether he solves problems which arise at business.
If such problems generally are.
Works - do not touch ()

12

Re: The session identifier locks itself

- wrote:

it is passed...
Presence service-paka mandatory to its setting?
At least, it is necessary to attend, and whether he solves problems which arise at business.
If such problems generally are.
Works - do not touch ()

For 2008? Unambiguously

13

Re: The session identifier locks itself

Badger-kopatel;
You believe, what they write about everything, what has been corrected?

14

Re: The session identifier locks itself

Kolosov wrote:

the Badger-kopatel;
You believe, what they write about everything, what has been corrected?

Good afternoon, Vladislav!
And what that changes?
If on  there is a problem and its decision is in service-pake it is torn the test environment and all is checked.
Only after that it is updated .
And it is not important, whether there corresponds a reality service-paka to its description.

15

Re: The session identifier locks itself

Ejik43 wrote:

2. For some reason in adjustments  servers changed  under what to come on it (too nobody confesses that made something)

1. Only in 2012 corrected  a type:
uchetka-nesisadmin has no rights to statistics of the involved tables .
Therefore check up, whether was former  the system administrator on
Also return the rights  if put in it
2. 2008 R2 SP1 still that infection, at us too it costs.
In all requests where there is a connection with the table  servers;
In Remote Query he adds order by on connection columns 8-o
Even if between tables, local and remote, in the plan flaunts hash join.
At me also local 2008 R2 costs, but SP3.
And so it order by does not mold this.
In descriptions SP2, SP3 like it is not present, at least, I did not manage to find.
But nevertheless, in SP3 it is corrected

16

Re: The session identifier locks itself

I thank All!!!
Forced to rearrange on SP3.  users on-new. Earned normally.

17

Re: The session identifier locks itself

Ejik43 wrote:

Peresozdal of users on-new.

It also was the decision. There was a similar problem. Removal and creation anew  solved.