Software Developer/Dev DBA. Often hyper, often talking rubbish. Pick and choose what to take away from this blog.... Want to get in touch? My social network profiles are listed above.

Odd Behavior With LOB/Overflow Data

Feb 20, 2019

In writing some sample demos around LOB and Row-Overflow data I found a couple of oddities in the way reads are reported in STATISTICS IO and query plans, There is every chance I’m missing something obvious here and if that is the case then please let me know as I’d love to understand this more.

The following is what I have observed on SQL Server 2017…

If I turn on Statistics IO and query a table with no LOB allocations but some Row-Overflow allocations I see LOB logical reads occurring
If I run that same query and look at the actual execution plan under Actual I/O Statistics no LOB reads are reported
If I create a new table with a column that will be put in LOB storage the query plan still reports zero LOB logical reads even though STATISTICS IO returns a number greater than zero.

Let’s look at these examples…

Setup

First lets create a sandbox database…

CREATE DATABASE AllocationUnitSandbox
GO
USE AllocationUnitSandbox
GO

Then lets add a little stored procedure we can run throughout our examples to see the allocation units…

CREATE PROCEDURE GetAllocationUnits AS
SELECT  
   o.name ObjectName,
   i.name IndexName,
   i.type_desc IndexType,
   au.type_desc AllocationUnitDesc,
   au.total_pages AllocationUnitTotalPages,
   au.used_pages AllocationUnitUsedPages,
   fg.name AS FileGroupName
FROM 
   sys.allocation_units au
   LEFT JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
   LEFT JOIN sys.partitions p ON
      (au.type_desc IN ('IN_ROW_DATA','ROW_OVERFLOW_DATA') AND p.hobt_id = au.container_id)
      OR (au.type_desc IN ('LOB_DATA') AND p.partition_id = au.container_id)
   LEFT JOIN sys.objects o ON o.object_id = p.object_id
   LEFT JOIN sys.indexes i ON 
      i.object_id = o.object_id
      AND i.index_id = ISNULL(p.index_id,0)
WHERE
   o.[type] NOT IN ('S','IT') /*SystemTable, InternalTable*/

In-Row Demo

Before we look at any Row-Overflow or LOB allocation units lets take this simple example where all the data fits In-Row.

CREATE TABLE InRow(Field1 VARCHAR(100), Field2 VARCHAR(100))
INSERT INTO InRow(Field1, Field2)
VALUES(REPLICATE('a',100), REPLACE('b',100))

Then lets check out GetAllocationUnits procedure…

EXEC GetAllocationUnits

In Row

We can see we have a single allocation unit for In-Row pages on this table. If we now run a SELECT * with statistics IO turned on we should see a single logical read…

SET STATISTICS IO ON
SELECT * FROM InRow

In Row Single Read

Bingo.

Overflow-Row Demo

Let’s now create a table with no fields that qualify for LOB but enough variable length for us to cause overflows…

CREATE TABLE Overflow
(
    Field1 VARCHAR(100),
    Field2 VARCHAR(8000)
)
INSERT INTO Overflow(Field1,Field2)
VALUES(REPLICATE('a',100),REPLICATE('b',8000))

If we then run our GetAllocationUnits procedure we should see that we have some data in row and some in overflow due to the fact we can’t fit our 100 length field1 and our 8000 length field2 on a single page…

EXEC GetAllocationUnits

Overflow Allocation Unit

If we then run a select * we’d expect to have to read at least 2 pages, one from the In-Row allocation unit and one from our Overflow-Row allocation unit…

SET STATISTICS IO ON
SELECT * FROM Overflow

Overflow LOB Read

I’m still not sure about this reporting as a LOB read when it’s really Row-Overflow but I guess it is what it is. What I really find odd is that if we run the above query again but turn on actual query plans we see no LOB reads…

No LOB Execution Plan

LOB Data Demo

Where this gets even weirder is if we then create another table that has real LOB data…

CREATE TABLE LOB
(
    Field1 VARCHAR(100),
    Field2 VARCHAR(MAX)
)
INSERT INTO LOB(Field1,Field2)
VALUES(REPLICATE('a',100), REPLICATE('b',10000))

EXEC GetAllocationUnits

LOB Allocation Units

Now let’s do select * again…

SET STATISTICS IO ON
SELECT * FROM LOB

LOB Logic Reads

As expected we can see our LOB reads however if we switch back to our actual execution plan…

LOB Not On Execution Plan

Still now LOB reads showing under Actual I/O Statistics.

I’ve even tried running the insert statement multiple times to increase the page count, STATISTICS IO ON correctly reports the read pages but my actual execution plan stays the same with zero LOB pages read. Weird Right?

SQL Server, What's In My Buffer Cache?

Feb 19, 2019

When SQL Server reads pages it stores them in an area of memory called the buffer cache, things like memory pressure can then cause items to get removed from the buffer cache. I wrote the below script to check what’s in the cache at any given time, it’s scoped to the database you are running it in…

SELECT 
    o.name [Table],
    i.name [Index],
    (COUNT(*) *8)/1024 [SizeMB]
