Re: 100% CPU Util on Simple Update Query

From: Joel H (JoelH_at_discussions.microsoft.com)
Date: 02/15/05 

Date: Tue, 15 Feb 2005 06:55:08 -0800

ITS BAACK. Yesterday, I had the same problem.
The query in full looks like this:

UPDATE IMPT_ZACKS_01

SET DATEOFFISCALQUARTER1END = (CASE WHEN SWITCH = -1 THEN
[DATEOFFISCALQUARTER0END] ELSE [DATEOFFISCALQUARTER1END] END),
Q1MEDIAN = (CASE WHEN [SWITCH] = -1 THEN [Q0MEDIANCURRENT] ELSE
[Q1MEDIAN] END),
Q1NBROFESTIMATES = (CASE WHEN [SWITCH] = -1 THEN [Q0NBROFESTIMATES] ELSE
[Q1NBROFESTIMATES] END),
PREVFISCQTRQ3ENDMO = (CASE WHEN [SWITCH] = -1 THEN [PREVFISCQTRENDMO]
ELSE [PREVFISCQTRQ3ENDMO] END),
Q3ACTUALEPS = (CASE WHEN [SWITCH] = -1 THEN [Q4ACTUALEPS] ELSE
[Q3ACTUALEPS] END),
DATEOFFISCALQUARTER2END = (CASE WHEN [SWITCH] = -1 THEN
[DATEOFFISCALQUARTER1END] ELSE [DATEOFFISCALQUARTER2END] END),
Q2MEDIAN = (CASE WHEN [SWITCH] = -1 THEN [Q1MEDIAN] ELSE [Q2MEDIAN]
END) ,
Q2NBROFESTIMATES = (CASE WHEN [SWITCH] = -1 THEN [Q1NBROFESTIMATES]
ELSE [Q2NBROFESTIMATES] END) ,
PREVFISCQTRQ2ENDMO = (CASE WHEN [SWITCH] = -1 THEN [PREVFISCQTRQ3ENDMO]
ELSE [PREVFISCQTRQ2ENDMO] END) ,
Q2ACTUALEPS = (CASE WHEN [SWITCH] = -1 THEN [Q3ACTUALEPS] ELSE
[Q2ACTUALEPS] END) ,
DATEOFFISCALQUARTER3END = (CASE WHEN [SWITCH] = -1 THEN
[DATEOFFISCALQUARTER2END] ELSE [DATEOFFISCALQUARTER3END] END),
Q3MEDIAN = (CASE WHEN [SWITCH] = -1 THEN [Q2MEDIAN] ELSE [Q3MEDIAN]
END) ,
Q3NBROFESTIMATES = (CASE WHEN [SWITCH] = -1 THEN [Q2NBROFESTIMATES]
ELSE [Q3NBROFESTIMATES] END) ,
PREVFISCQTRQ1ENDMO = (CASE WHEN [SWITCH] = -1 THEN
[PREVFISCQTRQ2ENDMO] ELSE [PREVFISCQTRQ1ENDMO] END) ,
Q1ACTUALEPS = (CASE WHEN [SWITCH] = -1 THEN [Q2ACTUALEPS] ELSE
[Q1ACTUALEPS] END) ,
Q0SERIALDATE = (CASE WHEN [SWITCH] = -1 THEN [Q_1SERIALDATE] ELSE
[Q0SERIALDATE] END) ,
Q1SERIALDATE = (CASE WHEN [SWITCH] = -1 THEN [Q0SERIALDATE] ELSE
[Q1SERIALDATE] END) ,
Q2SERIALDATE = (CASE WHEN [SWITCH] = -1 THEN [Q1SERIALDATE] ELSE
[Q2SERIALDATE] END) ,
Q3SERIALDATE = (CASE WHEN [SWITCH] = -1 THEN [Q2SERIALDATE] ELSE
[Q3SERIALDATE] END)

Each row only works on that row.
I took the table, and tried to remove the key, then replace the key, then
save - hoping to rebuild the key. I got the following error message:
'IMPT_ZACKS_01' table
- Unable to create index 'PK_IMPT_ZACKS_01'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location: statisti.cpp:3222
Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
SPID: 53
Process ID: 5576
Maybe thats a clue?
TIA Joel