CODEWITHSUNDEEP
sql-servermultithreading
Dominique
Dominique

Reputation: 3

How can I disable multithreading in SQL Server?

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 |

Upvotes: 0

Views: 910

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46202

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.

Upvotes: 1

Related Questions