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
Best Answer
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.
MAXDOP 22
is rather high for aSELECT
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. PerhapsMAXDOP
is set to 22 at the instance level for some reason. I'm basing that on the thread information in the plan: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.
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 inDESC
order to make the scan eligible for parallelism, but I don't know if that will make a difference in practice due to theTOP
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.