Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: annoying SQL Error msg - "INTRA-QUERY"

    Hi,

    Pbbly most of you know this:

    Intra-query parallelism caused your server command (process ID #57) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).

    I've seen MS KnowledgeBase for that (http://support.microsoft.com/default...b;EN-US;837983)

    But I have some probs with it:
    1. I don't have permissions for this kind of queries
    "USE master
    EXEC sp_configure 'show advanced option', '1'
    RECONFIGURE WITH OVERRIDE
    GO"

    2. I don't know what is an "intra-query parallelism"...

    I'm attaching the query I use. The query runs OK for some parameters but gets this error msg on other parameters.

    I'm using a single CPU on 2003 STD Edition.

    Query:

    create table #mytemp_table
    (client_id int not null, commission_value int null)
    insert into #mytemp_table (client_id, commission_value)
    select client_id, sum(transfer_value) from users_transfers where transfer_type in (1,3) and is_paid = 1 group by client_id

    select
    u.name [Vendor],
    u.email,
    sum(cost) as Payment,
    cmpd.vendor_id,
    ua.name [Agent],
    vd.join_date,
    commission_value [paid],
    allow_sign
    from
    reports ra
    left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
    left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
    left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
    left outer join userssb u on cmpd.vendor_id = u.client_id
    left outer join userssb ua on cmpd.agent_id = ua.client_id
    left outer join users_details vd on u.client_id = vd.client_id
    left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
    left outer join allow_db asd on asd.client_id = u.client_id
    where
    [conditions]
    group by

    [all the group fields]
    having sum(cost) > 2999
    order by u.name

    drop table #mytemp_table

  2. #2
    Join Date
    May 2002
    Posts
    299
    1. running sp_configure affects the entire server. This is really not what you want to do anyway.

    This is how you use Maxdop statement hint.

    select
    u.name [Vendor],
    u.email,
    sum(cost) as Payment,
    cmpd.vendor_id,
    ua.name [Agent],
    vd.join_date,
    commission_value [paid],
    allow_sign
    from
    reports ra
    left outer join codes ctd on ra.affiliation_code_show = ctd.affiliation_code_show
    left outer join traffic cmp on cmp.campaign_id = ctd.campaign_id
    left outer join traffic_details cmpd on cmp.campaign_id = cmpd.campaign_id
    left outer join userssb u on cmpd.vendor_id = u.client_id
    left outer join userssb ua on cmpd.agent_id = ua.client_id
    left outer join users_details vd on u.client_id = vd.client_id
    left outer join #mytemp_table com_paid on com_paid.client_id = u.client_id
    left outer join allow_db asd on asd.client_id = u.client_id
    where
    [conditions]
    group by

    [all the group fields]
    having sum(cost) > 2999
    order by u.name
    OPTION(MAXDOP 1)
    --OPTION (MERGE JOIN, LOOP JOIN)

    2. you probably have hyperthreading (i.e. 1 physical, 2 logical). Intra-query para means the statement is getting splitted up and executed in parallel. Each logical cpu takes a part of the query and perform the work.
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •