none
Does two SQL server execute two queries in parallel? RRS feed

  • Question

  • I have two heavy queries to run, and both of them take about 1 minute to run. Both queries are using Bulk Insert command to insert data from txt files to tables in the database. 

    For the two tables importing data, no index/trigger/constraint, just two empty table to load data.

    For the two text files to load, each of them have around 20M rows.

    Query 1:

    `Bulk insert table1
    FROM 'table1.txt'  WITH
            (FIELDTERMINATOR ='|',
    ROWTERMINATOR = '0x0a',
    TABLOCK)`

    Query 2:

    `Bulk insert table2 FROM 'table2.txt'
    FROM 'table2.txt'  WITH
            (FIELDTERMINATOR ='|',
    ROWTERMINATOR = '0x0a',
    TABLOCK)`

    I want to compare the running time under different scenarios

    Scenario 1: two queries running serially in one editor:

    • Query 1: 1 m 18 s
    • Query 2: 1 m 2 s

    Scenario 2: each query in a separate editor of the SAME STUDIO, running concurrently:

    • Query 1: 2 m 36 s
    • Query 2: 2 m 09 d

    Scenario 3: each query in a SEPARATE STUDIO, running concurrently:

    • Query 1: 2 m 29 s
    • Query 2: 2 m 19 s

    For all scenarios,no significant differences between the usage of CPU and disk are observed. I am wondering what's happening under the hood? How does the sql server execute these queries?

    I'm using sql server 2016 developer version on windows 10 64 bit.

    Thanks in advance for your help!

    Jason

    Update:

    After testing more scenarios with 4 queries running together, I think the possible cause of low usage of CPU and disk is the hyperthreading with my core i7:

    • With two editors running two queries concurrently, the most CPU usage is 25%.

    • With 4 editors running 4 queries concurrently, the most CPU usage is 50%.

    Are you guys aware of ways to let one query exclusively use one core? Turning off hyperthreading is not an option for me, since my thinkpad t460p doesn't support such option in the bios.



    • Edited by Jason_86 Sunday, November 27, 2016 4:04 PM
    Saturday, November 26, 2016 2:08 PM

