High IO on index, is there a more performant solution?

Question

I've got this query which used to take minutes to run and now takes like 6 seconds but it runs a thousands of time a day so I'd like to make it faster.

https://www.brentozar.com/pastetheplan/?id=SJMLjJOWm

It seems that over 99% of the I/O during this query is happening on one clustered index scan.

Is this normal ? This query is used enough to justify adding any extra indexes just for it so I'd like to know if I'm missing something obvious here.

The dbo.GROUP_CONCAT function comes from this github assembly project https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

SpecsProd table definition:

CREATE TABLE [dbo].[SpecsProd](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [specsID_1] [int] NULL,
    [specsID_2] [int] NULL,
    [specID] [int] NOT NULL,
    [productID] [int] NOT NULL,
    [SpecValue_1] [varchar](1000) NULL,
    [SpecValue_2] [varchar](1000) NULL,
    [Flock] [bit] NULL,
    [SpecValue_1a] [varchar](2000) NULL,
    [SpecValue_2a] [varchar](2000) NULL,
 CONSTRAINT [PK_SpecsProd] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SpecsProd] ADD  CONSTRAINT [DF_SpecsProd_Flock]  DEFAULT ((0)) FOR [Flock]
GO

ALTER TABLE [dbo].[SpecsProd]  WITH NOCHECK ADD  CONSTRAINT [FK_SpecsProd_Products] FOREIGN KEY([productID])
REFERENCES [dbo].[Products] ([Id_product])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[SpecsProd] CHECK CONSTRAINT [FK_SpecsProd_Products]
GO

The clustered index that uses 99% I/O is [PK_SpecsProd] (the first one). Other indexes are also there.

    ALTER TABLE [dbo].[SpecsProd] ADD  CONSTRAINT [PK_SpecsProd] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ProdID_SpecID] ON [dbo].[SpecsProd]
(
    [productID] ASC,
    [specID] ASC
)
INCLUDE (   [ID],
    [SpecValue_1],
    [SpecValue_2]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SpecID_ProductID] ON [dbo].[SpecsProd]
(
    [specID] ASC,
    [productID] ASC
)
INCLUDE (   [SpecValue_1],
    [SpecValue_2]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [product] ON [dbo].[SpecsProd]
(
    [productID] ASC
)
INCLUDE (   [specID],
    [SpecValue_1],
    [SpecValue_2]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [specId_inc_prodID_specvalue1] ON [dbo].[SpecsProd]
(
    [specID] ASC
)
INCLUDE (   [productID],
    [SpecValue_1]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [specsID] ON [dbo].[SpecsProd]
(
    [specsID_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

One of the guiness-record contenders for the table with the most columns (specs table)

CREATE TABLE [dbo].[Specs](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Id_spec] [int] NOT NULL,
    [CatId] [int] NOT NULL,
    [sect] [varchar](50) NULL,
    [spec] [varchar](75) NOT NULL,
    [format] [varchar](50) NULL,
    [unit] [varchar](20) NULL,
    [definition] [ntext] NULL,
    [ordre] [int] NOT NULL,
    [Id_langue] [int] NOT NULL,
    [FormField] [varchar](50) NULL,
    [List] [varchar](max) NULL,
    [filterField] [bit] NOT NULL,
    [isFilter] [bit] NOT NULL,
    [isCollectionFilter] [bit] NOT NULL,
    [quickViewSubcats] [varchar](250) NULL,
    [width] [bit] NOT NULL,
    [height] [bit] NOT NULL,
    [depth] [bit] NOT NULL,
    [weight] [bit] NOT NULL,
    [DateCreation] [datetime] NOT NULL,
    [DateModification] [datetime] NOT NULL,
    [quickView] [bit] NOT NULL,
    [Visible] [bit] NULL,
    [compare] [bit] NOT NULL,
    [priceTag] [bit] NOT NULL,
    [ConvertionRate] [varchar](20) NULL,
    [ConvertionUnit] [varchar](20) NULL,
    [searchableLabel] [bit] NULL,
    [searchableValue] [bit] NULL,
 CONSTRAINT [PK_Specs] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_filter]  DEFAULT ((0)) FOR [filterField]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_isFilter]  DEFAULT ((0)) FOR [isFilter]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_isCollectionFilter]  DEFAULT ((0)) FOR [isCollectionFilter]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_width]  DEFAULT ((0)) FOR [width]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_height]  DEFAULT ((0)) FOR [height]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_depth]  DEFAULT ((0)) FOR [depth]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_weight]  DEFAULT ((0)) FOR [weight]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_DateCreation]  DEFAULT (getdate()) FOR [DateCreation]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_DateModification]  DEFAULT (getdate()) FOR [DateModification]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_quickView]  DEFAULT ((0)) FOR [quickView]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_compare]  DEFAULT ((0)) FOR [compare]
