March 14, 2014 at 5:55 pm
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
March 14, 2014 at 6:05 pm
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 ...
March 14, 2014 at 6:13 pm
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. 🙂
March 14, 2014 at 7:03 pm
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..
March 14, 2014 at 8:29 pm
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)
March 15, 2014 at 4:11 am
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