Tuesday, November 23, 2010

HiI 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.

Monday, November 8, 2010

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

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

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.

About Me

The goal of this blog is to share my knowledge as a DBA/Developer in SQL Server area. Many years ago I started to collect some tips and tricks from many experts of SQL Server around the world as well as the scripts developed by myself. And I really want to share them with you. Also my intend is to help beginners as well as experienced people such I have been doing at microsoft forums. So please do not hesitate to write me comments or questions.