Hi
I am going to attend TechEd 2010 in Eilat next week. It is great opportunity to learn new things , meet new and old friends.It is my second TechEd and I am will be focusing on Data Platform direction and BI. Hope to see you there.
http://www.microsoft.com/israel/TechEd2010/Tracks/BI.aspx
Tuesday, November 23, 2010
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.
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.
Subscribe to:
Posts (Atom)
