Scheduler无法用于查询管理

当场报错如下:

现场报错如下:

转:

有1台SQL Server(SQL Server 201四标准版)服务器中的scheduler_count与cpu_count不均等,如下截图所示:

Process 0:0:0 (0x1ffc) Worker 0x00000001E580A1A0 appears to be non-yielding on Scheduler 3. Thread creation time: 13153975602106. 
Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 21%. System Idle 76%. Interval: 73616 ms.
Process 0:0:0 (0x1ffc) Worker 0x00000001E580A1A0 appears to be non-yielding on Scheduler 3. Thread creation time: 13153975602106. 
Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 21%. System Idle 76%. Interval: 73616 ms.

 

SELECT  cpu_count ,

        scheduler_count 

FROM    sys.dm_os_sys_info;

从报错来看是某个worker占用scheduler不释放导致,服务道具体的scheduler个数能够从sys.dm_os_sys_info.scheduler_count列中来看,一般的话服务器也就那些核数,在这之中假设被non-yeilding的worker永恒占用部分,将可能引致非常的惨重的习性难点。引发此难题的因由平时是少数查询并行度非常高的SQL,也与七个BUG有关,近年来从局地文书档案来看,此BUG并未有被解决,详见:

从报错来看是有个别worker占用scheduler不释放导致,服务器物体的scheduler个数能够从sys.dm_os_sys_info.scheduler_count列中看看,一般的话服务器也就那些核数,其中借使被non-yeilding的worker恒久占用部分,将恐怕引致相当惨重的习性难题。引发此主题素材的案由平常是某个查询并行度极高的SQL,也与三个BUG有关,如今从局地文书档案来看,此BUG并没有被化解,详见:

By Pankaj
Tanwar on October 1,
2011
in Coding, Developers · 2
Comments

997755.com澳门葡京 1

从二〇〇九年SQL
Server200伍起始,到20一七年6月SQL Server
2016本子那个主题素材依旧被报告,微软尚未提交明显的解决办法。

从二〇一〇年SQL
Server200伍伊始,到20壹7年十月SQL Server
201陆版本这些问题照旧被报告,微软从不提交鲜明的化解办法。

997755.com澳门葡京 2

 

从而近日不得不在产出难点后迅即搜索相关的SQL优化,并且通过修改查询调整器大概并行查询阈值来制止难题应运而生。

所以近年来只能在产出难题后马上找寻相关的SQL优化,并且通过更改查询调节器只怕并行查询阈值来防止难题出现。

In
this part of the series, we will learn how to deal with multiple clients
connected to the server.

SQL
Server中Scheduler数量应该与逻辑CPU的核数1致,而sys.dm_os_sys_info中的scheduler_count
为8,少于cpu_count的拾一个数据,那么很有相当大也许,有一些Scheduler的状态为VISIBLE
ONLINE.上边摘自Healthy SQL: A Comprehensive Guide to Healthy SQL
Server Performance

从官方网站的分解来看:那是七个微软winsock
API本身的BUG。

从官方网址的解说来看:那是一个微软winsock
API本身的BUG。

Welcome
to another dose of socket programming! Till now, we’ve created servers
that are capable of creating connections with multiple clients [Part
1 & Part
2],
but the problem is that the server will communicate with only one client
at any point in time. This is because there is only one socket
descriptor, cfd, created to communicate with a client — and all
connections will wait on the same descriptor. Now, let’s use
the fork() system call to fork a copy of the server for each
client.

997755.com澳门葡京 3

 

 

Here is
the code included from the previous article. This time it is for
IPv4…

SELECT  is_online 

       ,[status]

       , COUNT(*) AS [count]

FROM sys.dm_os_schedulers

WHERE scheduler_id < 255

GROUP BY is_online,

         [status];

I
am with the SQL Protocols Dev team. I’ve looked at the issue and most
likely it is related to the creation of child processes from within SQL
Server – e.g.
xp_cmdshell,
3rd party backup or compression software, etc. The problem occurs due to
a limitation of the Winsock API which does not offer creation of

I
am with the SQL Protocols Dev team. I’ve looked at the issue and most
likely it is related to the creation of child processes from within SQL
Server – e.g.
xp_cmdshell,
3rd party backup or compression software, etc. The problem occurs due to
a limitation of the Winsock API which does not offer creation of

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
#include <stdio.h>
#include <unistd.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <signal.h>
 
int main()
{
    int sfd, cfd;
    socklen_t len;
    char ch, buff[INET_ADDRSTRLEN];
    struct sockaddr_in saddr, caddr;
 
    sfd= socket(AF_INET, SOCK_STREAM, 0);
 
    saddr.sin_family=AF_INET;
    saddr.sin_addr.s_addr=htonl(INADDR_ANY);
    saddr.sin_port=htons(1205);
 
    bind(sfd, (struct sockaddr *)&saddr, sizeof(saddr));
 
    listen(sfd, 5);
    signal(SIGCHLD, SIG_IGN);
 
    while(1) {
        printf("Server waiting\n");
        len=sizeof(caddr);
        cfd=accept(sfd, (struct sockaddr *)&caddr, &len);
 
        if( fork() == 0) {
            printf("Child Server Created Handling connection with %s\n",
                inet_ntop(AF_INET, &caddr.sin_addr, buff, sizeof(buff)));
 
            close(sfd);
 
            if(read(cfd, &ch, 1)<0) perror("read");
 
            while( ch != EOF) {
                if((ch>='a' && ch<='z') || (ch>='A' && ch<='Z'))
                ch^=0x20;  
                    /* EXORing 6th bit will result in change in case */
 
                if(write(cfd, &ch, 1)<0) perror("write");
 
                if(read(cfd, &ch, 1)<0) perror("read");
            }
            close(cfd);
            return 0;
 
        }
 
        close(cfd);
    }
}

997755.com澳门葡京 4

non-inheritable
socket handles. If a child process of SQL Server is created after a
connected socket is created and before the inheritance flag of the

non-inheritable
socket handles. If a child process of SQL Server is created after a
connected socket is created and before the inheritance flag of the

Let’s
see what we’ve done here. At line 23, after getting the socket
descriptor cfdScheduler无法用于查询管理。 from the call to accept, we forked the server. The
child process (where pid==0) closes the listening descriptor
with close(sfd), does the work the server is intended to do, and when
finished, closes the descriptor and returns (see lines 27-39).

 

socket
is cleared, the child process inherits the socket handle. When closing
the corresponding client connection, a proper Tcp cleanup cannot be
performed
before
the child process of SQL Server is closed.

socket
is cleared, the child process inherits the socket handle. When closing
the corresponding client connection, a proper Tcp cleanup cannot be
performed
before
the child process of SQL Server is closed.

The
server, on the other hand, where pid>0, just closes the cfd (line
39), and is again ready to accept more connections. Thus, for each
incoming connection, a new server process will be created.

法定文书档案关于Status的牵线如下:

To
implement a temporary workaround, add the TcpAbortiveClose SQL parameter
to registry as described in KB article
.
Please
be careful if you are doing this on a cluster to avoid checkpoint issue
and registry misconfiguration which would prevent SQL from coming
online.
SQL
Server needs to be restarted in order the changes to take effect. No
significant perf impact is expected from the change, no functional
impact should be either.

To
implement a temporary workaround, add the TcpAbortiveClose SQL parameter
to registry as described in KB article
.
Please
be careful if you are doing this on a cluster to avoid checkpoint issue
and registry misconfiguration which would prevent SQL from coming
online.
SQL
Server needs to be restarted in order the changes to take effect. No
significant perf impact is expected from the change, no functional
impact should be either.

Another
method of doing this is using threads, but we’re not getting into that
right now.

Indicates the status of the scheduler. Can be one of the following
values:

 

 

Now,
compile, run and see the server running and handling multiple clients.
See Figure 1 — I’m running the server on a network now (;) though these
clients are VirtualBox running VMs with Backtrack (192.168.1.19) and
Arch (192.168.1.4), and Android phone running ConnectBot to create a TCP
connection.

– HIDDEN ONLINE

  

  

997755.com澳门葡京 5

– HIDDEN OFFLINE

Figure
1: Server screen

– VISIBLE ONLINE

Also
run netstat -a | grep 1205 to check for current network connections;
I’ve greped 1205, our port, to show only connections to our server (see
Figure 2).

– VISIBLE OFFLINE

997755.com澳门葡京 6

– VISIBLE ONLINE (DAC)

Figure
2: Output of netstat

– HOT_ADDED

We
can also see the parent server process LISTENing and the ESTABLISHED
connections, with IPs and ports.

Is not nullable.

We
added signal(SIGCHLD, SIG_IGN) to the code to prevent child processes
going into a zombie state. A child process, when it finishes (a clean
termination or killed by some signal), returns the exit status to its
parent process, which is notified by the SIGCHLD signal sent by the
system. If the parent doesn’t handle this signal, the child process
still uses some memory, and remains in a zombie state. If the parent
finishes before the child, or doesn’t collect the status and terminates,
the status will be provided to the parent of all processes,
i.e., init with pid 1.

HIDDEN schedulers are used to process requests that are internal to the
Database Engine. VISIBLE schedulers are used to process user requests.

Let’s
examine some code from the signal() man page:

OFFLINE schedulers map to processors that are offline in the affinity
mask and are, therefore, not being used to process any requests. ONLINE
schedulers map to processors that are online in the affinity mask and
are available to process threads.

#include &lt;signal.h&gt;
 
typedef void (*sighandler_t)(int);
sighandler_t signal(int signum, sighandler_t handler);

DAC indicates the scheduler is running under a dedicated administrator
connection.

signal() sets
the disposition of the signal signum to handler, which is
either SIG_IGNSIG_DFL, or the address of a programmer-defined
function (a “signal handler”). It also indicates that the behaviour
of signal() differs among different Linux and UNIX versions, and tells
us to usesigaction() instead.

HOT ADDED indicates the schedulers were added in response to a hot add
CPU event.

