New answers tagged

8

am I correct in thinking that LOB_DATA pages can cause slow scans not only because of their size, but also because SQL Server can't scan the clustered index effectively Yes, reading LOB data not stored in-row leads to random IO instead of sequential IO. The disk performance metric to use here to understand why it is fast or slow is Random Read IOPS. ...


22

Not all reads are equal. SQL Server knows that accessing LOB data is expensive, and tries to avoid it when possible. There are also detailed differences in the way the LOB data is read in each case: Summary The numbers are different because: The select reads the LOB in packet-sized chunks The variable assignment test does not read the LOB at all The ...


8

Presence of XML field causes most of the table data to be located on LOB_DATA pages (in fact ~90% of table pages are LOB_DATA). Merely having the XML column in the table does not have that effect. It is the presence of XML data that, under certain conditions, causes some portion of a row's data to be stored off row, on LOB_DATA pages. And while one (or ...


4

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, SQL Server will probably be waiting with one of the PAGEIOLATCH_XX wait types. It definitely appears that the LOB data must be loaded into memory on the ...



Top 50 recent answers are included