Bug = SQL hangs in plan creation for MAX(DATALENGTH(varbinmax))

  • I have a base table with 4 varbinary(max) columns (COMPRESSed columns that were originally nvarchar(max)).  As full background, there is an INSTEAD OF INSERT trigger to transparently handle converting the incoming nvarchar(max) to varbinary(max).  And a view that is used for all standard SELECTs that transparently converts the varbinary(max) back to nvarchar(max).

    I wanted to get the max lengths of the compressed columns for the first, say, 200 rows.  But when I try to get the Estimated Query Plan below, SSMS just spins endlessly.  However, TOP (1) does run ... but anything else, even TOP (2), just hangs.

    /* just spins endlessly, waited over 15 min and still no estimated exec plan */
    SELECT MAX(DATALENGTH(varbinmax_col1)), MAX(DATALENGTH(varbinmax_col2)),
    MAX(DATALENGTH(varbinmax_col3)), MAX(DATALENGTH(varbinmax_col4))
    FROM (
    SELECT TOP (200) *
    /* SELECT TOP (1) * --this works completely: plan gets created and run */
    FROM dbo.base_table_with_compressed_varbinmax_columns
    ) AS x

    Hmm, this hangs too, interesting:

    SELECT MAX(DATALENGTH(varbinmax_col1))
    FROM dbo.base_table

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • How long does

    SELECT DATALENGTH(varbinmax_col1)
    FROM dbo.base_table

    take to run?

  • Jonathan AC Roberts wrote:

    How long does

    SELECT DATALENGTH(varbinmax_col1)
    FROM dbo.base_table

    take to run?

    Here's the TIME statistics for:

    SELECT TOP (200) DATALENGTH(varbinmax_col1)

    (200 rows affected)

    SQL Server Execution Times:

    CPU time = 16 ms, elapsed time = 1 ms.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply