Query Performance Issue with multiple unions and a view

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 28, 2016 at 7:42 am

  #1867967

  chandan_jha18 (3/28/2016)

  ---

  Dear All,

  Today while running the procedure in its original form, which takes around 10 seconds to output 100 rows for a particular set of parameters, I opened a new query window and ran the following:

  select * from sys.dm_os_waiting_tasks where session_id=my spid.

  This was giving waits on 'CXPACKETS' . I tried to interpret the resource_description field which had similar values:

  exchangeEvent id=Pipec9dabf000 WaitType=e_waitPipeGetRow nodeId=134

  exchangeEvent id=Pipec9dabf000 WaitType=e_waitPipeGetRow nodeId=134

  Out of a bad habit of using maxdop hint when seeing such wait types, I put a hint in the procedure with MAXDOP(0) and the query is able to return results within 1 second.

  So I have two possible solutions at this point:

  - Use a permanent cache table to populate the data from view every 15 or 30 minutes(takes 5-10 seconds) and refer that in place of view in the procedure code

  - Use maxdop hint.

  Sorry for the long break, we had a major festival last week:-)

  Regards

  Chandan

  All the MAXDOP hint is doing is allowing it to consume as many resources as it wants. If it used all CPUs for a full second, would that still be desirable? Probably not.

  The cache thing is a good short term solution but I'd still be digging into the code and the related indexes.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• chandan_jha18

  SSChampion

  Points: 11330

  More actions

  March 28, 2016 at 8:16 am

  #1867979

  Jeff: "All the MAXDOP hint is doing is allowing it to consume as many resources as it wants. If it used all CPUs for a full second, would that still be desirable? Probably not.

  The cache thing is a good short term solution but I'd still be digging into the code and the related indexes."

  I am really sorry for my mistake here. I wanted to type MAXDOP(1) hint that I used to get the performance to the point where I wanted it.

  single threaded operation, but you are right Sir. I will keep trying to focus on the original query to find out the possible solution.