Denial of Service: BINARY_CHECKSUM of TEXT column hangs CPU: SQL 2K SP4

I was comparing two tables using a BINARY_CHECKSUM(*) function across the
rows when I ran into a problem. When one of my tables happened to have a
TEXT column in it, the query calculating the BINARY_CHECKSUM(*) pegged the
CPU at 100% and wouldn't let go, even when I killed Query Analyzer. Since it
was a dual proc server, it was only 50% CPU utilization overall. If I ran
the query twice in separate QA sessions, it pegged the whole server at 100%
CPU and I couldn't make any more SQL Server connections for that instance.
As a test, I let this go on overnight with no end in site for the CPU
utilization. In order to regain control of the instance, I had to kill the
SQLSERVR.EXE process in Task Manager on the server.

This is in SQL 2000 SP4 on a dual processor Intel server on top of Windows
2000 SP4.

I did a Google search and couldn't come up with any problems regarding a
TEXT column and the BINARY_CHECKSUM function. BOL says, regarding
BINARY_CHECKSUM:
"Noncomparable data types are text, ntext, image, and cursor, as well as
sql_variant with any of the above types as its base type." It doesn't say,
however, that it will take your CPU to 100% utilization and not let go.

Here is a sample scenario and query that duplicates my issue. Run it first
with the XSL column commented out and then run it after un-commenting the
XSL column to cause the problem. Be prepared to kill your SQLSERVR.EXE
process in Task Manager to recover the CPU.

And, oh by the way, since I did this in TEMPDB (you could do it with a #temp
table), essentially any login that
has any access at all to your SQL Server can lock it up and you will not
know what hit you since TEMPDB will be rebuilt after you kill SQLSERVR.EXE
and restart it. There were no error log message or event log messages to
give me a clue as to what was happening.

Chuck


USE TEMPDB
GO

select @@version -- Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

if exists (select * from dbo.sysobjects where id = object_id(N'[TESTXSL]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TESTXSL]
GO

if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TESTXSL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TESTXSL] (
[XSL_ID] [int] NOT NULL ,
[XSL] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_TESTXSL] PRIMARY KEY CLUSTERED
(
[XSL_ID]
)
)
END

GO

--
-- TABLE INSERT STATEMENTS
--
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 40, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 50, ' ' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 60, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 70, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 80, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 90, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 100, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 110, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 120, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 140, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 150, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 160, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 170, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 180, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 190, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 200, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 210, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 220, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 230, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 240, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 250, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 260, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 270, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 280, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 290, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 300, '' )
go
INSERT INTO TESTXSL ( XSL_ID, XSL )
VALUES ( 310, 'sa' )
go


SELECT *,BINARY_CHECKSUM(*) AS CHECKCODE from (
SELECT XSL_ID--,XSL
FROM tempdb.dbo.TESTXSL (nolock)
) B1





.

Relevant Pages