Sql server performance issue with multiple threads

Question

First I will describes the details of my scenario and then my question:

1. I am running an instance of sql server 2017 with the following properties (from SSMS):

   • product: SQL Server Express Edition (64-bit)
   • platform: NT x64
   • version: 10.0.2531.0
   • memory: 3980MB
   • processors: 4 (logical) 2 (physical)
   • collation: SQL_Latin_General_CP1_CI_AS

2. The instance is configured as follows: recovery model is set to simple and I truncate the log file
   via dbcc shrinkfile before I run my queries

3. Max server memory is set to 2147483647 and min to default
4. All connections to the instance are set to have an isolation level of read uncommitted
5. There are three tables whose total size is ~3.2GB upon which I perform selects and a single table, which is initialized to be empty, to which I perform inserts. All the tables have clustered indecies on their relevant columns.
6. I execute a single stored proc which performs all the operations in the above tables, but the access pattern of the selects is random, i.e. each time the sp is called it access random rows in the big tables (all in all it access a total of 5 rows from all tables per invocation) and adds a new row in the small table, so there is no collision between the inserts
7. I measure the execution time of the stored procedure via datediff inside the body of the stored procedure
8. I run two optional scenarios: a single thread from my app calls the sp N times and recieves an average time of 12sec 4 threads from my app concurrently call the sp N/4 times each and recieve average times of: 0.8sec 3sec 5sec 90sec All threads in all scenarios perform all their queries successfully

My hypothesis & question(s):

1. It seems logical that the single thread out performs threads that run concurrently due to increased memory pollution caused by multiple threads accessing such big tables, but how come there are concurrent threads that out perform the single thread even though they are running with a more polluted memory? (I would expect the single thread to out perform ALL the concurrent threads.)

2. Is there a locking issue here although the isolation level is read uncommitted?