Parallelism in query

  • Hello,

    I have query where is parallelism, How can I check, if parallelism is needed or no ?

    in attachment are details about tables and data...

    DECLARE @START_DATE varchar(8)='20140310'

    declare @END_DATE varchar(8)='20140312'

    SELECT b.[ORDER_DATE]

    ,b.[COMMIT_NO]

    ,b.[BODY_NO]

    ,b.[CAR_CODE]

    ,b.[LINE_CODE]

    ,b.[STATION_ID]

    ,b.[WORK_SEQ]

    ,b.[POINT_SEQ]

    ,b.[POINT_SER]

    ,b.[WORK_CODE]

    ,b.[WORK_POS]

    ,b.[WORK_TYPE]

    ,b.[WORK_QTY]

    ,b.[ITEM_CODE]

    ,w.[ITEM_NAME]

    ,b.[ALC_CODE]

    ,b.[LIMITV_LOW]

    ,b.[LIMITV_HIGH]

    ,b.[WORK_VALUE]

    ,b.[WORK_RESULT]

    ,b.[CREATE_DTTM]

    ,b.[ANGLE_VALUE]

    FROM [MCS_MESDB].[dbo].[TB_RESULT_TOOL_RCM01] b (NOLOCK)

    LEFT JOIN [MCS_MESDB].[dbo].[TB_CODE_ITEM] w (NOLOCK)

    ON w.ITEM_CODE = b.ITEM_CODE

    WHERE ORDER_DATE BETWEEN @START_DATE AND @END_DATE AND

    b.WORK_RESULT = 'NG'

    ORDER BY ORDER_DATE DESC, COMMIT_NO DESC

  • I forgot

    I rebuild indexes and update statistics, but I have to wait for breaktime for update statistics on TOOL table ( I have one hour to breaktime, so then I will try and I will see if it will disappear also..

    I tried the hint OPTION(RECOMPILE) and Parallelism disappear from ex.plan and there is just Hash Match ...

  • So I tried with this script for creating tables and etc on our test database and it looks that it is due to update statistics from TB_RESULT_TOOL, to this table are inserted every second few rows. So this can be mainly problem.

    So parallelism isnt here and Hash join also not...

    Important is that I know where is problem. 🙂

  • So still not solved, I tried update statistics on Production and nothing

    I have 2test database

    1. almost same data like production and I tried this select and is not function - I dont understand why there is sort operation, when I sorted according to first and second column from PK

    2. I created new database with table script for data what I posted above.

    3. is production. ex plan is on post above...

    ON all three DB I have different ex.plan...

    on all servers I updated statistics and then rebuild indexes.

    So best result is on point 2. with just 1000 rows. But why I cannot do same ex.plan?

    Why same select shows in ex plan right outher join .. number of rows in table can cause this problem?

    I think that problem is due to variable ..

    On PRODUCTION server with most data, if I make query with directly values or if I use OPTION(RECOMPILE), In execution plan are Sort and Hash Match(Righ Outer Join)..

    Is it any solution how can I do this ? Rebuild and update statistics were done. So I am little confuse now hah 😀

    sorry for english..

  • I was reading few articles about it and I tried solution with dynamic sql, but I dont want to use, Somebody knows next solution ?..

    With this I got right estimated values and if I compare in normal query is 4.74501 vs 0.725041 Estimated subtree cost ( 87% vs 13% in ex.plan)

    set @sql='

    SELECT b.[ORDER_DATE]

    ,b.[COMMIT_NO]

    ,b.[BODY_NO]

    ,b.[CAR_CODE]

    ,b.[LINE_CODE]

    ,b.[STATION_ID]

    ,b.[WORK_SEQ]

    ,b.[POINT_SEQ]

    ,b.[POINT_SER]

    ,b.[WORK_CODE]

    ,b.[WORK_POS]

    ,b.[WORK_TYPE]

    ,b.[WORK_QTY]

    ,b.[ITEM_CODE]

    ,w.[ITEM_NAME]

    ,b.[ALC_CODE]

    ,b.[LIMITV_LOW]

    ,b.[LIMITV_HIGH]

    ,b.[WORK_VALUE]

    ,b.[WORK_RESULT]

    ,b.[CREATE_DTTM]

    ,b.[ANGLE_VALUE]

    FROM [MCS_MESDB].[dbo].[TB_RESULT_TOOL_RCM01] b (NOLOCK)

    INNER JOIN [MCS_MESDB].[dbo].[TB_CODE_ITEM] w (NOLOCK)

    ON w.ITEM_CODE = b.ITEM_CODE

    WHERE ORDER_DATE BETWEEN '''+@START_DATE+''' AND '''+@END_DATE+''' AND

    b.WORK_RESULT = ''NG''

    ORDER BY ORDER_DATE DESC, COMMIT_NO DESC

    '

    select @sql

    execute(@sql)

  • One thing you can do to help with parallelism is change the cost threshold for parallelism from the default value. It's 5 and that's way to low for most systems. If this is a reporting system, I'd suggest bumping it up to 20-25. If it's an OLTP system, I'd go as high as 50.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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