Lesson Learned #341: Index Disabled vs Performance issues
Published Mar 29 2023 12:37 PM 2,577 Views

Today, we got a very interesting service request where our customer reported an issue about performance, where they mentioned that they have an execution plan where in staging environment the filter is using Index Seek and production environment is using Index Scan and they need to know why SQL Server is not using the Index Seek operator when the structure is the same.

 

Following I would like to share with you what was our lesson learned, here. To reproduce the issue, we developed the following script:

 

 

CREATE TABLE TestDemo1 
( ID INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR(50))

CREATE INDEX TestDemo1_ix1 ON TestDemo1 (Name)

INSERT INTO TestDemo1 (Name) VALUES(REPLICATE('x',50))
INSERT INTO TestDemo1 (Name) VALUES(REPLICATE('a',50))
INSERT INTO TestDemo1 (Name) VALUES(REPLICATE('b',50))
INSERT INTO TestDemo1 (Name) VALUES(REPLICATE('c',50))
INSERT INTO TestDemo1 (Name) VALUES(REPLICATE('d',50))

INSERT INTO TestDemo1 (Name) select Name from TestDemo1;
GO 1000

 

 

Our customer run the following query: SELECT * FROM TestDemo1 Where Name = REPLICATE('x',50) and we obtained the execution plan with Index Seek in staging environment.

 

Jose_Manuel_Jurado_0-1680117545140.png

 

However, running in production environment with same configuration, the execution plan is Index Scan

 

Jose_Manuel_Jurado_1-1680117669634.png

 

To check that both environment are the same, besides updates statistics, we executed, in both scenarios, the TSQL command sp_help 'TestDemo1' and we saw that they have the same definition.

 

Jose_Manuel_Jurado_2-1680117760061.png

 

Even, checking the execution plan we saw that Index Scan reported a statistics usage:

 

Jose_Manuel_Jurado_3-1680117859427.png

 

In this situation, we didn't see any reason about it, but, during our troubleshooting, running the query select * from sys.indexes where is_disabled=1 we saw that the index in Production was disabled for bulk insert reason and was not re-enable again. 

 

Once we have re-enabled this index, the query started using the Index Seek operator like we have in both environments. 

 

Enjoy!

 

Version history
Last update:
‎Mar 29 2023 12:44 PM
Updated by: