I have a table that is used heavily. This table has 370,370 records.

When I run the following select:

select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages] 
group by [ImageSize] 
order by [ImageSize] 

I get this result:

enter image description here

Basically there are only 5 different imageSize objects in that table.

This is the current table definition:

IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL 
DROP TABLE [dbo].[ProductImages] 

CREATE TABLE [dbo].[ProductImages] ( 
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]  
                          [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc, 
                          [SortOrder] asc, [ImageFormat] asc, 
                          [ImageSize] asc))


And here are the definitions of the current inexes

  • 1 clustered index

  • 4 non clustered indexes

  • 1 filtered index

  • and the primary key non clustered defined above

                 ON [dbo].[ProductImages] ([ProductImageID] asc)
                 ON [dbo].[ProductImages] 
          ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
          [ImageSize] asc, [ImageFormat] asc, 
          [BodenImageURL] asc)
                 INCLUDE ([BodenDomainURL])
          ON [dbo].[ProductImages] 
          ([ImageFormat] asc, [ImageSize] asc)
                 INCLUDE ([SeasonItemID], [Language],
           [Tier1], [Tier2], [BodenDomainURL], 
          ON [dbo].[ProductImages] 
          ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc, 
          [ImageFormat] asc)
                 INCLUDE ([ProductImageID], [BodenImageURL])

This is a filtered index:

        ON [dbo].[ProductImages] 
        ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
        [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
               INCLUDE ([BodenImageURL])
               WHERE ([ImageWidth]=(195) 
        AND [ImageHeight]=(252) 
        AND [ImageFormat]='ProductMain')

Can I expect significant gains by changing the 4 non clustered indexes above to 20 FILTERED INDEXES according to the imageSize?

Most queries use the ImageSize as one of the parameters. It is even in the clustered index.

I could modify the indexes and table structure.

This table is mostly used for reading. There is only 1 write a day, and that would not normally affect many records.

  • 3
    You say that "most queries use the ImageSize as one of the parameter", yet there is no index with ImageSize as the first column. Why? – ypercubeᵀᴹ Jul 20 '16 at 12:13
  • 1
    There's only 300k records in your table, so reading it should be fast, if your indexes are even somewhat correct. They look a little strange, but like I said yesterday, to really understand your problem, you should include the queries you're having trouble with and/or the most common ones. – James Z Jul 20 '16 at 12:25
  • 1
    I wouldn't add a filtered index on a column with that evenly distributed values. As @ypercubeᵀᴹ indicates, you will probably get better results by changing your indexes to better suit your queries. – Jonathan Fite Jul 20 '16 at 12:46
  • 1
    Keep in mind there's a number of issues with filtered indexes, the optimizer will not always use them even if you met the conditions. @JamesZ also points out that your table is relatively small. If your image data is too large, consider splitting the image metadata into a separate table from the image data, and connecting the tables by foreign keys. – Arthur D Jul 20 '16 at 13:02
  • 2
    @ArthurD What I can figure out, this is just the metadata with the URLs, no actual images there. The urls are probably wasting space being nvarchars, but otherwise it should be really small table. – James Z Jul 20 '16 at 13:09

Significant gains? No, I shouldn't think so.

SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-limiting factor is often the number of levels there are in the index and, hence, the number of pages (each index node is its own page) that have to be read. This, in turn is controlled by the fan-out, the number of lower-level pages a higher-level page can reference, which is itself determined by the total size of the key. Let's do some hand-wavy maths.

Index IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL is defined as

Column          Definition     Effective bytes  Note
------------    -------------  ---------------  -----------------------------------
SeasonItemID    VARCHAR(5)                   5  Use the full size
Tier1           VARCHAR(10)                  5  Assume 50% of defined size
Tier2           VARCHAR(10)                  5  Assume 50% of defined size
ImageSize       VARCHAR(10)                  7  Minimum from given data
ImageFormat     VARCHAR(20)                 10  Assume 50% of defined size
BodenImageURL   NVARCHAR(210)              210  Assume 50% of defined size, 2 bytes per character.
Total                                      242 bytes

I'll ignore the included column for fan-out since it's only present in the leaf nodes. Since there's about 8060 usable bytes per page that gives roughly 8060 / 242 = 33 rows per page. So a one-level index can reference up to 33 rows, a two-level index can reference 33 * 33 = 1,089 rows, a three-level 35,937 rows, and a four-level 1,185,921 rows. Given your counts of 74,000 rows per picture size and 370,000 rows in total you'll need a four-level index1 for both filtered and non-filtered indexes. There will be no reduction in the number of pages referenced and so no improvement in run-time.

These numbers are a lower limit as the included columns and the column of the clustered index will take further space in the leaf pages of the non-clustered index.

Adding these indexes will introduce risks, however. One is that the optimizer can be finicky about when it uses them. Another is that bringing on a new size (a data change) will require five more indexes to be defined (a DDL change). This is unwelcome maintenance overhead but without performing it the new-sized images will, in effect, be un-indexed. Schema changes will be complicated by the additional objects. Index maintenance will be slightly more complicated, if scripted manually. The overhead of the upper level of the indexes will increase the DB size fractionally, and with it backup and restore times.

1For the value used by an actual index see sys.dm_db_index_physical_stats.index_depth (docs).

| improve this answer | |
  • +1 great methodological approach and technical knowledge, thanks for taking the time to answer this question in such a detailed way. – Marcello Miorelli Jul 7 at 13:01

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.