SQL SERVER指定查询优化参数

默认情况下，查询语句在第一次执行时根据查询参数进行优化并将执行计划缓存到计划高速缓存里以便后续查询可以复用。

但对于某些数据不均匀分布的情况，第一次执行时所使用的参数会导致：

1.执行计划对当前参数相对是最优的

2.执行计划对其他大部分参数是较差的

USE [Demo1]
GO

/****** Object:  Table [dbo].[T1]    Script Date: 10/27/2012 09:57:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
--========================================================================
--创建表
CREATE TABLE [dbo].[T1](
    [RowId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [ObjectID] [int] NOT NULL,
    [ObjectName] [sysname] NOT NULL,
    [RowStatus] [int] NOT NULL,
) ON [PRIMARY]
GO
--========================================================================
--创建索引
CREATE NONCLUSTERED INDEX [NI_T1_RowStatus] ON [dbo].[T1] 
(
    [RowStatus] ASC
)
GO
--========================================================================
--创建存储过程
CREATE PROCEDURE dbo.DemoTest
    -- Add the parameters for the stored procedure here
    @RowStatus int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * from dbo.T1
    WHERE T1.RowStatus=@RowStatus
    --OPTION(OPTIMIZE FOR(@RowStatus=1))
    
END
GO
--========================================================================
--插入2w+[RowStatus]=1的数据和一条[RowStatus]=2的数据
--使得[RowStatus]数据严重分布不均匀
INSERT INTO [dbo].[T1]
           ([ObjectID]
           ,[ObjectName]
           ,[RowStatus])
     VALUES
           (1,'o1',2)
GO
INSERT INTO [dbo].[T1]
           ([ObjectID]
           ,[ObjectName]
           ,[RowStatus])
SELECT A.object_Id AS ObjectID,
A.Name AS ObjectName ,
1 AS RowStatus 
FROM sys.columns A CROSS JOIN sys.columns  B
--========================================================================
GO
EXEC dbo.DemoTest @RowStatus=2
GO
EXEC dbo.DemoTest @RowStatus=1
GO
--由于[RowStatus]=1的数据在所有数据的99.99%，
--因此EXEC dbo.DemoTest @RowStatus=1查询时做全表扫描会是最优的选择，
--而对EXEC dbo.DemoTest @RowStatus=2查询时做索引查找会是最优的选择
--如果只有少数几次执行@RowStatus=2和多次执行@RowStatus=1的话，
--而我们不能保证每次数据库重启后第一次执行EXEC dbo.DemoTest @RowStatus=1来缓冲该计划，
--因此我们需要使用OPTION(OPTIMIZE FOR(@RowStatus=1))来为查询优化器指明参数

--减少读锁对并发造成的影响
--使用NOLOCK 查询
SELECT * FROM dbo.T1 WITH(NOLOCK)
--修改隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.T1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;