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.
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.
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…
SETSTATISTICSIOONSELECT*FROMInRow
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…
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…
EXECGetAllocationUnits
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…
SETSTATISTICSIOONSELECT*FROMOverflow
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…
LOB Data Demo
Where this gets even weirder is if we then create another table that has real LOB data…
As expected we can see our LOB reads however if we switch back to our actual 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?
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…
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.
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…