FROM
    sys.allocation_units au 
    INNER JOIN sys.dm_os_buffer_descriptors bd ON au.allocation_unit_id = bd.allocation_unit_id
    INNER JOIN sys.partitions p ON  
        /* allocation_unit types : 
            0=Dropped, 
            1 = In Row, 
            2=LOB, 
            3=Row-Overflow data*/
        (au.[type] IN (1,3) AND au.container_id = p.hobt_id) 
        OR (au.[type] = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    LEFT JOIN sys.indexes i ON i.object_id = o.object_id AND p.index_id = i.index_id
 WHERE 
    o.type NOT IN ('S','IT') 
GROUP BY
    i.name,
    o.name

Backup Status Results

I find this really useful when looking at things like buffer hit ratios to then get an idea as to what in the buffer cache could be causing memory pressure.

SQL Server, What Waits Happened In The Last X Seconds

Feb 18, 2019

There are plenty of scripts out there that can show you the waits that are occurring on your server, however, a lot of them do a lot more than just that. I recently wanted a script that does just the absolute minimum for me to specify a time window and get that wait stats.

If you’ve ever looked at this you’ll probably know you can see the wait stats in dm_os_wait_stats, however, this is aggregated so in order to see them over a window of time we need to first take a snapshot of how the stats look at the start of our window and compare it to the end. I’ve also included a number of wait types to filter out which are primarily based around ones I’ve not wanted to see so you will probably want to change this for your environment…

/* HH:MM:SS */
DECLARE @DelayString NVARCHAR(8) = '00:00:05'

SELECT * INTO #WaitTypesToIgnore
FROM(
   SELECT 'SLEEP_TASK' AS WaitType
   UNION ALL SELECT 'DIRTY_PAGE_POLL'
   UNION ALL SELECT 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
   UNION ALL SELECT 'LOGMGR_QUEUE'   
   UNION ALL SELECT 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
   UNION ALL SELECT 'ONDEMAND_TASK_QUEUE'
   UNION ALL SELECT 'FT_IFTSHC_MUTEX'
   UNION ALL SELECT 'REQUEST_FOR_DEADLOCK_SEARCH'
   UNION ALL SELECT 'LAZYWRITER_SLEEP'
   UNION ALL SELECT 'SOS_WORK_DISPATCHER'
   UNION ALL SELECT 'CHECKPOINT_QUEUE'
   UNION ALL SELECT 'XE_TIMER_EVENT'
   UNION ALL SELECT 'FT_IFTS_SCHEDULER_IDLE_WAIT'
   UNION ALL SELECT 'BROKER_TO_FLUSH'
   UNION ALL SELECT 'BROKER_TASK_STOP'
   UNION ALL SELECT 'BROKER_EVENTHANDLER'
   UNION ALL SELECT 'WAITFOR'
   UNION ALL SELECT 'DBMIRROR_DBM_MUTEX'
   UNION ALL SELECT 'DBMIRROR_EVENTS_QUEUE'
   UNION ALL SELECT 'DBMIRRORING_CMD'
   UNION ALL SELECT 'DISPATCHER_QUEUE_SEMAPHORE'
   UNION ALL SELECT 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
   UNION ALL SELECT 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'
   UNION ALL SELECT 'SP_SERVER_DIAGNOSTICS_SLEEP'
   UNION ALL SELECT 'XE_DISPATCHER_WAIT'
   UNION ALL SELECT 'REQUEST_FOR_DEADLOCK_SEARCH'
   UNION ALL SELECT 'SQLTRACE_BUFFER_FLUSH'
   UNION ALL SELECT 'XE_DISPATCHER_WAIT'
   UNION ALL SELECT 'BROKER_RECEIVE_WAITFOR'
   UNION ALL SELECT 'CLR_AUTO_EVENT'
   UNION ALL SELECT 'DIRTY_PAGE_POLL'
   UNION ALL SELECT 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
   UNION ALL SELECT 'CLR_MANUAL_EVENT'
) x

SELECT dm_os_wait_stats.* 
INTO #StartStats
FROM 
   sys.dm_os_wait_stats
   LEFT JOIN #WaitTypesToIgnore ON dm_os_wait_stats.wait_type = #WaitTypesToIgnore.WaitType
WHERE 
   #WaitTypesToIgnore.WaitType IS NULL

WAITFOR DELAY @DelayString

SELECT 
   [now].wait_type,
   [now].waiting_tasks_count - ISNULL([before].waiting_tasks_count,0) waiting_tasks_count,
   [now].[wait_time_ms] - ISNULL([before].wait_time_ms,0) wait_time_ms,
   [now].[max_wait_time_ms] - ISNULL([before].max_wait_time_ms,0) max_wait_time_ms,
   [now].[signal_wait_time_ms] - ISNULL([before].signal_wait_time_ms,0) signal_wait_time_ms
FROM 
   sys.dm_os_wait_stats [now]
   LEFT JOIN #StartStats [before] ON [before].wait_type = [now].Wait_type
   LEFT JOIN #WaitTypesToIgnore ON #WaitTypesToIgnore.WaitType = [now].Wait_type
WHERE
   #WaitTypesToIgnore.WaitType IS NULL AND
   (
       ([now].waiting_tasks_count - ISNULL([before].waiting_tasks_count,0)) > 0
       OR ([now].[wait_time_ms] - ISNULL([before].wait_time_ms,0)) > 0
       OR ([now].[max_wait_time_ms] - ISNULL([before].max_wait_time_ms,0)) > 0
       OR ([now].[signal_wait_time_ms] - ISNULL([before].signal_wait_time_ms,0)) > 0 
   )
ORDER BY  [now].[wait_time_ms] - ISNULL([before].wait_time_ms,0) DESC

Previous Page: 1 of 51 Next