6

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
  • Sure, first off group concat is something somebody in The Heap recommended me : github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr it's just like doing a subquery CSV concatenation using XML PATH but it looks better. Performs nicely in some cases. – A_V Jun 20 '18 at 14:42
  • From the looks of things, the product index is almost certainly redundant; I believe any query that could use it would be able to use IX_ProdID_SpecID as effectively. The same may be true of specId_inc_prodID_specvalue1. From an index perspective, IX_SpecID_ProductID would be as effective, but if you frequently have queries that use the three columns in the first query and not SpecValue_2, and if SpecValue_2 tends to be large, then the smaller index may be more efficient. Finally, you don't need to include the ID column - the clustered index key is included automatically. – RDFozz Jun 20 '18 at 22:25
4
  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" />
  1. 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.

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

  • About point 1: This plan was run on my dev machine, it queries a database on a distant server so it is indeed quite likely to have network-bound issues. I can run it on the server again and see if that helps. About point 2: I have never heard of NUMA nodes before now, gonna need to read on it. Do you suggest having no more than MAXDOP 6 then ? About point 3: I did end up creating a more tailored index on SpecsProd. Let me post the updated plan. – A_V Jun 21 '18 at 14:30
3

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.

  • Thanks a lot for the insights. I did end up removing the lower() function and using just the exact value as I found the scalar expression was taking some time to process. This query is in fact a subquery part of a large stored procedure and in this procedure it's using an indexed memory-optimized temporary table for that subquery, as you suggested. I should have mentionned that the products table was just a "placeholder", my bad. – A_V Jun 21 '18 at 14:19

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.