Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parallelism in query Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 5:55 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
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




  Post Attachments 
test.sqlplan (4 views, 90.27 KB)
sql.txt (1 view, 233.11 KB)
Post #1551418
Posted Friday, March 14, 2014 6:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
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 ...

Post #1551419
Posted Friday, March 14, 2014 6:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
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.
Post #1551420
Posted Friday, March 14, 2014 7:03 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
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..


  Post Attachments 
test1.sqlplan (0 views, 92.38 KB)
test2.sqlplan (0 views, 92.38 KB)
Post #1551424
Posted Friday, March 14, 2014 8:29 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
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)

Post #1551430
Posted Friday, March 14, 2014 8:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:08 AM
Points: 171, Visits: 500
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
http://www.codeproject.com/Articles/459567/SQL-Server-How-Local-Variables-Can-Reduce-Query-Pe
Post #1551431
Posted Saturday, March 15, 2014 4:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 15,664, Visits: 28,062
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1551458
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse