Monday, November 8, 2010

Blobs and covering indexes

Nowadays it is common to store/save pictures,documents in the databbase. Since SQL Server 2005 we use VARBINARY(MAX) datatype to store such data. The 'problem' I have seen recently at the client database is SELECT statement on very huge table that contain BLOB data and that data needs to be return to the client works pretty slowly. Run the below
SET STATISTICS IO ON

SELECT col1,col2, blobdata FROM tbl WHERE....

Table 'tbl'. Scan count 1, logical reads 8125, physical reads 0, read-ahead reads 0, lob logical reads 261521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1252 ms, elapsed time = 12632 ms.

We see high number of reads to return the data from index/blob pages.

Execution plan shows that there is Bookmark to return the BLOB data from Clustered Index Key because of our NCI(NonClustered Index) does not cover all columns in SELECT statement.
Ok, at first glance you would re-create NCI to INCLUDE blobdata column to 'cover' SELECT ...Well, I noticed that recreating NCI index takes long long time and LOG file was grown dramatically. I see that after INCLUDE blobdata column I reduced logical reads as shown below

Table 'tbl'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 421521, lob physical reads 0, lob read-ahead reads 0.
CPU time = 1622 ms, elapsed time = 16512 ms.

But what happened? BLOB page reads were done almost 1.8 times more with covering index and actually I have not seen much improvment in performance of the query.
So finally, if you create an index or INCLUDE the BLOBs SQL Server create copy of that index for every blob column and thus it takes time to create index and reading the data from index page. Well, you were able to save some IO by covering BLOB column but internally SQL Server works much hardly to return the data and maintain the index.

2 comments:

Nike shox said...

You built some beneficial details there. I did a look for within the matter and observed most folks will consent with your webpage.Having a look Nike shox. Good things or not you have the final say.

aiya said...

Office 2010is powerful!
Microsoft Office 2010is the best software in the world.
Microsoft wordis so great!
Office 2007makes life great!
Many people likeMicrosoft Office.
Microsoft Office 2007is welcomed by the whole world.
Office 2007 keyis available here.
Office 2007 downloadis on sale now!
Office 2007 Professionalbring me so much convenience.
Outlook 2010 make life wonderful!
Microsoft outlook is my love.
Microsoft outlook 2010 is convenient!
Windows 7 is convenient!