I just need assistance on how we can minimize the CXPACKET Wait Type that's causing SPIDs that's doing a Select statement to lock up (LCK_M_S). We found that the Blocking SPID is from those that have CXPACKET.
There are a few things you can do...
- Always keep your statistics up to date
- Run your updates in smaller batches
- Review your query plans to make sure you're getting the proper plan
The problem is not that you have CXPACKET waits - the problem is that you are trying to select data from table(s) that are being updated. The update statement is blocking the select until that update has completed.
With that said - your problem is how long the update is taking - so review that statement for performance issues and address those issues.
If you cannot do that and you absolutely need this select to run then you should consider looking into snapshot isolation. If you enable the option then you could use that isolation level for the select statement so it will not be blocked by the update statement.
One more note: if you find that running the update statement in a single thread actually works better - reducing the time the process takes then you could modify the update statement and add OPTION (MAXDOP 1) to force the update to not generate a parallel plan. Be aware that doing this could cause the update to take even longer than it is now so I would not recommend doing this without a lot of testing.
@jeffw8713, how long does a task generate a Lock Wait Type there are CXPACKETs before it? Because I've checked a few of my diags and having a Lock in my environment is mostly at random.
I'd look at setting Read Committed Snapshot on your Test environment (its a database setting, no code changes needed) and seeing if that tests OK. Your only real issue is that you do need to review the code for a couple of edge conditions (e.g. where data is updated, re-read and re-updated / deleted again, in the same statement block) and also that it may use significant space in TEMPDB.
For Repro you might be able to record SQL statements using SQL Profiler and then play them back (against a restored database from the exact start time of the recorded script).
I would make sure there isn't a TRIGGER on OrderHeader (or a subsequent step in the SProc that is issuing the UPDATE OrderHeader within the same transaction block) which is taking longer than is reasonable - e.g. generating AND sending (rather than putting-it-in-a-queue for another process to handle) an Email
CXPACKET waits are caused by parallel plans and do not indicate an issue or cause locks to take longer.
SQL Server had chosen a parallel plan for that update statement. Getting rid of these waits won't reduce how long the update takes...
If you cannot optimize the update then your only options to allow that select statement to process are the ones we have outlined already.