Post reply

SQL Queries - Parallelism

  • February 23, 2010 at 11:30 pm

    #230301

    Hello,

    my actual situation:

    I have a Xeon CPU with 4 Cores and 8 Threads.

    SQL Server 2005 Developers Edition is running on this machine, configured to 8 processors.

    I have 8 SQL-Statements, that calculates 8 indepentens key values from one table. (one for each year)

    Each statement takes about 10 minutes to complete.

    I've put these 8 queries into a stored procedure. The compiler puts them into a sequence, one after the other. As a result the whole procedure lasts for about 80 minutes.

    For any reason only one (out of eight) CPU is used for it - this one is running on 100%, but the whole machine is running on 13% only.

    I've tried to manually divide the 8 queries and start them in separate query-windows.

    In this case they are calculated in parallel - all 8 CPUs are used, the whole machine is running on 100% CPU (instead of 13%) and the total result is availabe after 12 minuten.

    Do you have any idea how to tell the compiler to start these queries in parallel and to use all available CPU power ?

    I've no idea why it does not 🙁

    Thanks in advance and best regards

    Gawan

  • February 24, 2010 at 3:07 am

    #1124178

    Parallelism = tricky.

    I would have Google around and read up on maximum degree of parallelism settings (MAXDOP) and parallelism thresholds.

    Also, have a read of this - its worth reading just to understand it;

    http://www.sqlservercentral.com/Forums/Topic594302-360-1.aspx

  • February 24, 2010 at 4:56 am

    #1124199

    To get parallel processing to work, I think you will either have to combine all your queries into one with something like UNION ALL, or write an async CLR SP.

    Also, I would be very wary of allowing SQL Server to use all the processors for parallel processing. I suspect something like MAXDOP 4 would be OK for your machine.

  • February 25, 2010 at 12:23 pm

    #1125165

    using UNION or UNION ALL didn'T change anything at all ...

    what does "async CLR SP." mean ?

  • February 25, 2010 at 3:05 pm

    #1125245

    Asynchronous Common Language Runtime Stored Procedure

    You would need to create a manged stored proc which makes asynchronous calls to the database, each of them containing one of the queries.

    Another option may be service broker.

  • February 25, 2010 at 3:08 pm

    #1125246

    Different idea. Use service broker to instantiate 8 seperate sessions and then return the results.

    CEWII

  • February 25, 2010 at 10:12 pm

    #1125343

    great idea !

    do you perhaps have a coding example for

    Asynchronous Common Language Runtime Stored Procedure

    or

    Service Broker

    BR

    Gawan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply