Concurrency vs Parallelism in SQL Server

There is a difference between the terms Concurrency and Parallelism. For people like me who know just enough English to survive, meaning of these 2 terms might sound similar. But in SQL Server world, they mean different things.

Check out this statement

Insert operation when used with TABLOCK hint can run in parallel.

Does this mean multiple INSERT operations can run at once on the table?
OR
Does it mean the INSERT operation with TABLOCK hint will be executed by multiple threads?

Before I answer this question, let’s get these terms straight:

Concurrency:

• Multiple operations running at the same time on the server.

• In the screenshot below, you will see 37 operations running concurrently on the server. This could mean they are all different operations, each with its own SPID, doing its work.

  --Displays currently active requests with sql text
  SELECT * FROM sys.dm_exec_requests AS er
  CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) 

Parallelism:

• A single operation (operator) is executed by multiple threads (at least 2 threads).

• When an operator runs in parallel, you will see 2 tiny yellow arrows in execution plan indicating multiple threads are working on that operator.

• An operator will run parallel if its estimated cost exceeds ‘Cost Threshold of Parallelism’.

Answer to the question I quoted earlier

Insert operation when used with TABLOCK hint can run in parallel.
This does not mean multiple insert operations can run at once on the table.
It means that insert operation with tablock hint can be executed by multiple threads. (Note the word parallel in the statement, it refers to multiple threads, not multiple operations)

Summary:

Concurrency = multiple operations.
It is about how many operations are running on the server at once.

Parallelism = multiple threads.
It is about how a single operation gets split across multiple processors.

Hope this helps.

Thanks for reading,
Haripriya.