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.
However, running in production environment with same configuration, the execution plan is Index Scan.
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.
Even, checking the execution plan we saw that Index Scan reported a statistics usage:
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!
