1

I have a table which caches images from another data source:

CREATE TABLE [dbo].[ProductImages] (
    [ProductId] INT NOT NULL , 
    [ImageId] INT NOT NULL, 
    [Image] VARBINARY(MAX) NOT NULL, 
    PRIMARY KEY ([ProductId], [ImageId])
)

The images are served together with [Product] data in the form of:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Now, the problem is that the images from the data source may sometimes be updated, and there is no trigger-like or event-like mechanism to detect this change. So, my solution is to have a scheduled job which periodically downloads images from the source. Since the images are pre-processed (format changed, resized) before being stored into the database, I have no way of actually knowing whether the image has changed. Therefore, every time I do the following:

DELETE FROM [ProductImages] WHERE [ProductId] = 123
INSERT INTO [ProductImages] VALUES (123, 0, {Image_0})
INSERT INTO [ProductImages] VALUES (123, 1, {Image_1})

Most of the time (say at least 98%), there has been no change - I am just deleting and re-inserting the same data for nothing.

If this scheduled job is executed long enough, will it eventually lead to poor table performance because the data is scattered all over the place?

8
  • @sp_BlitzErik the specific case here is that I am deleting as many rows as I am inserting, so my guess is SQL Server should be smart enough to utilize the deleted empty spaces that "just fit" the inserted data. But I am not sure, hence I am asking.
    – kevin
    May 28 '17 at 1:18
  • what part of that can you not measure when your workload runs? May 28 '17 at 1:32
  • How big is a typical image?
    – Joe Obbish
    May 28 '17 at 3:02
  • @JoeObbish around 10kb~17kb
    – kevin
    May 28 '17 at 3:49
1

Let's consider what data will actually be stored on the data pages. You said that your images are around 10 KB - 17 KB which means that the VARBINARY(MAX) column will never be stored in row. Instead, it will always be stored as LOB data. So on the in row page for each row you'll have two INT columns along with a 24 byte pointer to the LOB data. Assuming a generous overhead of 10 bytes per row you can fit around 8000 / (10+4+4+24) = 190 rows on a single page. The row data on each page may be physically out of order and the different data pages may not be in order but SQL Server won't just insert the rows wherever it can find space. It's not true that for a given ProductId you'll have ImageId 1 stored on page 1, ImageId 2 stored on page 2, ImageId stored on page 3, and so on. So when you run the following query:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Unless you have hundreds of images for one product, all of the IO is going to be from going to the LOB pages to get the [Image] data. There will only be one or two relevant pages that store the pointers to the LOB data. I don't know how SQL Server picks which page is used for the LOB data. I suppose it's possible for the LOB pages to become more spread out over time.

No one here can say for sure if fragmentation will be a problem for you. As I see it your options are to schedule a maintenance job that deals with any fragmentation, to add code to your application to prevent unnecessary updates, or to simulate your workload to see if you need to take further action. From SQL Server's point of view it doesn't matter that the VARBINARY data corresponds to an image. Anything that you store to the column of the right size should work fine for a test.

2
  • Why I ask: If I know the table will get fragmented, I'd prefer to add code to application logic such that it detects when an update is necessary, instead of blindly re-inserting everything. I am hoping to evaluate the approach at design stage.
    – kevin
    May 28 '17 at 5:11
  • @Joe Obbish, you are right that those varbinary values will not be stored in row, but they will not be stored as row overflow data as well, and it's even written in the BOL article you've posted: This restriction does not apply to varchar(max), nvarchar(max), varbinary(max), text, image, or xml columns. Those values vill be stored as LOB data on LOB pages, you can see it using sys.allocation_units or usind DBCC IND
    – sepupic
    May 29 '17 at 11:01
0

What fragmentation do you mean?

If you ask about PK fragmentation the answer is yes, it's possible. Your binary values are about 10kb~17kb, as you said, and those never go in row but always go to LOB pages. When LOB data (varbinary(max)) fits in 5 pages (32Kb, your case) all the (max 5) pointers to LOB data go in row, so if you pass from image of 8Kb to 16Kb, your row grows at 1 pointer size; when LOB data size is greater than 5pages it's different, only 1 pointer goes in row that poits to the LOB structure root(which contains other pointers to LOB pages), but it's not your case.

If you ask about LOB pages themselves, even if SQL Server allocates pages for them in contiguous pages it will never read them efficiently. LOB data storage is not optimized, and server always spent more time to read them. When LOB value has more than 5 pages, LOB data is organized as a binary tree but it's not an efficient tree as used for indexes. And even when LOB value is less than 5 pages and the pointers themselves are stored in row, reading LOB data is slow. When you delete and re-insert LOB values, it will allocate new pages for new LOB values, but I repeat, even if there will be contiguous pages, the reading will be slower than in-row data pages

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.