如何在SQL Server中禁用多线程？
如何在SQL Server中禁用多线程？
提问于 2018-07-29 02:03:44
我有一个SQL Server 2017 Standard edition数据库，它在命令上使用多个线程。
例如，我运行简单的select和数据库打开更多的线程。这是不可取的，因为我为有限数量的用户购买了许可证，并且如果某些命令需要更长的时间，则会阻止对数据的访问。如何将其禁用？
感谢您的答复
DECLARE @loginname NCHAR(128)
DECLARE @spid SMALLINT
DECLARE @EventType NCHAR(30)
DECLARE @Parameters SMALLINT
DECLARE @EventInfo NVARCHAR(4000)
SELECT
loginame, nt_domain, nt_username, hostname, login_time, program_name,
CASE
WHEN dbid = 0 THEN N''
ELSE DB_NAME(dbid)
END,
spid, open_tran, net_library, cpu, physical_io,
memusage, blocked, status, last_batch, cmd, context_info
FROM
master.dbo.sysprocesses
WHERE
net_library <> N''
AND program_name <> N'SQLAgent - Generic Refresher'
AND program_name <> N'SQLAgent - Alert Engine'
AND program_name <> N'SQLAgent - Job invocation engine'
DECLARE c1 CURSOR LOCAL FAST_FORWARD FOR
SELECT loginame, spid
FROM master.dbo.sysprocesses
WHERE net_library <> N''
AND program_name <> N'SQLAgent - Generic Refresher'
AND program_name <> N'SQLAgent - Alert Engine'
AND program_name <> N'SQLAgent - Job invocation engine'
OPEN c1
WHILE 1 = 1
BEGIN
FETCH NEXT FROM c1 INTO @loginname, @spid
IF @@FETCH_STATUS <> 0
BREAK
DBCC INPUTBUFFER(@spid)
END
CLOSE c1
DEALLOCATE c1例如，使用相同的select命令输出7个线程：
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 405 | 0 | 4 | 0 | runnable | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 218 | 77 | 0 | 0 | suspended | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 265 | 93 | 0 | 0 | suspended | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 219 | 30 | 0 | 0 | suspended | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 219 | 65 | 0 | 0 | suspended | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 141 | 39 | 0 | 0 | runnable | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 203 | 75 | 0 | 0 | suspended | 2018-07-28 19:47:17.207 | SELECT |
| WS2012 | 2018-07-28 19:47:17.217 | Helios Orange - HeliosMain 2.0.2018.0600 HEIQ0100-22970|2 | Helios001 | 62 | 0 | TCP/IP | | 125 | 20 | 0 | 0 | runnable | 2018-07-28 19:47:17.207 | SELECT |回答 1
Stack Overflow用户
回答已采纳
发布于 2018-07-29 02:28:10
您可以使用以下命令禁用查询并行性：
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism',1;
RECONFIGURE;也就是说，阻塞问题的解决方案不是关闭并行性。解决方法是查询和索引调优，以及适当的隔离级别和/或启用READ_COMMITTED_SNAPSHOT数据库选项。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51573925
复制相关文章
相似问题