I have a SQL Server 2017 Standard edition database that uses multiple threads on the commands.
For example, I run simple select and database open more threads. This is undesirable because I have purchased a license for a limited number of users and if the some command takes longer occurs blocking access to data. How can I disable it?
Thanks for answer
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
For example output with 7threads with same select command:
| 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 |
You can disable query parallelism with:
EXEC sp_configure 'show',1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism',1;
RECONFIGURE;
That said, the solution for blocking problems is not turning off parallelism. The cure for that is query and index tuning along with an appropriate isolation level and/or turning on the READ_COMMITTED_SNAPSHOT database option.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments