Hi,

SQL Server 2005, SP2, 32 bit, 4 processors

Stored proc runs and never finishes...

I am seeing a lot of CXPACKET wait times on an UPDATE statement in a
stored procedure. Research on the internet seems to point to poor
usage of parallelism among the processors, or something to that
effect.

Various "cures" include tuning the query, adding indexes, adding a
hint to turn off the parallelism and redoing the statistics.

Has anyone here seen this problem, and how did you resolve it?

Thanks much

Typically, if you see > 1 CXPACKET for a given SPID, then parallelism is
working against you. As a start, use OPTION (MAXDOP 1) for those queries
where you are observing this. Consider using the Database Tuning Advisor
for indexing suggestions.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"sqlgirl" <tootsuite@gmail.com> wrote in message
news:1173380096.353536.70770@p10g2000cwp.googlegro ups.com...
Hi,

SQL Server 2005, SP2, 32 bit, 4 processors

Stored proc runs and never finishes...

I am seeing a lot of CXPACKET wait times on an UPDATE statement in a
stored procedure. Research on the internet seems to point to poor
usage of parallelism among the processors, or something to that
effect.

Various "cures" include tuning the query, adding indexes, adding a
hint to turn off the parallelism and redoing the statistics.

Has anyone here seen this problem, and how did you resolve it?

Thanks much