SQL Server Performance

Simultaneous queries execution taking more time

Discussion in 'Performance Tuning for DBAs' started by vikvin74, Oct 27, 2003.

  1. vikvin74 New Member

    Simultaneous Queries Execution Time (Minutes)
    I am facing a very weird proble. I have four different queries, each if executed individually will take say about 13 minutes. But if I execute them simulteneously (opening 4 different query analyzer windows and running one after another), it takes at least 36 minutes before the first one is displayed. I couldn't understand this behavior of SQL Server. Why it's taking soo much amount of time when parallel queries are executed? What exactly is going on?? I including all the possible details...

    Computer Specifications:
    OS: Windows 2K server Family
    SQL Server: Version 2000
    RAM: 392 MB
    Processor:398 MHz

    And executed query from the same machine. I didn't use any remote client.

    Query 1 Query 2 Query 3 Query 4
    CPU Elapsed CPU Elapsed CPU Elapsed CPU Elapsed
    Time Time Time Time TimeTime TimeTime
    4.5443.17 4.5742.46 4.4737.69 4.3536.26 (4 queries simulta)
    5.1335.75 5.2234.84 5.4331.32 (3 qrys simultansly)
    4.0622.82 3.9522.16 (2qrys simulatenio)
    3.5812.86 ( Singel Qry)

    Any Suggestions???
  2. satya Moderator

    For the slow running queries PROFILER is the best tool to assess the trace and submit the same to Index TUning wizard for index recommendations.

    Also it depends on machine resources, and I don't see much RAM is available.
    Capture counters for PERFMON also about system resources.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. vikvin74 New Member

    Thanks Satya for replying...

    Yea that's ture that I am sevierly under resource constraints....i checked PROFILER too...it's showing me that I am overutilizing my CPU and RAM......

    But what I am interested in is why is this happning.,....what exactly is going on inside the CPU??? is that related to multithreading???? how server handles request????why not server is dedicated to first query first and when done with it, then start the second one and so on????? is this related to CONTEXT switching concept which i really don't know...

    any more suggestions??
  4. satya Moderator

    Its stressed on single processor and it will be advantage to take affinity mask option if you have more than one processor.

    Refer to Inside SQL Server 2000 by Kalen Delaney for detailed explanation of CPU requests and handling queries by SQL engine.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. ykchakri New Member

    quote:Originally posted by vikvin74

    Thanks Satya for replying...

    But what I am interested in is why is this happning.,....what exactly is going on inside the CPU??? is that related to multithreading???? how server handles request????why not server is dedicated to first query first and when done with it, then start the second one and so on????? is this related to CONTEXT switching concept which i really don't know...

    any more suggestions??

    I guess this is because of the queing of threads. If the first query is using CPU and if there are not enough CPU resources for the subsequent ones, they will wait in a queue and once the first query releases CPU for performing other tasks like I/O, the subsequent queries start using the now idle CPU. But, when the first query returns back for more CPU it has to wait in the queue. This is what could be making the parallel execution of queries slower and this is by design.
  6. vikvin74 New Member

    Thanks for the reply. I guess that makes sense. I guess I really need to go deep inside...like when the first query returns back for more CPU which is now occupied by query 2, has to wait in a queue. But at that point of time, if the third query is already waiting in the queue for CPU, which one will get the preference in case query 2 leaves the CPU for some I/O purpose? Query 1 or Query 3?

    i guess this is related to Operating systems issue. Also I will go through that book "Inside Microsoft SQL Server 2000" by Kalen Delaney....

  7. satya Moderator

    The more you go deep with the book .... will be refreshed and understand the skeleton.
    As of now you can even capture counters such as CPU, PRocessor, Process, Physical disk and see how largely they're stressed.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. Twan New Member


    as you've worked out your queries will essentially follow a round-robin pattern. Either when a thread has to get more info from disk, or if its timeslice is up, it will be interrupted and the next longest waiting thread (with the highest priority) will start.

    Cheers
    Twan
  9. vikvin74 New Member

    Thanks for the reply Twan. However what is this round-robin pattern and timeslicing??? Can you put more light onto it? Can you explain me giving some proper and easy example?

    Vikas
  10. cejar New Member

    Other resources you might want to investigate are the Max worker tread option, Thread and fibers execution, Thread pooling, sp_configure. Of course before you reconfigure any settings make sure you consult with your Sr.DBA first if you have one, and do not change any setting on production server before testing.



    Cheers!
  11. cejar New Member

    Are these four queries all hiting the same tables? It could be a locking conflict.

    Cheers!
  12. Twan New Member

    Hi ya,

    I'll use a reference instead...http://www.winntmag.com/Articles/Index.cfm?IssueID=22&ArticleID=302

    In summary though. Each process is given a base priority, the OS itself typically has a higher priority than user programs (to avoid a user program starving the OS of resources). When a thread is ready to be executed it takes on the priority of the process. To avoid a single process from using the CPU for too long, a maximum amount of time is set by the OS (timeslice) and the OS will stop the thread after this timeslice expires, and then place it back in the pool of threads ready to process. Every time the timeslice interval expires all threads that have been waiting have their priority incremented. Then the thread with the highest priority is chosen to be run this time. In cases where there is an equal priority, then the OS choose that thread which has consumed the least amount of CPU so far (assuming that a long running process is more likely to require more resources to complete)

    Cheers
    Twan
  13. vikvin74 New Member

    Hi Guys,

    Thanks a lot for your responses. As cejar pointed out, basically all of the queries are hitting the same table.....so it could be locking conflict too. But then my database is read only....so I guess locking doesn't work in read only mode....I am not sure though...

    Twan, your explanation further cleared my ideas. Resources you pointed out for references are also pretty helpful.

    cheers...
  14. ykchakri New Member

    Yes, If the database is read only it can't be a locking conflict. But, then it can also be an I/O conflict where all the queries are trying to read from same data pages simultaneously. Check the current activity window while running these queries and see if you find any wait types like 'PAGEIOLATCH_SH'.
  15. satya Moderator

    And it depends on the server h/w resource also, such as memory, CPU time etc.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. vikvin74 New Member

    If there is an I/O conflict where all the queries are trying to read from same data pages simultaneously, then how to resolve it? How OS behaves in that case? Does it lock the data pages too like SQL Server locking the table/rows? Then if it locks data pages, then when and how it releases the lock so that other waiting query access the same data pages?

    How can I check for PAGEIOLATCH_SH and what it does? Thanks for the help.
  17. vikvin74 New Member

    If there is an I/O conflict where all the queries are trying to read from same data pages simultaneously, then how to resolve it? How OS behaves in that case? Does it lock the data pages too like SQL Server locking the table/rows? Then if it locks data pages, then when and how it releases the lock so that other waiting query access the same data pages?

    How can I check for PAGEIOLATCH_SH and what it does? Thanks for the help.
  18. Twan New Member


    I'd say that what you are seeing is expected behaviour... The scheduler will do a slice of each query in turn, so the first one will take approx time*n to complete...

    Cheers
    Twan
  19. ykchakri New Member

    You can check PAGEIOLATCH_SH from EM by clicking on current activity/Process Info and observe the Wait Type column. What this says is that this process is waiting to read/write from a page that is being currently latched by some other process.

  20. gaurav_bindlish New Member

    Check if there are proper indexes on the table under consderation based on query.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Share This Page