唯一非聚集索引变量传入时索引失效解决方案

场景一:
确认1.碰到了一个一个非常慢的SQL server语句，发现是变量查询时很慢
SQL语句:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'


SELECT  [Mac2]
FROM    SF_Cp_Detail 
WHERE   [Mac2] = @SN


确认2.查看索引是:唯一非聚集索引
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
    [Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
确认3.执行计划如下:
执行计划
  


SET STATISTICS  IO ON ; 
(0 行受影响)
Table 'SF_Cp_Detail'. Scan count 33, logical reads 1267942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.








场景二:
为什么用不到索引IX_SF_CP_Detail_MAC2]呢?
尝试1:
把唯一非聚集索引改为非聚集索引, Done,用到索引了.
Drop index …….
CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
         [Mac2] 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)
GO


 
尝试2:
可是还需要验证唯一性怎么办呢? 
试了半天未达到目标,  根据别人提示, 恢复唯一非聚集
CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
    [Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO




然后
修改查询:
DECLARE @SN VARCHAR(12)
SET @SN = '30F335CD0045'


SELECT  [Mac2]
FROM    SF_Cp_Detail 
WHERE   [Mac2] = @SN option (recompile)


预计查询计划
 
看样子不行, 但是,看一下实际查询计划:
 
OK ,Done,可以了.
尝试3.
但是我不可能去每个程序加option (recompile)呀,


而且随着数据量的增大,每次重新编译索引,  本身就导致SQL语句变慢.




最终解决方案:
唯一非聚集索引留着, 再添加一个 非聚集索引,保留两个索引, 终于搞定了.


CREATE UNIQUE NONCLUSTERED INDEX [IX_SF_CP_Detail_MAC2] ON [dbo].[SF_Cp_Detail]
(
    [Mac2] ASC
)
WHERE ([MAC2]<>'' AND [MAC2] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


CREATE NONCLUSTERED INDEX [IX_SF_Cp_Detail_Mac2] ON [dbo].[SF_Cp_Detail]
(
         [Mac2] 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)
GO