Answers

  • Can you kindly explained to me what happened? I guess after sever times reconnect, the query cost was recalculated and the scheduler adjusted to the changed cost and  assigned two queries to two different cores finally.

    The choice of which scheduler is used is not based on query cost. SQL Server (actually SOS) basically tries to balance the workload among schedulers by examining the load_factor column of sys.dm_os_schedulers (https://msdn.microsoft.com/en-us/library/ms177526.aspx). When a new task is created for a query execution, SOS prefers to use the same scheduler it used for the last request on the session.  However, it will use a different scheduler if the load factor of the preferred scheduler is greater than a certain percentage of the other scheduler's average load factor.  

    So what's happening here is that the 2 SSMS connections both initially use the same scheduler because it has the least load. The BULK INSERTs then both run on the same scheduler, which is the reason for the high SOS_SCHEDULER_YEILD waits. 

    SQL Server generally balances the workload among schedulers fairly evenly but in the case of your laptop with only a few active connection, the chances are high that the same scheduler (with the lightest load) will be used.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Jason_86 Monday, November 28, 2016 2:30 PM
    Monday, November 28, 2016 5:23 AM
    Moderator

All replies

  • Scenario 1: two queries together in one editor, it takes about 2 minutes. 

    Scenario 2: each query in a separate window, the running time is about 1 min 45 seconds. 

    Scenario 3: two Microsoft SQL server management studios opened and let each studio run different query. The running time is still around 1 min 45 seconds. For this scenario, I used the same username to login

    For all these three scenarios, I don't observe significant differences between the CPU and disk usage. I am wondering what's happening under the hood? How does the sql server execute these queries?

    Scenario 1 will run the 2 statements serially, so if each takes 1 minute, the total duration will be 2 minutes.

    Scenarios 2 and 3 run the statements concurrently. The elapsed time will depend on concurrency and available server resources. Since each inserts into a different table, concurrency shouldn't be an issue.  If the inserts take significantly longer than a minute without high CPU utilization, you are probably constrained by resources other than CPU.  How are you measuring disk usage?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, November 26, 2016 3:01 PM
    Moderator
  • Jason,

    In scenario 1 the first query would be executed and then the 2nd one. In rest all queries would be executed mutually exclusive.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, November 26, 2016 3:02 PM
    Moderator
  • Hi Dan,

    Thanks for your reply! To measure the disk usage, I opened the task manager and checked the percentage of disk. Most of the time the transition speed of the disk 50MB to 60MB per second for whichever scenario.

    Thanks,

    Jason

    Saturday, November 26, 2016 4:03 PM
  • Hi Shashank,

    Thanks for your reply! While running these two queries concurrently, say in two studios, I expected the running time to be slightly more than 1 min and the CPU and disk usage would be double. Now in either of the scenarios, the CPU usage is around 22% and disk usage is around 50-60 MB per second. How can speed the execution up? I have a quad core cpu and SSD in my laptop running windows. 

    Thanks,

    Jason

    Saturday, November 26, 2016 4:11 PM
  • Try running a sequential write test using the DiskSpd utility (https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223) to make sure your storage is capable of sustaining more than 50-60MB/sec throughput. If that's the limit of your storage system, you won't get much increased throughput by running the inserts in parallel.

    The example below will run a 60-second sequential write test with a 64K block size using 2 threads.  This can be run from an administrator command-prompt for instant file initialization.  See the accompanying DiskSpd documentation for more details and examples.

    DiskSpd.exe -Sh -d60 -W0 -c1G -F2 -w100 -si64K -b64K -o2 -L "D:\diskspd.dat"


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Saturday, November 26, 2016 4:30 PM
    Moderator
  • I have an SSD and core i7 in my computer and I have observed more than 200 MB per second for my disk usage. Do you think I should run such test?
    Saturday, November 26, 2016 4:46 PM
  • I have an SSD and core i7 in my computer and I have observed more than 200 MB per second for my disk usage. Do you think I should run such test?

    Running the IO test will be an academic exercise if you are certain the storage is capable of sustaining much more. If the system in question is your PC rather than a dedicated database server, make sure SQL Server max memory is configured to play nice with other applications running on the box.  You might try specifying BATCHSIZE=1000 on the BULK INSERT statement to see if that improves throughput.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, November 26, 2016 5:04 PM
    Moderator
  • Using batchsize = 1000, my disk usage are more than 250 MB/s, but the running time is actually longer....
    Saturday, November 26, 2016 5:30 PM
  • I updated my post with more information, do you mind taking a quick look at it? Thanks a lot for your help!
    Sunday, November 27, 2016 4:36 AM
  • How many records are you importing? I meant to suggest a larger batchsize, such as 10000 or 100000. you might give that a try.

    I have seen sub-optimal performance with concurrent bulk inserts when multiple concurrent bulk inserts happened to use the same UMS scheduler (CPU) back in the days of SQL Server 2000, but the thread scheduling architecture has changed significantly since then.  I doubt hyperthreading is the issue for such a large performance difference with Server 2016.

    Try running the query below while the BULK INSERT commands are running. Pay particular attention to the wait types as that will indicate why CPU is not being used fully. Again, make sure that you have set SQL Server max memory to allow for the needs of both the OS and other applications as this will avoid waits related to memory application.

     


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Sunday, November 27, 2016 3:27 PM
    Moderator
  • Thanks for your reply! What "query below" did you mean?

    The number of records for my dataset is 20M for both datasets.

    Sunday, November 27, 2016 3:55 PM
  • Thanks for your reply! What "query below" did you mean?

    Sorry, forgot to post the query:

    SELECT scheduler.cpu_id, request.command, request.wait_type, request.last_wait_type, worker.affinity
    FROM sys.dm_os_schedulers AS scheduler
    JOIN sys.dm_os_workers AS worker ON
    	worker.worker_address = scheduler.active_worker_address
    JOIN sys.dm_exec_requests AS request ON
    	request.scheduler_id = scheduler.scheduler_id
    JOIN sys.dm_os_tasks AS task ON
    	task.task_address = request.task_address
    WHERE request.command = 'BULK INSERT';


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, November 27, 2016 4:39 PM
    Moderator
  • I ran the command and the most frequent I got is 

    cpu_id command wait_type last_wait_type affinity
    1       BULK INSERT    NULL SOS_SCHEDULER_YIELD 2
    1       BULK INSERT    NULL SOS_SCHEDULER_YIELD 2

    Using Paul Randal's code here, 

    www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

    I get some more wait statistics

    WaitType SOS_SCHEDULER_YIELD   PAGELATCH_UP
    Wait_S 930.27 148.54
    Resource_S 0.22 74.42
    Signal_S 930.05 74.12
    WaitCount 481531 164578
    Percentage 82.57 13.18
    AvgWait_S 0.0019 0.0009
    AvgRes_S 0 0.0005
    AvgSig_S 0.0019 0.0005


    What issues does it suggest for my query?

    Thanks!





    • Edited by Jason_86 Sunday, November 27, 2016 5:37 PM
    Sunday, November 27, 2016 5:18 PM
  • I ran the command and the most frequent I got is 

    cpu_id command wait_type last_wait_type affinity
    1       BULK INSERT    NULL SOS_SCHEDULER_YIELD 2
    1       BULK INSERT    NULL SOS_SCHEDULER_YIELD 2


    Verify the SQL Instance is configured to use all processors.  This can be done via the Processor properties of the server in SSMS.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, November 27, 2016 5:54 PM
    Moderator
  • "Automatically set processor affinity mask for all process" is checked.

    "Automatically set I/O affinity mask for all process" is also checked.

    "Maximum worker threads" is set to 0

    I checked the system table, the maximum worker threads number is 576. My computer is 16 logical processors with 64 bit.

    While 2 queries are working, the number of working threads is 72.

    With "boost SQL server priority" checked, no significant improvement found.

    With "using windows fibers" checked, no significant improvement found.


    Sunday, November 27, 2016 6:47 PM
  • Try to disconnect, reconnect, and repeat the 2 bulk insert commands on different connections. I'm hoping to see a different CpuId for the bulk insert commands when you run the query.

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, November 27, 2016 7:38 PM
    Moderator
  • I repeated the disconnect/reconnect for sever times and something really unexpected happened.

    For the first couple of times, the CPU_id were the same for both bulk-insert. The CPU/Disk usage was low and the running time was also long. There were two parts in the execution plan, remote scan is 78% and table insert is 28%.

    Suddenly after one reconnect, the CPU_id were different for the two bulk-insert. Say, one were 2 and another were 4. The CPU/Disk usage was high and the running time was also short. The percentage for the two parts of the execution also changed: remote scan is 63% and table insert is 37%.

    Can you kindly explained to me what happened? I guess after sever times reconnect, the query cost was recalculated and the scheduler adjusted to the changed cost and  assigned two queries to two different cores finally.

    If that's the reason, is there a way that I can tell sql-server that the cost is large and prompt the scheduler to run queries on different cores?

    Thanks a lot for your dedicated help and your advice has been very helpful to me!

    Monday, November 28, 2016 12:31 AM
  • Can you kindly explained to me what happened? I guess after sever times reconnect, the query cost was recalculated and the scheduler adjusted to the changed cost and  assigned two queries to two different cores finally.

    The choice of which scheduler is used is not based on query cost. SQL Server (actually SOS) basically tries to balance the workload among schedulers by examining the load_factor column of sys.dm_os_schedulers (https://msdn.microsoft.com/en-us/library/ms177526.aspx). When a new task is created for a query execution, SOS prefers to use the same scheduler it used for the last request on the session.  However, it will use a different scheduler if the load factor of the preferred scheduler is greater than a certain percentage of the other scheduler's average load factor.  

    So what's happening here is that the 2 SSMS connections both initially use the same scheduler because it has the least load. The BULK INSERTs then both run on the same scheduler, which is the reason for the high SOS_SCHEDULER_YEILD waits. 

    SQL Server generally balances the workload among schedulers fairly evenly but in the case of your laptop with only a few active connection, the chances are high that the same scheduler (with the lightest load) will be used.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by Jason_86 Monday, November 28, 2016 2:30 PM
    Monday, November 28, 2016 5:23 AM
    Moderator
  • Thanks for your reply! How can I avoid such things happen again? Can I force the new task to use a new scheduler?

    When you say scheduler, is it a windows concept or SQL concept? I didn't find its definition online. It's extensively referred in the internet and I want to know its exact definition. Sorry I just started learning those advanced concepts of SQL. 

    Besides, SOS stands SQLOS?

    Based on so high wait, why doesn't SOS switch the query to another scheduler after some running time?


    • Edited by Jason_86 Monday, November 28, 2016 5:56 AM
    Monday, November 28, 2016 5:46 AM
  • When you say scheduler, is it a windows concept or SQL concept? I didn't find its definition online. It's extensively referred in the internet and I want to know its exact definition. Sorry I just started learning those advanced concepts of SQL. 

    Besides, SOS stands SQLOS?

    SOS is a shorthand acronym for SQLOS (SQL Server Operating System) and used interchangeably (e.g. in the SOS_SCHEDULER_YIELD wait type). The definition according to https://msdn.microsoft.com/en-us/library/ms176083.aspx, is "The SQLOS is responsible for managing operating system resources that are specific to SQL Server."  That includes thread scheduling, memory management, synchronization, etc.  See this blog post for a high-level SQLOS diagram: https://blogs.msdn.microsoft.com/sqlosteam/2010/06/23/sqlos-resources/.

    SQL Server internals are discussed in depth in books like those in the SQL Server Internals series by Kalen Delaney, which is what I consulted to help answer your question.  Internals like thread scheduling are not something one generally needs deep knowledge of but it does come in handy for diagnosing specific issues like this.

    Based on so high wait, why doesn't SOS switch the query to another scheduler after some running time?

    The worker thread that performs the actual task for a query are assigned when the query starts and held until completion. Workers are bound to schedulers (one per logical CPU) so the effect for a non-parallel query is that all the work is done on the same scheduler. Moving the task to a different worker thread (and scheduler) during query execution would be very expensive. The affinity of workers to schedulers increases query performance.

    AFAIK, you cannot force a task to use a specific scheduler; SQLOS chooses it for you based on the scheduler load factor. As I mentioned earlier, this shouldn't be an issue with a production-like load.  The odds of being unlucky are higher when there is no other SQL activity. I think the SQLOS could be smarter to handle edge cases like yours, though.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, November 28, 2016 1:55 PM
    Moderator
  • Hi Dan,

    Thanks a lot for your help! I didn't expect a seemingly "simple" questions could take such a long way to answer and it could involve so much deep knowledge.

    In production phase, I'll run hundreds of queries like the query 1 and 2 in the first post. I think the scheduling wouldn't really be a problem.

    I guess in the case if I really only need to run two heavy queries, I can let the first query run for a couple of minutes. So the running processor could be busy and then I'll start the second query, hopefully, the SOS could assign the second query to the free CPU.

    Jason

    Monday, November 28, 2016 2:29 PM
  • Thanks a lot for your help! I didn't expect a seemingly "simple" questions could take such a long way to answer and it could involve so much deep knowledge.


    Makes two of us:-) 

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Monday, November 28, 2016 6:29 PM
    Moderator