CODEWITHSUNDEEP
sql-serversql-server-2008
David Shochet
David Shochet

Reputation: 5385

Where clause with varbinary type doesn't work

I have a table MyTable with a field MyField, which is varbinary(max). I have the following query:

SELECT COUNT(*) FROM MyTable WHERE MyField IS NOT NULL

The SELECT clause can have anything, it does not matter. Because of the varbinary MyField in the Where clause, the execution never ends.

I tried even this:

SELECT Size
FROM
(
    SELECT ISNULL(DATALENGTH(MyField), 0) AS Size FROM MyTable
) AS A
WHERE A.Size > 0

The inner query works fine, the whole query without the Where clause works fine, but with that Where clause it is stuck. What is the reason for this?

Upvotes: 1

Views: 983

Answers (2)

swasheck
swasheck

Reputation: 4693

Just for fun and giggles I decided to toss this one out to you. I'm taking some things for granted here but you can look at tasks that had to wait. Look for wait types that begin with LCK_ as these will be your blocked tasks. Please note that on a busy server the ring buffer may have rolled over after a while. Also note that this is intended to supplement @AaronBertran's excellent answer and in no way meant to supplant it. His is more comprehensive and is the correct way to identify the issue while it's happening.

        SELECT 
            td.r.value('@name','sysname') event_name,
            td.r.value('@timestamp','datetime2(0)') event_timestamp, 
            td.r.value('(data[@name="wait_type"]/text)[1]','sysname') wait_type,
            td.r.value('(data[@name="duration"]/value)[1]','bigint') wait_duration,
            td.r.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
            td.r.query('.') event_data
        FROM (
                    SELECT
                    CAST(target_data AS XML) target_data
                    FROM sys.dm_xe_sessions s
                    JOIN sys.dm_xe_session_targets t
                        ON s.address = t.event_session_address

                    WHERE s.name = N'system_health'
                        and target_name = 'ring_buffer'
                    ) base
        CROSS APPLY target_data.nodes('/RingBufferTarget/*') td(r)
        where td.r.value('@name','sysname') = 'wait_info';

Upvotes: 5

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

Don't think or assume that it couldn't possibly be blocking, just because a different query returns immediately. With a different where clause, and a different plan, and possibly different locking escalation, you could certainly have cases where one query is blocked and another isn't, even against the same table.

The query is obviously being blocked, if your definition of "stuck" is what I think it is. Now you just need to determine by who.

In one query window, do this:

SELECT @@SPID;

Make note of that number. Now in that same query window, run your query that gets "stuck" (in other words, don't select a spid in one window and expect it to have anything to do with your query that is already running in another window).

Then, in a different query window, do this:

SELECT blocking_session_id, status, command, wait_type, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE session_id = <spid from above>;

Here is a visualization that I suspect might help (note that my "stuck" query is different from yours):

click to embiggen

If you get a non-zero number in the first column, then in that different query window, do this:

DBCC INPUTBUFFER(<that blocking session id>);

If you aren't blocked, I'd be curious to know what the other columns show.

As an aside, changing the WHERE clause to use slightly different predicates to identify the same rows isn't going to magically eliminate blocking. Also, there is no real benefit to doing this:

SELECT Size
FROM
(
    SELECT ISNULL(DATALENGTH(MyField), 0) AS Size FROM MyTable
) AS A
WHERE A.Size > 0

When you can just do this:

SELECT ISNULL(DATALENGTH(MyField), 0) AS Size 
  FROM dbo.MyTable -- always use schema prefix
  WHERE DATALENGTH(MyField) > 0; -- always use semi-colon

SQL Server is smart enough to not calculate the DATALENGTH twice, if that is your concern.

Upvotes: 6

Related Questions