The
problem is whether the blocked system call, after running the signal
handler, will be restarted or not. We can look at this later, when we
write our own signal handler — or just go check
the sigaction structure in the man pages.

 

Now,
just receive the signal and ignore it by setting handler to SIG_IGN.
This will not let the child enter a zombie state. What if the parent
finishes before the child (though not the case here, because the server
is in an infinite loop)? In that case, the parent can wait for the child
usingwait() or waitpid() calls, of which waitpid() is
preferred.

当中有关OFFLINE与ONLINE的讲明:OFFLINE
安顿程序在关乎掩码中映射到地处脱机状态的Computer,由此不用于拍卖任何请求。
ONLINE
陈设程序在事关掩码中映射随处于一块状态的微管理器,并且可用以拍卖线程。

These
system calls wait for a state change in the child, such as the child
being terminated or stopped by a signal, or resumed by a signal (check
the man pages).

 

A little science

Now,
before moving further, let’s talk about TCP here since better code
requires a sound understanding of the basics.

In
Figure 3, the rectangles represent the states, and the edges, the
transitions.

997755.com澳门葡京 7

Figure
3: TCP state diagram

We
need to visualise the server and client in the diagram. Two edges come
out of the CLOSED state; one is Active Open. This transition occurs when
a client sends a SYN packet to the server, and the system is initiating
the connection. Another is Passive Open, where the server enters the
listen state and waits for connections.

First,
from the client side, after the SYN is sent the client enters
the SYN_SENT state. Now, after receiving the SYN from the server and
sending SYN and ACK, it transitions to ESTABLISHED. From the ESTABLISHED
state (where communication takes place), sending a FIN will initiate an
Active Close to terminate the connection, and enter
the FIN_WAIT_1 state. Receiving ACK will move it to
the FIN_WAIT_2 state.

Receiving
the FIN from the server will result in sending the ACK, and going to
the TIME_WAITstate. In this state, the system waits for twice the MSL
(maximum segment lifetime); the recommended value in RFC 1337 is 120
seconds, Linux implements 60 seconds. This state helps reliable
termination ofconnections in case of lost packets, and allows old
duplicate segments to expire in the network. Finally, it goes to the
CLOSED state.

For the
server, passive open is the LISTEN state. Receiving a SYN results in
sending SYN and ACK, and going to the SYN_RCVD state. Receiving an ACK
will take the server to the ESTABLISHED state for data communication.
Then, receiving a FIN will result in sending an ACK, and will initiate
the passive close and it going to the CLOSE_WAIT state.

After
the operation completes, the server sends the FIN, and transitions to
the LAST_ACK state. On receiving the ACK, it will terminate the
connection and go to the CLOSED state.

Here we
can see the “ThreeHandshake” — the exchange of three packets to
establish a TCP connection. It is initiated when the client
calls connect(). Packet 1 is SYN x from client to server; Packet 2 is
ACK x+1 and SYN y from server to client; and Packet 3 is ACK y+1 from
client to server. Here, x is the sequence number from the client, and y
the sequence number from the server.

To
terminate the connection, we need four packets. The client
calls close() to initiate termination: Packet 1 is FIN m from client
to server; and Packet 2 is ACK m+1 from server to client. Now, the
server finishes the operation, and then calls close() and sends its
FIN n. The client sends ACK n+1 to terminate the connection.

Here’s
where I close the connection, even though it was short this time! Next
month, I’ll be back with a new connection to socket
programming…
you can now ACK my FIN!

大概,调节程序(SQL Schedulers)不会用来查询管理。
那种意况是由关联掩蔽恐怕可限制引起的:

 

SQL Schedulers
disabled

When SQL Server uses a portion of the server processors , the is_online
column on the sys.dm_os_schedulers will return 0.

 Basically , the scheduler will not be used for query processing. The
situation is caused by either affinity
masking
or licensing restrictions

997755.com澳门葡京,Be careful if you attempt to change these number – either be confident
you know how to use affinity
maskingor
if Virtualization is used , speak to the VM administrator about
apportioning more cores per virtual socket. In either case analyse the
impact and test thoroughly

 

自己抵触服务器的CPU能源使用情状,发掘在Resource
Monitor里面,某个逻辑CPU上利用率大概为零。基本上就能够判别是SQL
License许可限制的大概最大

 

997755.com澳门葡京 8

997755.com澳门葡京 9

 

反省官方文书档案SQL Server 201四各类版本扶助的法力.aspx)开采标准版单个实例使用的最大总计技能(SQL
Server 数据库引擎)限制为5个插槽或1陆核,取两者中细小值。如下截图所示

 

997755.com澳门葡京 10

 

检查服务器的CPU配置,开掘CPU能源配置如下:1贰核是(虚拟插槽数陆*各类插槽的内核数为二)也正是说陆*二=1二,超过了License限制(多少个插槽数),所以应当调节为4(虚拟插槽数)*3(内核数)本事使得的应用分配到CPU财富.

997755.com澳门葡京 11

 

参考资料:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website