SQL服务器并发访问 [英] SQL server concurrent accessing
问题描述
当sql server同时接收两个查询(SELECT * From the_Same_Table)，如果你有一个多处理器的服务器，sql server可以同时检索数据吗?
When sql server is receiving two queries (SELECT * From the_Same_Table), at exactly the same time, and if u have a server with multiple processors, Can sql server retrieve the data at the same time?
我试图了解如果一个廉价的 select 语句在 0.01 秒内完成并且 1000 个用户完全同时运行相同的查询会发生什么.我想如果服务器有四个处理器，那么 SQL Server 将在 0.01 秒内为前四个用户提供服务，并在 0.02 秒内为接下来的四个用户提供服务，额外会发生什么.
I am trying to understand what will happen if a cheap select statement that finish within .01 sec, and 1000 users run the same query exactly at the same time. I think what will happen that if the server is having four processors, then SQL server will serve the first four in .01 sec and serve next four users in 0.02 sec, extra.
这是否接近实际发生的情况?
Is that even close to what will actually happen?
我想我会尝试使用某种代码和记录器来测试它，或者可能有可靠的测试工具来做到这一点.
I think I will try to use some kind of code and loggers to test that, or maybe there is reliable testing tools to do that.
谢谢
推荐答案
进入 SQL Server 的每个批次(请求)都会创建一个任务.任务被安排执行并由工作人员拾取.工作线程与线程非常相似.一个任务一直留在一个工人那里直到它完成，然后它释放工人去接另一个任务.系统中的 worker 数量有限，由 sp_configure 'max worker threads' 配置.至少有 256 名工人，其中大约 35 名是系统工人.一个 worker 需要一个调度器来运行，并且每个 CPU 核心都有一个调度器.工作人员合作共享调度程序.
Each batch (request) that comes into SQL Server creates a task. Task are scheduled for execution and picked up by a worker. A worker is very similar to a thread. A task stays with a worker until it finishes, then it frees the worker to pick up another task. There are a limited number of workers in the system, configured by sp_configure 'max worker threads'. At a minimum there are 256 workers, from which about 35 are system. A worker needs a scheduler to run, and there is one single scheduler for each CPU core. Workers cooperate in sharing the scheduler.
某些任务会产生子任务，例如并行查询.这些任务也排队等待执行，需要一个工人来完成.一个产生子任务的任务只有在它产生的所有任务都完成后才能完成.
Some tasks spawn subtasks, like parallel queries. These tasks are also queued for execution and need a worker to complete. A tasks that spawn subtasks cannot complete untill all the tasks it spawn are complete.
还有一些用户操作驱动的系统任务，比如登录握手.当客户端打开一个新的连接时，握手和登录认证/授权是由一个任务完成的，这需要一个工人.
There are also some user action driven system tasks, like the login handshake. When a client opens a new conneciton, the handshake and login authentication/authorization are done by a task, which requires a worker.
当 1000 个请求到达服务器时，将创建 1000 个任务并排队等待执行.自由工作者拿起任务并开始执行它们.当他们完成一项任务时，他们会选择下一项任务，直到 1000 个请求创建的所有任务都完成.
When 1000 requests come to the server, 1000 tasks are created and queued for execution. The free workers pick up tasks and start executing them. As they finish one task, they pick up the next task, untill all tasks created by the 1000 requests complete.
显示正在发生的事情的 DMV 是:
The DMVs that show what's going on are:
这些详细信息在 SQL Server 批处理或任务调度 和在 Slava 的博客中.
These details are described in SQL Server Batch or Task Scheduling and in Slava's blog.
此外，一旦任务在执行，请求将被编译.编译将首先在内存中查找请求文本，并为具有相同计划的请求搜索现有的编译计划.您可以阅读我对 动态创建的 SQL 与参数的回复SQL Server 更详细地了解这是如何发生的.另请参阅执行计划缓存和重用.一旦创建了计划，它就会开始执行.像 SELECT ... FROM table 这样的查询将创建一个简单的计划，该计划只有几个运算符，这些运算符基本上获取每一行并将其放入 TDS 流中并返回给客户端.查询计划是一棵运算符树，查询总是通过向树的根查询下一行来执行，在循环中直到根返回 EOF.树下的查询运算符变得越来越具体，直到底部运算符将成为对所选访问路径(优化器为满足查询而选择的索引或堆)的物理访问.请参阅SQL 语句处理.索引访问将始终从缓冲池中请求数据，而不是从磁盘中请求数据.当缓冲池没有缓存请求的页面时，将在页面上放置一个 PAGEIOLATCH，并将读取该页面的请求提交给 IO 子系统.对同一页面的后续请求将等待此 IO 完成，一旦该页面在缓冲池中，所有其他需要此页面的查询将从缓冲池中删除.当缓冲池需要空闲页面时，未使用的页面会被逐出，但如果系统有足够的 RAM，页面一旦加载将永远不会被逐出.索引和堆扫描操作将请求预读，预计将请求页面链接链中当前页面之前的页面.预读受到索引连续片段的限制，这就是索引片段出现的时候，因为它减少了预读请求的大小，请参阅 了解页面和范围.
Further, once the task is in execution, the request will be compiled. Compilation will first look up the requests text in memory, and search for an existing compiled plan for a request with an identical plan. You can read my reply for Dynamically created SQL vs Parameters in SQL Server for a more detailed drill into how that happens. Also see Execution Plan Caching and Reuse. Once a plan is created, it is launched into execution. A query like SELECT ... FROM table will create a trivial plan that has a just a couple of operators that basically fetch each row and place it in the TDS stream back to the client. A query plan is a tree of operators, and the query is always executed by asking the root of the tree for the next row, in a loop until the root returns EOF. The query operators down the tree get more and more specific, until the bottom operator will be a physical access to the chosen access path (the index or heap chosen by the optimizer to satisfy the query). See SQL Statement Processing. The index access will always request the data from the buffer pool, never from disk. When the buffer pool does not have the requested page cached, a PAGEIOLATCH is placed on the page and a request to read the page is submitted to the IO subsystem. Subsequent requests for the same page will wait for this IO to complete, and once the page is in the buffer pool all other queries that need this page will it from the buffer pool. unused pages are evicted when the buffer pool need free pages, but if the system has enough RAM the page will never be evicted once loaded. Index and heap scan operations will requests read-aheads, anticipating that pages ahead of the current one in the page link-chain will be requested. Read aheads are limited by the index contiguos fragments, and this is when the index fragmentation comes into picture, as it reduces the size of read-ahead requests, see Understanding Pages and Extents.
查询执行的另一个维度是行的逻辑锁定.为了稳定性，读取可能会根据隔离模型在它读取的行上放置行锁或范围锁，以防止在查询遍历扫描时并发更新.在 SNAPSHOT 隔离级别下，查询根本不会请求锁定，而是使用版本标记来可能提供从版本存储请求的数据(请参阅 SQL Server 2005 基于行版本控制的事务隔离).在 READ UNCOMMITED 隔离下(或使用 nolock 提示时)，查询不会请求对其读取的行加锁，但如果发生并发更新(读取未提交的行、同一行可能被读取两次或现有行)，则读取不一致可能根本没读过).
Another dimension of the query execution is the logical locking of rows. For stability, a read may place row locks or range locks, depending on the isolation model, on the rows it reads, to prevent concurent updates while the query traverses a scan. Under SNAPSHOT isolation level the query will not request locks at all, but instead a version mark will be used to possible serve the data requested from the version store (see SQL Server 2005 Row Versioning-Based Transaction Isolation). Under READ UNCOMMITED isolation (or when nolock hint is used) the query does not requests locks on the rows it reads, but the reads are inconsistent if concurent updates occur (uncommited rows are read, same row may be read twice, or an existing row may be not read at all).
这篇关于SQL服务器并发访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