GO

ALTER TABLE [dbo].[Specs] ADD  CONSTRAINT [DF_Specs_priceTag]  DEFAULT ((0)) FOR [priceTag]
GO

Answer 1

1. Wait stats suggest ASYNC_NETWORK_IO is the bottleneck.

In your actual plan about 87% of your elapsed time is spent sending the results to the client. You may need to change application code or reduce the amount of data that you're sending back.

<WaitStats>
  <Wait WaitType="CMEMTHREAD" WaitTimeMs="33" WaitCount="86" />
  <Wait WaitType="SESSION_WAIT_STATS_CHILDREN" WaitTimeMs="1029" WaitCount="55" />
  <Wait WaitType="LATCH_EX" WaitTimeMs="2796" WaitCount="308" />
  <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="3083" WaitCount="144" />
</WaitStats>
<QueryTimeStats ElapsedTime="3542" CpuTime="7247" />

2. MAXDOP 22 is rather high for a SELECT query.

Have you tested this query with different MAXDOP values to verify that 22 is indeed the best choice? If I had to guess I'd say you have a two socket server with 12 cores per socket. Perhaps MAXDOP is set to 22 at the instance level for some reason. I'm basing that on the thread information in the plan:

<ThreadStat Branches="4" UsedThreads="88">
  <ThreadReservation NodeId="0" ReservedThreads="24" />
  <ThreadReservation NodeId="1" ReservedThreads="24" />
  <ThreadReservation NodeId="2" ReservedThreads="16" />
  <ThreadReservation NodeId="3" ReservedThreads="24" />
</ThreadStat>

Either NUMA node 2 has fewer schedulers than the others, the server has 2 sockets of 12 schedulers each, the server has 4 sockets of 6 schedulers each, or manual soft-NUMA was set up. A common suggestion for MAXDOP is to use something less than the physical number of cores per hard NUMA node.

With all of that said, you don't need to understand all of the technical details in order to do testing. Try testing with different MAXDOPs (be sure to run the tests more than once) and see if that helps.

3. You can reduce IO for all three tables in the plan if you want to.

Please note that I just looked at the query plan and didn't look at the index definitions that you already have. SpecsProd looks to be your biggest table. You can define a covering index on just the columns that you need. You can't seek on it, but you'll do less IO because the nonclustered index that only includes the three columns used in the query will be smaller than the clustered index.

The Products table has the next highest IO cost. You're already ordering by the clustered index which is good, but the size of the clustered index is the size of all of the data in the table. You could create a nonclustered index on just the clustered key column to less IO. You could even create it in DESC order to make the scan eligible for parallelism, but I don't know if that will make a difference in practice due to the TOP expression.

You already have a covering index on Specs, but the filters are evaluated in a predicate instead of a seek predicate. If you change the index or create a new one with key columns in the correct order you should be able to do a seek instead of a scan.

Answer 2

Just looking at the code, I'd recommend the following two things

1st: Create a temp table which will holds the following sub-query

WHERE sp.productID IN (
            SELECT TOP 25000 id_product
            FROM products
            ORDER BY 1 DESC
            )

i.e. I'd do

create table #t (id_product int primary key);
insert into #t (id_product)
SELECT TOP 25000 id_product
FROM products
ORDER BY 1 DESC;

and then change the original WHERE clause to

WHERE sp.productID IN (
                SELECT id_product
                FROM #t )

2nd: I'd remove lower() function in the where clause, if your database collation is not case-sensitive (CS), you do not need lower() function. If your database is CS, then you can re-write the condition.

For example,

lower(sp.SpecValue_1) = ('y')

can be re-written as

sp.SpecValue_1 in ('y', 'Y')

You can start from this approach and see whether this will improve your performance. Do not forget to update your statistics with fullscan as well.

If it still does not meet your requirements, then consider other approaches like what @Joe Obbish mentioned.