开发笔记 第82页

来源： MS SQL巡检系列——检查重复索引 – 潇湘隐者 – 博客园

前言感想：一时兴起，突然想写一个关于MS SQL的巡检系列方面的文章，因为我觉得这方面的知识分享是有价值，也是非常有意义的。一方面，很多经验不足的人，对于巡检有点茫然，不知道要从哪些方面巡检，另外一方面，网上关于MS SQL巡检方面的资料好像也不是特别多。写这个系列只是一个分享，自己的初衷是一个知识梳理、总结提炼过程，有些知识和脚本也不是原创，文章很多地方也是融入了自己的一些想法和见解的，不足和肤浅之处肯定也非常多，抛砖引玉，也希望大家提意见和建议、补充，指正其中的不足之处。Stay Hungry Stay Foolish！

在SQL Server数据库中，有可能存在重复的索引（Duplicate Indexes），这个不仅影响性能（INSERT、UPDATE、DELETE时带来额外的IO开销，当数据库维护，索引重组时也会带来额外的开销），而且占用空间。数据库存在重复索引（Duplicate Indexes）的原因是多方面的，很多时候、很多事情不是你所能完全掌控的，除非你所管理的数据库非常规范，权限控制、脚本发布非常严格、流程化。暂且不说这些，那么怎么在数据库巡检过程找出这些重复的索引（Duplicate Indexes）呢？ 下面分享一个我在Premier Proactive Services中发现一个的脚本（做了一些修改和调整）。

我们以AdventureWorks2014数据库为例，如下所示，表[Person].[Address]下有4个索引，如下所示

假设某个二愣子在这个表的字段StateProvinceID上创建了下面重复索引，IX_Address_N1 与IX_Address_StateProvinceID是一个重复索引。

CREATE INDEX IX_Address_N1 ON [Person].[Address](StateProvinceID);

那么我们执行下面脚本就能找到这个重复的索引，如下所示

;WITH    IndexColumns

          AS ( SELECT DISTINCT

                      SCHEMA_NAME(o.schema_id)     AS SchemaName    ,

                      OBJECT_NAME(o.object_id)     AS TableName     ,

                      i.name                       AS IndexName     ,

                      o.object_id                  AS [Object_ID]   ,

                      i.index_id                   AS Index_ID      ,

                      i.type_desc                 AS IndexType      ,

                      ( SELECT    CASE key_ordinal

                                    WHEN 0 THEN NULL

                                    ELSE '[' + COL_NAME(k.object_id,

                                                        column_id) + '] '

                                         + CASE WHEN is_descending_key = 1

                                                THEN 'Desc'

                                                ELSE 'Asc'

                                           END

                                  END AS [data()]

                        FROM      sys.index_columns  k WITH(NOLOCK)

                        WHERE     k.object_id = i.object_id

                                  AND k.index_id = i.index_id

                        ORDER BY  key_ordinal ,

                                  column_id

                      FOR

                        XML PATH('')

                      ) AS IndexColumns ,

                        CASE WHEN i.index_id = 1

                             THEN ( SELECT  '[' + name + ']' AS [data()]

                                    FROM    sys.columns (NOLOCK) AS c

                                    WHERE   c.object_id = i.object_id

                                            AND c.column_id NOT IN (

                                            SELECT  column_id

                                            FROM    sys.index_columns (NOLOCK)

                                                    AS kk

                                            WHERE   kk.object_id = i.object_id

                                                    AND kk.index_id = i.index_id )

                                    ORDER BY column_id

                                  FOR

                                    XML PATH('')

                                  )

                             ELSE ( SELECT  '[' + COL_NAME(k.object_id,

                                                           column_id) + ']' AS [data()]

                                    FROM    sys.index_columns k WITH(NOLOCK)

                                    WHERE   k.object_id = i.object_id

                                            AND k.index_id = i.index_id

                                            AND is_included_column = 1

                                            AND k.column_id NOT IN (

                                            SELECT  column_id

                                            FROM    sys.index_columns kk

                                            WHERE   k.object_id = kk.object_id

                                                    AND kk.index_id = 1 )

                                    ORDER BY key_ordinal ,

                                            column_id

                                  FOR

                                    XML PATH('')

                                  )

                        END AS IndexInclude

               FROM     sys.indexes  i WITH(NOLOCK)

                        INNER JOIN sys.objects o WITH(NOLOCK) ON i.object_id = o.object_id

                        INNER JOIN sys.index_columns ic  WITH(NOLOCK ) ON ic.object_id = i.object_id

                                                              AND ic.index_id = i.index_id

                        INNER JOIN sys.columns c WITH(NOLOCK) ON c.object_id = ic.object_id

                                                              AND c.column_id = ic.column_id

               WHERE    o.type = 'U'

                        AND i.index_id <> 0  -- 0 = 堆

                        AND i.type <> 3         -- 3 = XML

                        AND i.type <> 5         -- 5 = 聚集列存储索引(SQL 2014~ SQL 2016)

                        AND i.type <> 6         -- 6 = 非聚集列存储索引(SQL 2014~ SQL 2016)

                        AND i.type <> 7         -- 7 = 非聚集哈希索引(SQL 2014~ SQL 2016)

               GROUP BY o.schema_id ,

                        o.object_id ,

                        i.object_id ,

                        i.name ,

                        i.index_id ,

                        i.type_desc

             ),

        DuplicatesTable

          AS ( SELECT   ic1.SchemaName    ,

                        ic1.TableName     ,

                        ic1.IndexName     ,

                        ic1.[Object_ID]   ,

                        ic2.IndexName AS DuplicateIndexName ,

                        ic1.IndexType   ,

                        CASE WHEN ic1.index_id = 1

                             THEN ic1.IndexColumns + ' (Clustered)'

                             WHEN ic1.IndexInclude = '' THEN ic1.IndexColumns

                             WHEN ic1.IndexInclude IS NULL THEN ic1.IndexColumns

                             ELSE ic1.IndexColumns + ' INCLUDE ' + ic1.IndexInclude

                        END AS IndexCols ,

                        ic1.index_id

               FROM     IndexColumns ic1

                        JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id

                                                 AND ic1.index_id < ic2.index_id

                                                 AND ic1.IndexColumns = ic2.IndexColumns

                                                 AND ( ISNULL(ic1.IndexInclude, '') = ISNULL(ic2.IndexInclude,

                                                              '')

                                                       OR ic1.index_id = 1

                                                     )

             )

    SELECT  SchemaName ,

            TableName ,

            IndexName ,

            DuplicateIndexName ,

            IndexType,

            IndexCols ,

            Index_ID ,

          Object_ID ,

          0 AS IsXML

    FROM    DuplicatesTable dt

    ORDER BY 1 , 2 ,3

注意，关于重复索引（Duplicate Indexes）表示存在的索引除了名字不一样外， 索引所在字段以及索引字段顺序都是一样的。An index is considered to be a duplicate if it references the same column and ordinal position as another index in the same database。 这个脚本是找出一模一样的索引，如果你创建下面索引，索引字段一样，但是有包含列字段不一样，那么这个脚本会将这个索引视为不一样的索引。有兴趣可以自己试试。

CREATE INDEX IX_Address_N2 ON [Person].[Address](StateProvinceID) INCLUDE (City);

另外关于XML索引的重复索引，可以使用下面脚本检查。

--Use the below T-SQL script to generate the complete list of duplicate XML indexes in a given database:

;WITH    XMLTable

          AS ( SELECT   OBJECT_NAME(x.object_id) AS TableName ,

                        SCHEMA_NAME(o.schema_id) AS SchemaName ,

                        x.object_id ,

                        x.name ,

                        x.index_id ,

                        x.using_xml_index_id ,

                        x.secondary_type ,

                        CONVERT(NVARCHAR(MAX), x.secondary_type_desc) AS secondary_type_desc ,

                        ic.column_id

               FROM     sys.xml_indexes x ( NOLOCK )

                        JOIN sys.objects o ( NOLOCK ) ON x.object_id = o.object_id

                        JOIN sys.index_columns (NOLOCK) ic ON x.object_id = ic.object_id

                                                              AND x.index_id = ic.index_id

             ),

        DuplicatesXMLTable

          AS ( SELECT   x1.SchemaName ,

                        x1.TableName ,

                        x1.name AS IndexName ,

                        x2.name AS DuplicateIndexName ,

                        x1.secondary_type_desc AS IndexType ,

                        x1.index_id ,

                        x1.object_id ,

                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName, x1.TableName, x1.name, x2.name ) AS seq1 ,

                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName DESC, x1.TableName DESC, x1.name DESC, x2.name DESC ) AS seq2 ,

                        NULL AS inc

               FROM     XMLTable x1

                        JOIN XMLTable x2 ON x1.object_id = x2.object_id

                                            AND x1.index_id < x2.index_id

                                            AND x1.using_xml_index_id = x2.using_xml_index_id

                                            AND x1.secondary_type = x2.secondary_type

             )

    SELECT  SchemaName ,

            TableName ,

            IndexName ,

            DuplicateIndexName ,

            IndexType  ,

            Index_ID ,

            [Object_ID] ,

            1 AS IsXML

    FROM    DuplicatesXMLTable dtxml

    ORDER BY 1 ,

             2 ,

             3;

在每个库跑一次这个脚本，就能将所有的重复的索引（Duplicate Indexes）全部找出，但是当手头服务器、数据库特别多时，这个工作也是一个体力活，可以将这个常规工作自动化，避免重复劳动，我将这个集成在MyDBA工具里面，只需要点击一下鼠标，就可以帮助我自动处理这些工作。

来源： MS SQL巡检系列——检查外键字段是否缺少索引 – 潇湘隐者 – 博客园

前言感想：一时兴起，突然想写一个关于MS SQL的巡检系列方面的文章，因为我觉得这方面的知识分享是有价值，也是非常有意义的。一方面，很多经验不足的人，对于巡检有点茫然，不知道要从哪些方面巡检，另外一方面，网上关于MS SQL巡检方面的资料好像也不是特别多。写这个系列只是一个分享，自己的初衷是一个知识梳理、总结提炼过程，有些知识和脚本也不是原创，文章很多地方融入了自己的一些想法和见解的，不足和肤浅之处肯定也非常多，抛砖引玉，也希望大家提意见和建议、补充，指正其中的不足之处。Stay Hungry Stay Foolish！

MS SQL巡检系列——检查重复索引

MS SQL巡检系列——检查外键字段是否缺少索引

MS SQL巡检系列——检查数据库上一次DBCC CHECKDB的时间

对于绝大部分情况，外键字段都有必要建立对应的索引（注意，外键约束并不会自动建立索引），关于外键字段为什么要建立索引？下面从几个简单的例子来分析一下。我们先准备测试环境数据。

CREATE TABLE PRIMARY_TB

(

    PRODUCT_CD        VARCHAR(12)      ,

    PRODUCT_DATE    DATE             ,

    PRODUCT_DESC    VARCHAR(120)     ,

    CONSTRAINT PK_PRIMARY_TB  PRIMARY KEY CLUSTERED (PRODUCT_CD)

);

SET NOCOUNT ON;

GO

DECLARE @Index INT=1;

BEGIN TRAN

WHILE @Index <= 3000

BEGIN

    INSERT INTO dbo.PRIMARY_TB

    SELECT 'Prd' + CAST(@Index AS VARCHAR(4)), GETDATE() - CEILING(RAND()*200), 'production description' + CAST(@Index AS VARCHAR(4));

    SET @Index +=1;

END

COMMIT;

CREATE TABLE FK_TB

(

    FK_ID            INT IDENTITY(1,1),

    SALES_REGION    VARCHAR(32),

    SALES_CITY        VARCHAR(32),

    PRODUCT_CD        VARCHAR(12),

    SALIES_SUM        INT,

    CONSTRAINT PK_FK_TB PRIMARY KEY CLUSTERED (FK_ID)

)

GO

ALTER TABLE [dbo].[FK_TB]  WITH CHECK ADD  CONSTRAINT [FK_PRIMARY_TB_PRODUCT_CD] FOREIGN KEY([PRODUCT_CD])

REFERENCES [dbo].[PRIMARY_TB] ([PRODUCT_CD]) ON  DELETE CASCADE;

GO

SET NOCOUNT ON;

GO

DECLARE @Index INT=1;

BEGIN TRAN

WHILE @Index <=1000000

BEGIN

    INSERT INTO FK_TB

    SELECT 'REGION'+CAST(CEILING(RAND()*20) AS VARCHAR(2)), CAST(CEILING(RAND()*300) AS VARCHAR(3)),'Prd'+ CAST(CEILING(RAND()*3000) AS VARCHAR(8)),CEILING(RAND()*100000);

    SET @Index +=1;

END

COMMIT;

UPDATE STATISTICS dbo.PRIMARY_TB WITH FULLSCAN;

UPDATE STATISTICS dbo.FK_TB WITH FULLSCAN;

GO

1： 外键字段建立索引，在主表与子表JOIN操作时能提高性能，减少IO操作。

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  p.PRODUCT_CD ,

        p.PRODUCT_DATE ,

        f.SALES_REGION ,

        f.SALES_CITY ,

        f.SALIES_SUM

FROM    dbo.PRIMARY_TB p

        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD

WHERE p.PRODUCT_CD ='Prd131';

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

如下截图所示，如果外键字段缺少索引，这两个表关联查询时，子表就会走扫描（此处测试是聚集索引扫描），如果子表非常大（例如此处案例所示），IO开销就比较大。

我们对外键约束字段PRODUCT_CD建立下面非聚集索引IDX_FK_TB，然后对比两者的执行计划和IO开销

CREATE INDEX IDX_FK_TB ON dbo.FK_TB(PRODUCT_CD);

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  p.PRODUCT_CD ,

        p.PRODUCT_DATE ,

        f.SALES_REGION ,

        f.SALES_CITY ,

        f.SALIES_SUM

FROM    dbo.PRIMARY_TB p

        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD

WHERE p.PRODUCT_CD ='Prd131'

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

你会发现执行计划从原来的聚集索引扫描（Clustered Index Scan）变成了索引查找（Index Seek），IO的减少也是非常明显的。因为这里仅仅是测试数据，复杂的生产环境，性能的提升有可能比这更加明显。

2：如果外键约束为CASCADE（on update/delete）时，则当修改被引用行（referenced row）时，所有引用行（referencing rows ）也必须修改（更新或级联删除）。外键列上的索引能减小锁的粒度和范围，从而提高效率和性能。如下所示：

我们先看看缺少索引的情况。

DROP INDEX IDX_FK_TB ON dbo.FK_TB;

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

DELETE FROM dbo.PRIMARY_TB WHERE PRODUCT_CD IN ('Prd132','Prd133')

GO

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

CREATE INDEX IDX_FK_TB ON dbo.FK_TB(PRODUCT_CD);

GO

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

DELETE FROM dbo.PRIMARY_TB WHERE PRODUCT_CD IN ('Prd134','Prd135')

GO

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

3：如果外键关系为NO ACTION（on update/delete）时，那么被引用的行不能被删除，因为这个操作会导致引用行变成“孤立”。删除之前，数据库会为了有效地查找、定位行，外键列上创建索引也非常有帮助。

上面肤浅的构造了简单案例，并对比外键约束字段拥有索引和缺少索引时，SQL的执行计划和IO的差异，那么接下来，我们进入正题，巡检的时候，我们必须通过脚本找到数据库里面外键字段没有索引的相关表和信息，并生成对应的创建索引的脚本。如下所示。

/*

One or more tables found, with foreign key constraint defined but no supporting indexes created on the foreign key columns.

SQL Server doesnt put an index on foreign key columns by default and indexing foreign key fields in referencing tables is not required.

Foreign key columns usage must evaluated to determine whether or not indexing this column will help up increase the current

workloads performance by enhancing join performance, reducing table locking (for full table scans) while cascading updates and

deletes, etc.

*/

;

WITH    FKTable

          AS ( SELECT   SCHEMA_NAME(po.schema_id) AS 'parent_schema_name' ,

                        OBJECT_NAME(fkc.parent_object_id) AS 'parent_table_name' ,

                        OBJECT_NAME(constraint_object_id) AS 'constraint_name' ,

                        SCHEMA_NAME(ro.schema_id) AS 'referenced_schema' ,

                        OBJECT_NAME(referenced_object_id) AS 'referenced_table_name' ,

                        ( SELECT    '[' + COL_NAME(k.parent_object_id,

                                                   parent_column_id) + ']' AS [data()]

                          FROM      sys.foreign_key_columns (NOLOCK) AS k

                                    INNER JOIN sys.foreign_keys  (NOLOCK) ON k.constraint_object_id = object_id

                                                              AND k.constraint_object_id = fkc.constraint_object_id

                          ORDER BY  constraint_column_id

                        FOR

                          XML PATH('')

                        ) AS 'parent_colums' ,

                        ( SELECT    '[' + COL_NAME(k.referenced_object_id,

                                                   referenced_column_id) + ']' AS [data()]

                          FROM      sys.foreign_key_columns (NOLOCK) AS k

                                    INNER JOIN sys.foreign_keys  (NOLOCK) ON k.constraint_object_id = object_id

                                                              AND k.constraint_object_id = fkc.constraint_object_id

                          ORDER BY  constraint_column_id

                        FOR

                          XML PATH('')

                        ) AS 'referenced_columns'

               FROM     sys.foreign_key_columns fkc ( NOLOCK )

                        INNER JOIN sys.objects po ( NOLOCK ) ON fkc.parent_object_id = po.object_id

                        INNER JOIN sys.objects ro ( NOLOCK ) ON fkc.referenced_object_id = ro.object_id

               WHERE    po.type = 'U'

                        AND ro.type = 'U'

               GROUP BY po.schema_id ,

                        ro.schema_id ,

                        fkc.parent_object_id ,

                        constraint_object_id ,

                        referenced_object_id

             ),

        /* Index Columns */

        IndexColumnsTable

          AS ( SELECT   SCHEMA_NAME(o.schema_id) AS 'schema_name' ,

                        OBJECT_NAME(o.object_id) AS TableName ,

                        ( SELECT    CASE key_ordinal

                                      WHEN 0 THEN NULL

                                      ELSE '[' + COL_NAME(k.object_id,

                                                          column_id) + ']'

                                    END AS [data()]

                          FROM      sys.index_columns (NOLOCK) AS k

                          WHERE     k.object_id = i.object_id

                                    AND k.index_id = i.index_id

                          ORDER BY  key_ordinal ,

                                    column_id

                        FOR

                          XML PATH('')

                        ) AS cols

               FROM     sys.indexes (NOLOCK) AS i

                        INNER JOIN sys.objects o ( NOLOCK ) ON i.object_id = o.object_id

                        INNER JOIN sys.index_columns ic ( NOLOCK ) ON ic.object_id = i.object_id

                                                              AND ic.index_id = i.index_id

                        INNER JOIN sys.columns c ( NOLOCK ) ON c.object_id = ic.object_id

                                                              AND c.column_id = ic.column_id

               WHERE    o.type = 'U'

                        AND i.index_id > 0

               GROUP BY o.schema_id ,

                        o.object_id ,

                        i.object_id ,

                        i.name ,

                        i.index_id ,

                        i.type

             ),

        FKWithoutIndexTable

          AS ( SELECT   fk.parent_schema_name AS SchemaName ,

                        fk.parent_table_name AS TableName ,

                        fk.referenced_schema AS ReferencedSchemaName ,

                        fk.referenced_table_name AS ReferencedTableName ,

                        fk.constraint_name AS ConstraintName ,

                        fk.referenced_columns AS Referenced_Columns ,

                        fk.parent_colums AS Parent_Columns

               FROM     FKTable fk

               WHERE    NOT EXISTS ( SELECT 1

                                     FROM   IndexColumnsTable ict

                                     WHERE  fk.parent_schema_name = ict.schema_name

                                            AND fk.parent_table_name = ict.TableName

                                            AND fk.parent_colums = LEFT(ict.cols,

                                                              LEN(fk.parent_colums)) )

             )

    SELECT  @@SERVERNAME AS InstanceName ,

            DB_NAME() AS DatabaseName ,

            SchemaName ,

            TableName ,

            Parent_Columns ,

            ReferencedSchemaName ,

            ReferencedTableName ,

            Referenced_Columns ,

            ConstraintName

    INTO    #ForeignKeyWithOutIndex

    FROM    FKWithoutIndexTable

    ORDER BY DatabaseName ,

            SchemaName ,

            TableName;

--输出临时表数据

SELECT  *

FROM    #ForeignKeyWithOutIndex;

--生成外键字段缺少的索引，请抽查、检验，确认后批量执行

SELECT  'CREATE INDEX IX_' + LTRIM(RTRIM(TableName)) + '_'

        + SUBSTRING(Parent_Columns, 2, LEN(Parent_Columns) - 2) + '  ON '

        + LTRIM(RTRIM(SchemaName)) + '.' + LTRIM(RTRIM(TableName)) + '('

        + Parent_Columns + ');'

FROM    #ForeignKeyWithOutIndex;

--删除临时表

DROP TABLE #ForeignKeyWithOutIndex;

在创建这些索引前最好检查、确认一下，外键字段创建索引能提高性能，但是肯定也要特殊的场景和上下文不适合，所以最好根据实际情况决定。索引创建之后，通过监控工具监控一下数据库性能、等待事件的变化。

参考资料：

http://stackoverflow.com/questions/3650690/should-every-sql-server-foreign-key-have-a-matching-index

http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx

http://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

来源： SQL Server中INNER JOIN与子查询IN的性能测试 – 潇湘隐者 – 博客园

这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣？”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣（IN是子查询的实现方式之一，本篇还是只对比INNER JOIN与子查询IN的性能，如果展开INNER JOIN与子查询性能对比，范围太大了，没法一一详述）。下面这篇文章，我们就INNER JOIN与子查询IN这两种写法孰优孰劣，在不同场景下进行一下测试对比一下，希望能解答你心中的疑惑。

下面例子以AdventureWorks2014为测试场景，测试表为Sales.SalesOrderHeader与Sales.SalesOrderDetail。 如下所示：

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  h.* FROM

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail)

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

如下所示，两种写法的SQL的实际执行计划是几乎一致。而且对比IO开销也是一致。cpu time 与elapsed time 有所差别，这个是因为两者返回的数据有所差别的缘故（SQL 1 返回 31465行数据， SQL 2返回 121317行数据），两者在逻辑上实际上是不一致的。因为重复数据的缘故。撇开这个不谈，光从性能上来考察两种，它们几乎是一模一样。没有优劣之分。

如果有人对上面的重复数据不明白的话，下面做个简单的例子演示给大家看看。如下所示，截图中INNER JOIN就会有重复数据。

CREATE TABLE P

(

    PID    INT ,

    Pname  VARCHAR(24)

)

INSERT INTO dbo.P

SELECT 1, 'P1' UNION ALL

SELECT 2, 'P2' UNION ALL

SELECT 3, 'P3'

CREATE TABLE dbo.C

(

    CID       INT ,

    PID       INT ,

    Cname  VARCHAR(24)

)

INSERT INTO dbo.c

SELECT 1, 1, 'C1' UNION ALL

SELECT 2, 1, 'C2' UNION ALL

SELECT 3, 2, 'C3' UNION ALL

SELECT 3, 3, 'C4'

其实下面SQL在逻辑上才是相等的，它们的实际执行计划与IO是一样的。没有优劣之分。

SELECT  h.* FROM

Sales.SalesOrderHeader h

WHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail);

SELECT DISTINCT h.* FROM Sales.SalesOrderHeader h

INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID;

那么我们再来看另外一个例子，测试一下两者的性能差别。如下所示

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  C.*

FROM    Sales.Customer C

        INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID;

SELECT  C.*

FROM    Sales.Customer C

WHERE  C.PersonID IN ( SELECT Person.Person.BusinessEntityID

                                     FROM   Person.Person );

INNER JOIN与子查询IN的实际执行计划对比的百分比为66% VS 34% ， 子查询IN的性能还比 INNER JOIN的性能要好一些. IO几乎无差别，cpu time 与elapsed time的对比情况来看，子查询IN的性能确实要好一些。

这个是因为子查询IN在这个上下文环境中，它使用右半连接（Right Semi Join）方式的Hash Match，即一个表中返回的行与另一个表中数据行进行不完全联接查询（查找到匹配的数据行就返回，不再继续查找）。那么可以肯定的是，在这个场景（上下文）中，子查询IN这种方式的SQL的性能比INNER JOIN 这种写法的SQL要好。

那么我们再来看一个INNER JOIN性能比子查询（IN)要好的案例。如下所示，我们先构造测试数据。

CREATE TABLE P

(

    P_ID    INT IDENTITY(1,1),

    OTHERCOL        CHAR(500),

    CONSTRAINT PK_P PRIMARY KEY(P_ID)

)

GO

BEGIN TRAN

DECLARE @I INT = 1

WHILE @I<=10000

BEGIN

    INSERT INTO P VALUES (NEWID())

    SET @I = @I+1

    IF (@I%500)=0

    BEGIN

        IF @@TRANCOUNT>0

        BEGIN

            COMMIT

            BEGIN TRAN

        END

    END

END

IF @@TRANCOUNT>0

BEGIN

    COMMIT

END

GO

CREATE TABLE C

(

    C_ID  INT IDENTITY(1,1) ,

    P_ID   INT  FOREIGN KEY REFERENCES P(P_ID),

    COLN  CHAR(500),

    CONSTRAINT PK_C  PRIMARY KEY (C_ID)

)

SET NOCOUNT ON;

DECLARE @I INT = 1

WHILE @I<=1000000

BEGIN

    INSERT INTO C VALUES ( CAST(RAND()*10 AS INT)+1,  NEWID())

    SET @I = @I+1

END

GO

构造完测试数据后，我们对比下两者的性能差异

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT C.* FROM dbo.C C

INNER JOIN dbo.P  P ON C.P_ID = P.P_ID

WHERE P.P_ID=8

SELECT * FROM dbo.C

WHERE P_ID IN (SELECT P_ID FROM dbo.P WHERE P_ID=8)

增加对应的索引后，这个性能差距更更明显。 如下截图所示

USE [AdventureWorks2014]

GO

CREATE NONCLUSTERED INDEX [IX_C_N1]

ON [dbo].[C] ([P_ID])

INCLUDE ([C_ID],[COLN])

GO

在生产环境遇到一个案例， 两个视图使用INNER JOIN 与 IN 两种写法，在性能上差距很大。 使用子查询IN的性能比使用INNER JOIN的性能要好很多。如下截图所示。因为视图里面涉及多表。这样肯定导致执行计划非常复杂，导致SQL用INNER JOIN 的写法在性能上没有用子查询IN的写法要快

其实一部分情况下，INNER JOIN 与 子查询IN都是等价的。因为SQL Server优化器已经足够聪明，能够进行一些内部转换，生成等价的计划。但是在某一些特殊场景下，各有优劣。不能武断的就说INNER JOIN在性能上要比子查询IN要好。一定要结合上下文环境具体来谈性能优劣。否则没有多大意义。另外，子查询可以分为相关子查询和无关子查询，对于无关子查询来说，Not In子句比较常见，但Not In潜在会带来两种问题，结果不正确和性能问题，具体可以参考在SQL Server中为什么不建议使用Not In子查询

来源： MS SQL 事务日志管理小结 – 潇湘隐者 – 博客园

本文是对SQL Server事务日志的总结，文章有一些内容和知识来源于官方文档或一些技术博客，本文对引用部分的出处都有标注。

事务日志介绍

在SQL Server中，事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。每个SQL Server数据库都拥有自己的事务日志，用于记录所有事务以及每个事务对数据库所做的修改。那么数据库的哪些操作会记录在事务日志中呢？具体一点的说，这些操作包括：

• 每个事务的开始和结束。

• 每次数据修改（插入、更新或删除）。 这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。

• 每次分配或释放区和页。

• 创建或删除表或索引。

另外，像SELECT这样的操作是不会记录在事务日志当中的。如果你想对事务日志记录信息有一个直观的认识，那么你可以在测试环境做一些SELECT、INSERT、UPDATE、DDL等操作，然后使用ApexSQL Log这款工具查看具体的事务日志记录信息。

USE YourSQLDba;

GO

CREATE TABLE dbo.TEST(ID  INT);

GO

INSERT INTO dbo.TEST SELECT 100;

GO

SELECT * FROM dbo.TEST;

GO

UPDATE dbo.TEST SET ID=101;

GO

DELETE FROM dbo.TEST WHERE ID=101;

GO

如上所示，像DDL、DML操作都会记录在事务日志当中，但是SELECT是不会记录在事务日志当中（当然SELECT INTO除外，其实SELECT INTO在事务日志里面转化为了CREATE TABLE形式）。另外，需要注意: 事务日志并不是审计跟踪。也就是说事务日志并不能完全替代审计跟踪。它不提供对数据库做出改变的审计跟踪；它不保持对数据库已执行命令的记录，只有数据如何改变的结果。其实很多对事务日志了解不深入的人都以为事务日志可以代替审计跟踪（曾经有项目经理想让我从事务日志当中挖掘出谁误删了数据，其实事务日志只会记录那个账号删除了记录，并不会记录客户端信息，所以不能定位到谁删除了数据）。如下所示，我们多了一个DROP TABLE操作。你会看到跟上面不一样的结果。

USE YourSQLDba;

GO

CREATE TABLE dbo.TEST(ID  INT);

GO

INSERT INTO dbo.TEST SELECT 100;

GO

SELECT * FROM dbo.TEST;

GO

UPDATE dbo.TEST SET ID=101;

GO

DELETE FROM dbo.TEST WHERE ID=101;

GO

DROP TABLE dbo.Test;

GO

这篇博客transactionlog中有一张图，描述了一个更新操作的流程中，事务日志在这个流程中的位置以及作用。想必看过这张图后，大家在大脑中会对事务日志的功能作用有一个初步的形象认识。

其实这张图还包含了很多隐藏的重要信息，下面我们一一来述说一下：

预写式日志(Write-Ahead Logging）

什么是预写式日志呢？ 其实其核心思想就是在变化的数据写入到数据库之前，将相关日志记录信息先写入到日志. SQL Server的预写式日志（Write-Ahead Logging）机制保证修改的描述（例如日志记录）会在数据本身修改写入数据文件前写入，会写入磁盘上的事务日志文件。它是SQL Server保证事务持久性（Durability）的基本机制。一个日志记录会包含已提交事务或未提交事务的详细信息，在数据被事务修改的不同情况下，可能已经写入数据文件或还没来得及写入数据文件，这取决于检查点是否已发生。

浅谈SQL Server中的事务日志(二)—-事务日志在修改数据时的角色 这篇博客有深入浅出的介绍（如下所示）：

Write-Ahead Logging的核心思想是:在数据写入到数据库之前，先写入到日志.

因为对于数据的每笔修改都记录在日志中，所以将对于数据的修改实时写入到磁盘并没有太大意义，即使当SQL Server发生意外崩溃时，在恢复(recovery)过程中那些不该写入已经写入到磁盘的数据会被回滚(RollBack),而那些应该写入磁盘却没有写入的数据会被重做(Redo)。从而保证了持久性(Durability)。

     但WAL不仅仅是保证了原子性和持久性。还会提高性能.

     硬盘是通过旋转来读取数据,通过WAL技术，每次提交的修改数据的事务并不会马上反映到数据库中，而是先记录到日志.在随后的CheckPoint和Lazy Writer中一并提交,如果没有WAL技术则需要每次提交数据时写入数据库……

官方文档SQL Server 事务日志体系结构和管理指南介绍如下（个人对翻译做了一下调整，也增加了一点点内容）：

要了解预写日志的工作方式，了解如何将修改的数据写入磁盘很重要。SQL Server维护一个缓冲区缓存(buffer cache)，在必须检索数据时从其中读取数据页。 在缓冲区缓存中修改页后，不会将其立即写回磁盘；而是将其标记为“脏”数据。在将数据页物理写入磁盘之前，这些脏数据可以多次被修改。 对于每次逻辑写入，都会在日志缓存(log cache)中插入一条事务日志记录记录这些修改。在将关联的脏页从缓冲区缓存中删除并写入磁盘之前，必须将这条些日志记录写入磁盘。检查点进程定期在缓冲区高速缓存中扫描包含来自指定数据库的页的缓冲区，然后将所有脏页写入磁盘。 CHECKPOINT 可创建一个检查点，在该点保证全部脏页都已写入磁盘，从而在以后的恢复过程中节省时间。

将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页。 SQL Server具有一个逻辑，它可以在写入关联的日志记录前防止刷新脏页。 日志记录将在提交事务时写入磁盘。

检查点作用

检查点将脏数据页从当前数据库的缓冲区高速缓存刷新到磁盘上。这最大限度地减少了数据库完整恢复时必须处理的活动日志,减少的崩溃恢复需要的时间。其实CheckPoint是为了优化IO和减少Recovery时间 在完整恢复时，需执行下列操作：

• 前滚系统停止之前尚未刷新到磁盘上的日志记录修改信息。
• 回滚与未完成的事务（如没有 COMMIT 或 ROLLBACK 日志记录的事务）相关联的所有修改。

检查点操作

检查点在数据库中执行下列过程：

• 将记录写入日志文件，标记检查点的开始。

• 将为检查点记录的信息存储在检查点日志记录链内。

• 记录在检查点中的一条信息是第一条日志记录的日志序列号 (LSN)，它必须存在才能成功进行数据库范围内的回滚。 该 LSN 称为“最小恢复 LSN”（“MinLSN”）。 MinLSN 是下列各项中的最小者：

o   检查点开始的 LSN。

o   最早的活动事务起点的 LSN。

o   尚未传递给分发数据库的最早的复制事务起点的 LSN。

o   检查点记录还包含所有已修改数据库的活动事务的列表。

• 如果数据库使用简单恢复模式，检查点则标记在 MinLSN 前重用的空间。
• 将所有脏日志和脏数据页写入磁盘。
• 将标记检查点结束的记录写入日志文件。
• 将这条链起点的 LSN 写入数据库引导页。

导致检查点的活动

下列情形下将出现检查点：

• 显式执行 CHECKPOINT 语句。 用于连接的当前数据库中出现检查点。
• 在数据库中执行了最小日志记录操作，例如，在使用大容量日志恢复模式的数据库中执行大容量复制操作。
• 已经使用 ALTER DATABASE 添加或删除了数据库文件。
• 通过 SHUTDOWN 语句或通过停止 SQL Server (MSSQLServer) 服务停止了 SQL Server 实例。 任一操作都会在 SQL Server 实例的每个数据库中生成一个检查点。
• SQL Server 实例在每个数据库内定期生成自动检查点，以减少实例恢复数据库所需的时间。
• 进行了数据库备份。
• 执行了需要关闭数据库的活动。 例如，AUTO_CLOSE 设置为 ON ，并且关闭了数据库的最后一个用户连接，或者执行了需要重新启动数据库的数据库选项更改。

事务日志物理结构

SQL Server数据库中的事务日志可以有一个或多个事务日志文件。当存在多个事务日志文件时，这些日志文件也只能顺序调用，并不能并行使用，因此使用多个日志文件并不会带来性能上的提升（后面内容会展开讨论这个）。其实，如果你对ORACLE当中联机重做日志体系结构非常熟悉的话，多个事务日志文件就相当于多个redo log file，不同的是，ORACLE下面的redo log可以实现多路复用（日志组可以有一个或多个同样的日志成员redo log file，多个日志成员的原因是防止日志文件组内某个日志文件损坏后及时提供备份，所以同一组的日志成员一般内容信息相同，但是存放位置不同）。一般会将同一组的不同日志成员文件放到不同的磁盘或不同的裸设备上。以提高安全性。SQL Server似乎没有这个架构设计。另外，ORACLE的REDO 与UNDO在结构设计上是分开的。而SQL Server可以通过事务日志进行REDO和UNDO操作。

事务日志逻辑结构

从逻辑结构上看，SQL Server对于日志文件的管理，是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。SQL Server事务日志按逻辑运行，就好像事务日志是一串日志记录一样。每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处，并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建时的串行序列存储。 每条日志记录都包含其所属事务的 ID。对于每个事务，与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。 虚拟日志文件没有固定大小，且物理日志文件所包含的虚拟日志文件数不固定。 数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎尝试维护少量的虚拟文件。 在扩展日志文件后，虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员不能配置或设置虚拟日志文件的大小或数量。但是如果设置日志文件的增量过小，则会产生过多的VLFS,也就是日志文件碎片，过多的日志文件碎片会拖累SQL Server性能.因此，指定合适的日志文件初始大小和增长，是减少日志碎片最关键的部分.

事务日志是一种回绕的文件。 例如，假设有一个数据库，它包含一个分成四个虚拟日志文件的物理日志文件。 当创建数据库时，逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。 日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。

当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端。

上面关于事务日志的虚拟日志循环覆盖使用是否有点眼熟的感觉，这个跟ORACLE下REDO LOG的循环覆盖使用的理念是一模一样的。只不过是不同的概念和不同的实现方式。

事务日志功能

事务日志有啥功能呢？关于事务日志的功能，详细具体内容可以参考官方文档事务日志 (SQL Server)，里面已经详细介绍了事务日志的几个功能，在此不做展开。

事务日志支持以下操作：

• 恢复个别的事务。

• 在SQL Server启动时恢复所有未完成的事务。

• 将还原的数据库、文件、文件组或页前滚至故障点。

• 支持事务复制。

• 支持高可用性和灾难恢复解决方案： AlwaysOn 可用性组、数据库镜像和日志传送。

事务日志截断

什么是事务日志截断呢？ 在介绍事务日志截断前，我们必须先了解一下MinLSN、活动日志（Actvie Log）等概念。

最小恢复LSN（Minimum Recovery LSN（MinLSN）)概念

  MinLSN是在还未结束的事务记录在日志中最小的LSN号,MinLSN是下列三者之一的最小值:

• CheckPoint的开始LSN

• 还未结束的事务在日志的最小LSN

• 尚未传递给分发数据库的最早的复制事务起点的 LSN.

从MinLSN到日志的逻辑结尾处，则称为活动日志(Active Log)。日志文件中从 MinLSN 到最后写入的日志记录这一部分称为日志的活动部分，或者称为活动日志（Active log）。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。所有的日志记录都必须从 MinLSN 之前的日志部分截断。也就是说永远不能截断活动日志的任何部分。

下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。

LSN 148 是事务日志中的最后一条记录。 在处理 LSN 147 处记录的检查点时，Tran 1 已经提交，而 Tran 2 是唯一的活动事务。 这就使 Tran 2 的第一条日志记录成为执行最后一个检查点时处于活动状态的事务的最旧日志记录。 这使 LSN 142（Tran 2 的开始事务记录）成为 MinLSN。

活动日志必须包括所有未提交事务的每一部分。如果应用程序开始执行一个事务但未提交或回滚，将会阻止数据库引擎推进 MinLSN。 这可能会导致两种问题：

    如果系统在事务执行了许多未提交的修改后关闭，以后重新启动时，恢复阶段所用的时间将比“恢复间隔”选项指定的时间长得多。

    因为不能截断 MinLSN 之后的日志部分，日志可能变得很大。 即使数据库使用的是简单恢复模式，这种情况也有可能出现，在简单恢复模式下，每次执行自动检查点操作时通常都会截断事务日志。

日志截断其实指从SQL Server数据库的逻辑事务日志中删除不活动的虚拟日志文件，释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。 但是，在截断日志前，必须执行检查点操作。检查点将当前内存中已修改的页（称为“脏页”）和事务日志信息从内存写入磁盘。 执行检查点时，事务日志的不活动部分将标记为可重用。 此后，日志截断可以释放不活动的部分。有关检查点的详细信息，请参阅数据库检查点 (SQL Server)。

关于日志截断，必须定期截断事务日志，防止其占满分配给物理日志文件的磁盘空间。日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小，则必须收缩日志文件。

日志截断会在下面事件后自动进行截断：

    简单恢复模式下，在检查点之后发生。

    在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。

   CHECKPOINT only truncates the transaction log (marks the VLF for reuse) only in simple recovery model. In Full recovery, you have to take log backup.

实际上，日志截断会由于多种原因发生延迟。 查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列，了解哪些因素（如果存在）阻止日志截断。 下表对这些列的值进行了说明：

事务日志收缩

有时候我们监控告警会发现事务日志出现暴增的情况，那么此时就必须对是事务日志进行收缩，不管数据库处于那种恢复模式，简单、完整模式。都可以按下面流程进行收缩。

1：查看对应数据库事务日志的逻辑名称（name)，后续操作需要用到。

SELECT  database_id ,        name ,        type_desc ,        physical_nameFROM    sys.master_filesWHERE   database_id = DB_ID(‘YourSQLDba’)    AND type_desc=’LOG’

2: 使用DBCC SQLPERF查看事务日志空间使用情况统计信息：

      DBCC SQLPERF (LOGSPACE)

     如果对应数据库的Log Space Used(%)的值较小，那么就可以收缩事务日志。

  3：执行类似下面的收缩事务日志文件语句。

USE YourSQLDba;

GO

DBCC SHRINKFILE(‘YourSQLDba_Log’, 128);

  如果Log Space Used(%)很小，而收缩效果又不佳，那么一般是因为日志截断延迟造成，一般可以通过下面脚本检查原因，大部分情况是因为等待LOG_BACKUP缘故。所以你对事务日志做一次备份后，再进行收缩即可解决。

SELECT  name ,        log_reuse_wait  ,        log_reuse_wait_descFROM    sys.databasesWHERE   database_id = DB_ID(‘YourSQLDba’);  backup log [YourSQLDba] to disk = ‘M:\DB_BACKUP\LOG_BACKUP\YourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN’ with noInit, checksum, name = ‘YourSQLDba:15h40: M:\DB_BACKUP\LOG_BACKUP\YourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN’

增加事务日志文件

SQL Server数据库中的事务日志可以有一个或多个事务日志文件，但是即使有多个事务日志文件，也不能并行写入多个事务日志文件，数据库引擎还是会串行使用多个事务日志文件。也就是说大多数场景，多个事务日志文件其实并没有什意义，那么它存在的意义是什么呢？例如，当你当前磁盘告警，事务日志无法继续增长，你需要在其他磁盘新增一个事务日志文件，让数据库继续顺畅运行。个人觉得多个事务日志文件确实是一个很鸡肋的东西。Paul S. Randal在“了解SQL Server的日志记录和恢复”中明确指出：不要创建多个的日志文件，因为它不会导致性能增益。

下面是如何增加一个事务日志文件的样例：

USE [master]GOALTER DATABASE [YourSQLDba] ADD LOG FILE ( NAME = N’YourSQLDba_Log2′, FILENAME = N’D:\SQL_LOG\YourSQLDba_Log1.LDF’ , SIZE = 65536KB , MAXSIZE = 55296KB , FILEGROWTH = 10%)GO

删除事务日志文件

既然可以增加事务日志文件，那么当然也可以删除事务日志文件，但是这个删除操作是有限制的。主日志文件（primary log）是不能删除的。如果你删除primary log就会报“不能从数据库中删除主数据文件或主日志文件。”，下面我们来测试一下。

准备测试环境如下：

USE master;GOCREATE DATABASE [TEST] CONTAINMENT = NONE ON  PRIMARY ( NAME = N’TEST’, FILENAME = N’D:\SQL_DATA\TEST.mdf’ , SIZE = 100MB , MAXSIZE = 40GB, FILEGROWTH = 64MB ) LOG ON ( NAME = N’TEST_log’ , FILENAME = N’D:\SQL_LOG\TEST_LOG_1.ldf’ , SIZE = 20MB , MAXSIZE = 40MB , FILEGROWTH = 10MB),( NAME = N’TEST_log2′, FILENAME = N’D:\SQL_LOG\TEST_LOG_2.ldf’ , SIZE = 20MB , MAXSIZE = 20GB , FILEGROWTH = 10MB)GO BACKUP DATABASE [TEST] TO  DISK = N’D:\DB_BACKUP\Test.bak’         WITH NOFORMAT, NOINIT,          NAME = N’TEST-Full Database Backup’,        SKIP, NOREWIND, NOUNLOAD,  STATS = 10;GO  USE TEST;GOSELECT * INTO mytest FROM sys.objects;GOINSERT INTO mytestSELECT * FROM mytestGO 12 DBCC SQLPERF(LOGSPACE) DBCC LOGINFO(‘TEST’)

注意，此时DBCC LOGINFO显示FileId=3的日志文件对应的虚拟日志(VLF)的Status为2，此时删除事务日志文件会提示文件无法删除，因为Status=2意味着VLF不能被覆盖和重用。

Status = 2 means that VLF can’t be reused (overwritten) at this time and it doesn’t necessarily mean that VLF is still active and writing transactions to that VLF. As Jonathan already mentioned, it means that the VLF is waiting for backup/REPL/Mirroring etc..

USE master;

GO

ALTER DATABASE TEST REMOVE FILE TEST_log2

备份事务日志后，你会发现FileId=3的日志文件对应的虚拟日志(VLF)的Status变为了0，那么此时就可以移除事务日志文件了。

BACKUP LOG TEST TO DISK = ‘D:\SQL_LOG\Test.Trn’

GO

DBCC LOGINFO(‘TEST’)

GO

USE master;

GO

ALTER DATABASE TEST REMOVE FILE TEST_log2

如果是生产环境或者在上述备份事务日志后，对应日志文件的VLF的状态仍然为2，那么可以用收缩日志文件和备份事务日志循环处理，直至对应日志文件下所有的VLF状态全部为0，就可以删除事务日志文件。

USE TEST;

GO

DBCC SHRINKFILE(TEST_log2);

BACKUP LOG TEST TO DISK = ‘D:\SQL_LOG\Test.Trn’

注意，主日志文件（primary log）是不能删除的，如下测试所示：

USE master;

GO

ALTER DATABASE TEST REMOVE FILE TEST_log

Msg 5020, Level 16, State 1, Line 35

The primary data or log file cannot be removed from a database.

但是当你需要规划存储路径、移动事务日志文件时，你可以使用折中的方法将主事务日志文件（primary log）移动到其它目录。如下所示：

1： 将当前数据库脱机;

ALTER DATABASE TEST SET OFFLINE;

2： 修改数据库的事务日志位置

ALTER DATABASE TEST

MODIFY FILE

(

NAME = N’TEST_log’

, FILENAME = N’E:\SQL_LOG\TEST_LOG_1.ldf’

)

3： 手工将事务日志文件移动到上面位置

4：将数据库联机操作。

ALTER DATABASE TEST SET ONLINE;

另外，如何判断那个日志文件是主事务日志文件？目前来说，我只能这样判断， sys.master_files当中，file_id最小值对应的日志文件为主事务日志文件。用脚本判断如下：

SELECT  f.database_id            AS database_id  ,        DB_NAME(f.database_id)   AS database_name,        MIN(f.file_id)           AS primary_log_id ,        f.type_desc              AS type_desc    FROM    sys.master_files  fWHERE  f.database_id= DB_ID(‘databasename’) AND  type = 1GROUP BY f.database_id,f.type_desc;

另外，你也可以用下面脚本查出哪些数据库拥有两个或以上事务日志。

SELECT  f.database_id    AS database_id  ,        d.name           AS database_name,        f.type_desc      AS type_desc    ,        COUNT(*)         AS log_countFROM    sys.master_files  fINNER  JOIN sys.databases d ON f.database_id = d.database_idWHERE   type = 1GROUP BY f.database_id ,         f.type_desc,         d.nameHAVING  COUNT(*) >= 2;

参考资料：

https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide#physical_arch

https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/the-transaction-log-sql-server#FactorsThatDelayTruncation

https://docs.microsoft.com/zh-cn/sql/relational-databases/logs/database-checkpoints-sql-server

https://technet.microsoft.com/zh-cn/library/2009.02.logging.aspx

http://www.cnblogs.com/CareySon/archive/2012/02/13/2349751.html

http://www.cnblogs.com/CareySon/p/3315041.html

http://www.cnblogs.com/CareySon/archive/2012/02/17/2355200.html

来源： SQL Server OPTION (OPTIMIZE FOR UNKNOWN) 测试总结 – 潇湘隐者 – 博客园

关于SQL Server的查询提示OPTION (OPTIMIZE FOR UNKNOWN) ，它是解决参数嗅探的方法之一。 而且对应的SQL语句会缓存，不用每次都重编译。关键在于它的执行计划的准确度问题， 最近在优化的时候，和同事对于这个查询提示（Query Hint）有一点分歧，遂动手实验验证、总结了一些东西。

关于提示OPTION (OPTIMIZE FOR UNKNOWN)，它会利用统计数据和标准算法生成一个折中、稳定的执行计划，但是它是无法利用直方图（histogram）信息来生成执行计划。官方文档的介绍如下：

OPTIMIZE FOR 编译和优化查询时提示查询优化器对本地变量使用特定值。仅在查询优化期间使用该值，在查询执行期间不使用该值。

UNKNOWN

指定查询优化器在查询优化期间使用统计数据而不是初始值来确定局部变量的值。OPTIMIZE FOR 可以抵消优化器的默认参数检测行为，也可在创建计划指南时使用

OPTIMIZE FOR UNKNOWN

指示查询优化器在查询已经过编译和优化时为所有局部变量使用统计数据而不是初始值，包括使用强制参数化创建的参数。有关强制参数化的详细信息，请参阅强制参数化。

如果在同一查询提示中使用 OPTIMIZE FOR @variable\_name = literal_constant 和 OPTIMIZE FOR UNKNOWN，则查询优化器将对特定的值使用指定的 literal_constant，而对其余变量使用 UNKNOWN。这些值仅用于查询优化期间，而不会用于查询执行期间。

OPTIMIZE FOR UNKNOWN是否会用直方图数据呢？ 不会，OPTIMIZE FOR UNKNOWN只会用简单的统计数据。我们以how-optimize-for-unknown-works这篇博客中的例子来演示一下， 下面测试环境为SQL Server 2014，数据库为AdventureWorks2014

CREATE PROCEDURE test (@pid int)ASSELECT * FROM [Sales].[SalesOrderDetail]WHERE ProductID = @pid OPTION (OPTIMIZE FOR UNKNOWN);

为了消除统计信息不准确会干扰测试结果，我们手工更新一下统计信息。

UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH FULLSCAN;

我们在SSMS里面点击“包含实际执行计划”选项，然后测试执行该存储过程，如下截图所示： 执行计划居然走聚集索引扫描

EXEC test @pid=709

Filter里面过滤的记录为456.079，而实际上ProductID=709的记录有188条，那么优化器是怎么估计判断记录数为456.709的呢？

其实优化器是这样来估计的：它使用ProductID列的密度(Density)* Rows来计算的

SELECT 0.003759399 *121317 ~= 456.079008483 ~= 456.079

而ProductID列的密度(Density)的计算是这样来的:

ProductID的值有266个，可以用下面SQL获取ProductID的值个数

SELECT COUNT(DISTINCT ProductID) FROM  Sales.SalesOrderDetail

SELECT 1.0/266  ~=  0.003759

然后你可以使用任意不同的参数测试，例如707、712……, 你会发现使用查询提示OPTION (OPTIMIZE FOR UNKNOWN)后，优化器会总是使用相同的执行计划。也就是说这个查询提示生成的执行计划是一个“折中的执行计划” ，对于数据分布倾斜的比较厉害（数据分布极度不均衡）的情况下，是极度不建议使用查询提示OPTION (OPTIMIZE FOR UNKNOWN)的。

本人曾经一度对使用OPTION（RECOMPILE）还是OPTION (OPTIMIZE FOR UNKNOWN)感到困惑和极度难以取舍，后面总结了一下：

1：执行不频繁的存储过程，使用OPTION（RECOMPILE）要优先与OPTION (OPTIMIZE FOR UNKNOWN)

2：执行频繁的存储过程，使用OPTION (OPTIMIZE FOR UNKNOWN)要优先于OPTION（RECOMPILE）

3：数据分布倾斜的厉害的情况下，优先使用OPTION（RECOMPILE）

    4: 使用OPTION (OPTIMIZE FOR UNKNOWN)会生成一个稳定、统一的执行计划，如果这个执行计划的效率基本能满足用户需求，那么优先使用OPTION (OPTIMIZE FOR UNKNOWN)

参考资料：

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms181714(v=sql.100)

http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/

https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/

来源： SQL Server中LIKE %search_string% 走索引查找（Index Seek）浅析 – 潇湘隐者 – 博客园

在SQL Server的SQL优化过程中，如果遇到WHERE条件中包含LIKE ‘%search_string%’是一件非常头痛的事情。这种情况下，一般要修改业务逻辑或改写SQL才能解决SQL执行计划走索引扫描或全表扫描的问题。最近在优化SQL语句的时候，遇到了一个很有意思的问题。某些使用LIKE ‘%’ + @search_string + ‘%'(或者 LIKE @search_string)这样写法的SQL语句的执行计划居然走索引查找（Index Seek）。下面这篇文章来分析一下这个奇怪的现象。

首先，我们来看看WHERE查询条件中使用LIKE的几种情况,这些是我们对LIKE的一些常规认识：

1： LIKE ‘condition%’

执行计划会走索引查找（Index Seek or Clustered Index Seek）。

2:  LIKE ‘%condition’

执行计划会走索引扫描（Index Scan or Clustered Index Scan）或全表扫描（Table Scan）

3:  LIKE ‘%condition%’

执行计划会走索引扫描（Index Scan or Clustered Index Scan）或全表扫描（Table Scan）

4： LIKE ‘condition1%condition%’;

执行计划会走索引查找（Index Seek）

下面我们以AdventureWorks2014示例数据库为测试环境（测试环境为SQL Server 2014 SP2），测试上面四种情况，如下所示：

其实复杂的情况下，LIKE ‘search_string%’也有走索引扫描（Index Scan）的情况，上面情况并不是唯一、绝对的。如下所示

在表Person.Person的 rowguid字段上创建有唯一索引AK_Person_rowguid

那么我们来看看上面所说的这个特殊案例（这里使用一个现成的案例，懒得构造案例了），如何让LIKE %search_string%走索引查找（Index Seek），这个技巧就是使用变量，如下SQL对比所示：

如下所示，表[dbo].[GEN_CUSTOMER]在字段CUSTOMER_CD有聚集索引。

可以看到CUSTOMER_CD LIKE ‘%’ + @CUSTOMER_CD + ‘%’这样的SQL写法（或者CUSTOMER_CD LIKE @CUSTOMER_CD也可以）， 执行计划就走聚集索引查找（Clustered Index Seek）了， 而条件中直接使用CUSTOMER_CD LIKE ‘%00630%’ 反而走聚集索引扫描（Clustered Index Scan），另外可以看到实际执行的Cost开销比为4% VS 96% ，初一看，还真的以为第一个执行计划比第二个执行的代价要小很多。但是从IO开销，以及CPU time、elapsed time对比来看，两者几乎没有什么差异。在这个案例中，并不是走索引查找（Index Seek）就真的开销代价小很多。

考虑到这里数据量较小，我使用网上的一个脚本，在AdventureWorks2014数据库构造了一个10000000的大表，然后顺便做了一些测试对比

CREATE TABLE dbo.TestLIKESearches(     ID1         INT    ,ID2         INT    ,AString     VARCHAR(100)    ,Value       INT    ,PRIMARY KEY (ID1, ID2)); WITH Tally (n) AS(SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))FROM sys.all_columns a CROSS JOIN sys.all_columns b)INSERT INTO dbo.TestLIKESearches    (ID1, ID2, AString, Value)SELECT 1+n/500, n%500    ,CASE WHEN n%500 > 299 THEN            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1) +            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1) +            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1) +            RIGHT(1000+n%1000, 3) +            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1) +            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1) +            SUBSTRING(‘abcdefghijklmnopqrstuvwxyz’, 1+ABS(CHECKSUM(NEWID()))%26, 1)          END    ,1+ABS(CHECKSUM(NEWID()))%100FROM Tally;  CREATE INDEX IX_TestLIKESearches_N1 ON dbo.TestLIKESearches(AString);

如下测试所示，在一个大表上面，LIKE @search_string这种SQL写法，IO开销确实要小一些，CPU Time也要小一些。个人多次测试都是这种结果。也就是说对于数据量较大的表，这种SQL写法性能确实要好一些。

现在回到最开始那个SQL语句，个人对执行计划有些疑惑，查看执行计划，你会看到优化器对CUSTOMER_CD LIKE ‘%’ + @CUSTOMER_CD + ‘%’ 进行了转换。如下截图或通过执行计划的XML，你会发现上面转换为使用三个内部函数LikeRangeStart, LikeRangeEnd,  LikeRangeInfo.

<OutputList>                    <ColumnReference Column=”Expr1007″ />                    <ColumnReference Column=”Expr1008″ />                    <ColumnReference Column=”Expr1009″ />                  </OutputList>                  <ComputeScalar>                    <DefinedValues>                      <DefinedValue>                        <ColumnReference Column=”Expr1007″ />                        <ScalarOperator ScalarString=”LikeRangeStart((N’%’+[@CUSTOMER_CD])+N’%’)”>                          <Identifier>                            <ColumnReference Column=”ConstExpr1004″>                              <ScalarOperator>                                <Intrinsic FunctionName=”LikeRangeStart”>                                  <ScalarOperator>                                    <Arithmetic Operation=”ADD”>                                      <ScalarOperator>                                        <Arithmetic Operation=”ADD”>                                          <ScalarOperator>                                            <Const ConstValue=”N’%'” />                                          </ScalarOperator>                                          <ScalarOperator>                                            <Identifier>                                              <ColumnReference Column=”@CUSTOMER_CD” />                                            </Identifier>                                          </ScalarOperator>                                        </Arithmetic>                                      </ScalarOperator>                                      <ScalarOperator>                                        <Const ConstValue=”N’%'” />                                      </ScalarOperator>                                    </Arithmetic>                                  </ScalarOperator>                                  <ScalarOperator>                                    <Const ConstValue=”” />                                  </ScalarOperator>                                </Intrinsic>                              </ScalarOperator>                            </ColumnReference>                          </Identifier>                        </ScalarOperator>                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Column=”Expr1008″ />                        <ScalarOperator ScalarString=”LikeRangeEnd((N’%’+[@CUSTOMER_CD])+N’%’)”>                          <Identifier>                            <ColumnReference Column=”ConstExpr1005″>                              <ScalarOperator>                                <Intrinsic FunctionName=”LikeRangeEnd”>                                  <ScalarOperator>                                    <Arithmetic Operation=”ADD”>                                      <ScalarOperator>                                        <Arithmetic Operation=”ADD”>                                          <ScalarOperator>                                            <Const ConstValue=”N’%'” />                                          </ScalarOperator>                                          <ScalarOperator>                                            <Identifier>                                              <ColumnReference Column=”@CUSTOMER_CD” />                                            </Identifier>                                          </ScalarOperator>                                        </Arithmetic>                                      </ScalarOperator>                                      <ScalarOperator>                                        <Const ConstValue=”N’%'” />                                      </ScalarOperator>                                    </Arithmetic>                                  </ScalarOperator>                                  <ScalarOperator>                                    <Const ConstValue=”” />                                  </ScalarOperator>                                </Intrinsic>                              </ScalarOperator>                            </ColumnReference>                          </Identifier>                        </ScalarOperator>                      </DefinedValue>                      <DefinedValue>                        <ColumnReference Column=”Expr1009″ />                        <ScalarOperator ScalarString=”LikeRangeInfo((N’%’+[@CUSTOMER_CD])+N’%’)”>                          <Identifier>                            <ColumnReference Column=”ConstExpr1006″>                              <ScalarOperator>                                <Intrinsic FunctionName=”LikeRangeInfo”>                                  <ScalarOperator>                                    <Arithmetic Operation=”ADD”>                                      <ScalarOperator>                                        <Arithmetic Operation=”ADD”>                                          <ScalarOperator>                                            <Const ConstValue=”N’%'” />                                          </ScalarOperator>                                          <ScalarOperator>                                            <Identifier>                                              <ColumnReference Column=”@CUSTOMER_CD” />                                            </Identifier>                                          </ScalarOperator>                                        </Arithmetic>                                      </ScalarOperator>                                      <ScalarOperator>                                        <Const ConstValue=”N’%'” />                                      </ScalarOperator>                                    </Arithmetic>                                  </ScalarOperator>                                  <ScalarOperator>                                    <Const ConstValue=”” />                                  </ScalarOperator>                                </Intrinsic>                              </ScalarOperator>                            </ColumnReference>                          </Identifier>                        </ScalarOperator>                      </DefinedValue>                    </DefinedValues>

另外，你会发现Nested Loops & Compute Scalar 等步骤的Cost都为0.后面在“Dynamic Seeks and Hidden Implicit Conversions”这篇博客里面看到了一个新名词“Dynamic Seeks”。文字提到因为成本估算为0，所以，你看到的执行计划的Cost又是“不准确”的，具体描述如下：

The plan now contains an extra Constant Scan,  a Compute Scalar and a Nested Loops Join.  These operators are interesting because they have zero cost estimates: no CPU, no I/O, nothing.  That’s because they are purely architectural: a workaround for the fact that SQL Server cannot currently perform a dynamic seek within the Index Seek operator itself.  To avoid affecting plan choices, this extra machinery is costed at zero.

The Constant Scan produces a single in-memory row with no columns.  The Compute Scalar defines expressions to describe the covering seek range (using the runtime value of the @Like variable).  Finally, the Nested Loops Join drives the seek using the computed range information as correlated values.

The upper tooltip shows that the Compute Scalar uses three internal functions, LikeRangeStart, LikeRangeEnd, and LikeRangeInfo.  The first two functions describe the range as an open interval.  The third function returns a set of flags encoded in an integer, that are used internally to define certain seek properties for the Storage Engine.  The lower tooltip shows the seek on the open interval described by the result of LikeRangeStart and LikeRangeEnd, and the application of the residual predicate ‘LIKE @Like’.

不管你返回的记录有多少，执行计划Nested Loops & Compute Scalar 等步骤的Cost都为0，如下测试所示，返回1000条记录，它的成本估算依然为0 ，显然这样是不够精确的。深层次的原因就不太清楚了。执行计划Cost不可靠的案例很多。

SET STATISTICS IO ON; SET STATISTICS TIME ON; DECLARE @CUSTOMER_CD NVARCHAR(10); SET @CUSTOMER_CD=N’%44%’   SELECT * FROM  [dbo].[GEN_CUSTOMER] WHERE CUSTOMER_CD LIKE @CUSTOMER_CD

另外，其实还一点没有搞清楚的时候在什么条件下出现Index Seek的情况。有些情况下，使用变量的方式，依然是索引扫描

不过我在测试过程，发现有一个原因是书签查找(Bookmark Lookup：键查找（Key Lookup）或RID查找 （RID Lookup）)开销过大会导致索引扫描。如下测试对比所示：

CREATE NONCLUSTERED INDEX [IX_xriteWhite_N1] ON.[dbo].[xriteWhite] ([Item_NO]) INCLUDE ([Iden],[WI_CE],[CIE],[Operate_Time])

参考资料：

http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx

https://blogs.msdn.microsoft.com/varund/2009/11/30/index-usage-by-like-operator-query-tuning/

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

https://stackoverflow.com/questions/1388059/sql-server-index-columns-used-in-like

来源： SQL Server通过条件搜索获取相关的存储过程等对象 – 潇湘隐者 – 博客园

--==================================================================================================================

--        ScriptName          :            find_prc_from_src_txt.sql

--        Author              :            潇湘隐者

--        CreateDate          :            2019-10-22

--        Description         :            在SQL Server实例中通过条件搜索所有数据库的存储过程、函数、视图，找出这些对象

--        Note                :

/*******************************************************************************************************************

        Parameters            :                                    参数说明

********************************************************************************************************************

            @src_text         :            你要搜索的条件，例如，想找出那些存储过程有调用某个链接服务器：@src_text=xxxx

********************************************************************************************************************

        Notice                ：            由于效率问题，有时候会被阻塞，在tempdb等待LCK_M_SCH_S

********************************************************************************************************************

   Modified Date    Modified User     Version                 Modified Reason

********************************************************************************************************************

    2019-10-22        潇湘隐者         V01.00.00        新建该脚本。

*******************************************************************************************************************/

--==================================================================================================================

DECLARE @cmdText        NVARCHAR(MAX);

DECLARE @database_name  NVARCHAR(64);

DECLARE @src_text        NVARCHAR(128);

SET @src_text='xxxx' --根据实际情况输入查询、搜索条件

IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL

    DROP TABLE dbo.#databases;

CREATE TABLE #databases

(

    database_id     INT,

    database_name   sysname

);

INSERT  INTO #databases

SELECT  database_id ,

        name

FROM    sys.databases

WHERE name NOT IN ('model') AND state = 0; --state_desc=ONLINE

IF OBJECT_ID('TempDB.dbo.#sql_modules') IS NOT NULL

    DROP TABLE #sql_modules;

/**********************************************************************************************************

此处如果用这种写法，就会报下面错误，所以用下面这种写法。

SELECT '' AS database_name,  t.* INTO #sql_modules

FROM sys.sql_modules t WITH(NOLOCK) WHERE 1=0;

------------------------------------------------------------------------———----------------------------

Msg 8152, Level 16, State 2, Line 2

将截断字符串或二进制数据。

**********************************************************************************************************/

SELECT 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' AS database_name

    ,  t.* INTO #sql_modules

FROM sys.sql_modules t WITH(NOLOCK) WHERE 1=0;

WHILE 1= 1

BEGIN

    SELECT TOP 1 @database_name= database_name

    FROM #databases

    ORDER BY database_id;

    IF @@ROWCOUNT =0

        BREAK;

    SET @cmdText =  N'USE ' + QUOTENAME(@database_name) + N';' +CHAR(10)

    //**********************************************************************************************************

    SELECT @cmdText += N'INSERT INTO ##sql_modules

    SELECT  *

    FROM    sys.sql_modules W

    WHERE   definition LIKE ''%@p_src_text%'';' + CHAR(10);

    EXEC SP_EXECUTESQL @cmdText, N'@p_src_text NVARCHAR(128)',@p_src_text=@src_text;

    此种方式不生效。这里弃用这种动态SQL执行方式

    ***********************************************************************************************************/

    SELECT @cmdText += N'INSERT INTO #sql_modules

                       SELECT @p_database_name

                             , t.*

                       FROM    sys.sql_modules t WITH(NOLOCK)

                       WHERE   definition LIKE ''%' +@src_text +'%'';' + CHAR(10);

    EXEC SP_EXECUTESQL @cmdText,N'@p_database_name NVARCHAR(64)',@p_database_name=@database_name;

    DELETE FROM #databases WHERE database_name=@database_name;

END

SELECT * FROM tempdb.dbo.#sql_modules;

IF OBJECT_ID('TempDB.dbo.#databases') IS NOT NULL

    DROP TABLE dbo.#databases;

IF OBJECT_ID('TempDB.dbo.#sql_modules') IS NOT NULL

    DROP TABLE #sql_modules;

来源： OPTION(RECOMPILE)提高带参数执行SQL语句的索引效率_奋斗鱼的博客-CSDN博客

本例为MSSQL2008环境下测试。

1. 无参数查询
先看一个简单的查询语句在大数据表中的查询情况：

SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
WHERE fmr.BasicTime>=’2015-12-02 00:00:00′
1
2
在查询分析器中执行后显示耗时5s，查询到的记录数为6223条

2. 带参数查询
现在引用参数再查询一次

DECLARE
@BasicTime DATETIME
SET @BasicTime= LEFT(CONVERT(VARCHAR(20),DATEADD(DAY,-2,GETDATE()),120),10)+’ 00:00:00′
–SELECT @BasicTime
SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
WHERE fmr.BasicTime>=@BasicTime
1
2
3
4
5
6
为保证查询条件一样，参数@BasicTime的值也是’2015-12-02 00:00:00’
在查询分析器中执行后显示耗时15s，查询到的记录数为6223条。比不带参数的查询时间多了10s。

3. 分析对比
将例一查询语句选中，点击工具栏中的“显示估计的执行计划”按钮，可以看到该语句的执行计划。

可以看到，执行时使用了索引查找和键查找这两种最有效率的查询方式，所以耗时短。
同理查看例2中的执行计划

查询1是赋值参数的过程，我们主要看查询2、
执行计划明显比例1的无参数查询的复杂，例1的“键查找”在例2中变成了“索引扫描“。
由此可判断此处是耗时的主要关键。

4. 语句优化
在语句末尾添加OPTION(RECOMPILE)，可以使执行计划重新编译，提高性能。

DECLARE
@BasicTime DATETIME
SET @BasicTime= LEFT(CONVERT(VARCHAR(20),DATEADD(DAY,-2,GETDATE()),120),10)+’ 00:00:00′
–SELECT @BasicTime
SELECT fmr.FID,fmr.BasicTime FROM feeMeterRead fmr
WHERE fmr.BasicTime>=@BasicTime
OPTION(RECOMPILE)
1
2
3
4
5
6
7
上面查询语句重新执行后，耗时为6s。优化成功。
我们现在来查看下执行计划，估计的执行计划还是没变，但是点击”包括实际的执行计划“，然后再点击”执行“查询，可以看到执行时的计划如下：

可以看到，实际执行时是重新编译了，执行计划已经与例1的无参数执行一致。
————————————————
版权声明：本文为CSDN博主「奋斗鱼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/quan278905570/article/details/50171345

来源： SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析 – 潇湘隐者 – 博客园

在SQL SERVER的查询语句中使用OR是否会导致不走索引查找（Index Seek）或索引失效（堆表走全表扫描 （Table Scan）、聚集索引表走聚集索引扫描（Clustered Index Scan））呢？是否所有情况都是如此？又该如何优化呢？ 下面我们通过一些简单的例子来分析理解这些现象。下面的实验环境为SQL SERVER 2008，如果在不同版本有所区别，欢迎指正。

堆表单索引

首先我们构建我们测试需要实验环境，具体情况如下所示：

DROP TABLE TEST

CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(32));

CREATE INDEX PK_TEST ON TEST(OBJECT_ID)

DECLARE @Index INT =0;

WHILE @Index < 500000

BEGIN

    INSERT INTO TEST

    SELECT @Index, 'kerry'+CAST(@Index AS VARCHAR(6));

    SET @Index = @Index +1;

END

UPDATE STATISTICS TEST WITH FULLSCAN

场景1：如下所示，并不是所有的OR条件都会导致SQL走全表扫描。具体情况具体分析，不要套用教条。

SELECT * FROM TEST WHERE (OBJECT_ID =5 OR OBJECT_ID = 105)

场景2：加了条件1=1后，执行计划从索引查找（Index Seek）变为全表扫描（Table Scan），为什么会如此呢？个人理解为优化器将OR运算拆分为两个子集处理，由于一些原因，1=1这个条件导致优化器认定需要全表扫描才能完成1=1条件子集的计算处理（为了理解这个，煞费苦心，鉴于理论薄弱，如有错误或不足，敬请指出）。所以优化器在权衡代价后生成的执行计划最终选择了全表扫描（Table Scan）

SELECT * FROM TEST WHERE (1=1 OR OBJECT_ID =105);

场景3: 下面场景比较好理解，因为下面需要从500000条记录中取出499700条记录，而全表扫描（Table Scan）肯定是最优的选择，代价（Cost）最低。

SELECT * FROM TEST WHERE (OBJECT_ID >300 OR OBJECT_ID =105);

场景4：这种场景跟场景2的情况本质是一样的。所以在此略过。其实类似这种写法也是实际情况中最常出现的情况，还在迷糊的同学，赶紧抛弃这种写法吧

DECLARE @OBJECT_ID INT =150;

SELECT * FROM TEST WHERE (@OBJECT_ID IS NULL OR OBJECT_ID =@OBJECT_ID);

聚集索引表单索引

在聚集索引表中，我们也依葫芦画瓢，准备实验测试的数据环境。

DROP TABLE TEST

CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(32));

CREATE CLUSTERED INDEX PK_TEST ON TEST(OBJECT_ID)

DECLARE @Index INT =0;

WHILE @Index < 500000

BEGIN

    INSERT INTO TEST

    SELECT @Index, 'kerry'+CAST(@Index AS VARCHAR(6));

    SET @Index = @Index +1;

END

UPDATE STATISTICS TEST WITH FULLSCAN

场景1 ：索引查找（Index Seek）

SELECT * FROM TEST WHERE (OBJECT_ID =5 OR OBJECT_ID = 105)

场景2：聚集索引扫描（Clustered Index Scan）

场景3：似乎与堆表有所不同。聚集索引表居然还是走聚集索引查找。

场景4：OR导致聚集索引扫描

如果堆表或聚集索引表上建立有联合索引,情况也大致如此，在此不做过多案例讲解。下面仅仅讲述一两个案例场景。

DROP TABLE test1;

CREATE TABLE test1

  (

     a INT,

     b INT,

     c INT,

     d INT,

     e INT

  )

DECLARE @Index INT =0;

WHILE @Index < 10000

  BEGIN

      INSERT INTO test1

      SELECT @Index,

             @Index,

             @Index,

             @Index,

             @Index

      SET @Index = @Index + 1;

  END

CREATE INDEX idx_test_n1

  ON test1(a, b, c, d)

UPDATE STATISTICS test1 WITH fullscan;

SELECT * FROM TEST1 WHERE A=12 OR B> 500 OR C >100000

因为结果集是几个条件的并集，最多只能在查找A=12的数据时用索引，其它几个条件都需要表扫描，那优化器就会选择直接走一遍表扫描，以最低的代价COST完成，所以索引就失效了。

那么如何优化查询语句含有的OR的SQL语句呢？方法无外乎有三种：

1：通过索引覆盖，使包含OR的SQL走索引查找（Index Seek）。但是这个只能满足部分场景，并不能解决所有这类SQL。这个Solution具有一定的局限性。

SELECT * FROM TEST1 WHERE A=12 OR B=500

如果我们通过索引覆盖，在字段B上面也建立索引，那么下面OR查询也会走索引查找。

CREATE INDEX IDX_TEST1_B ON TEST1(B);

SELECT * FROM TEST1 WHERE A=12 OR B=500

2：使用IN替换OR。 但是这个Solution也有很多局限性。在此不做过多阐述。

3：一般将OR的字句分解成多个查询，并且通过UNION ALL 或UNION连接起来。在联合索引或有索引覆盖的场景下。大部分情况下，UNION ALL的效率更高。但是并不是所有的UNION ALL都会比OR的SQL的代价（COST），特殊的情况或特殊的数据分布也会出现UNION ALL比OR代价要高的情况。例如，上面特殊的要求，从全表中取两条记录，如下所示

SELECT * FROM TEST1 WHERE A=12

UNION ALL

SELECT * FROM TEST1 WHERE B=500

UNON ALL语句的代价（Cost）要高与OR是因为它做了两次索引查找（Index Seek），而OR语句只做一次索引查找（Index Seek）就完成了。开销明显小一些，但是实际情况这类特殊情况比较少，实际情况的取数条件、数据都比这个简单案例要复杂得多。所以在大部分情况下，拆分为UNION ALL语句的效率要高于OR语句

另外一个案例，就是最上面实验的堆表TEST， 在字段OBJECT_ID上建有索引

SELECT * FROM TEST WHERE (OBJECT_ID >300 OR OBJECT_ID =105);

SELECT * FROM TEST WHERE OBJECT_ID >300

UNION ALL

SELECT * FROM TEST WHERE OBJECT_ID =105;

可以从下面看出两者开销不同的地方在于IO方面，两者开销之所以有区别，是因为第二个SQL多了一次扫描（索引查找）

总结：

    在实际开发环境中，OR这种写法确实会带来很多不确定性，尽量使用UNION 或IN替换OR。我们需要遵循一些规则，但是也不能认为它就是一成不变的，永为真理。具体场景、具体环境具体分析。要知其然知其所以然。在微软亚太区数据库技术支持组的官方博客中就有一个案例SQL Server性能问题案例解析 （3）也是OR引起的性能案例。 博客中有个观点，我觉得挺赞的：”需要注意的是，对于OR或UNION，并没有确定的孰优孰劣，使用时要进行测试才能确定。“ 。

来源： SQL Server优化技巧——如何避免查询条件OR引起的性能问题 – 潇湘隐者 – 博客园

之前写过一篇博客“SQL SERVER中关于OR会导致索引扫描或全表扫描的浅析”，里面介绍了OR可能会引起全表扫描或索引扫描的各种案例，以及如何优化查询条件中含有OR的SQL语句的几种方法，其实还有一些方法可以用来优化这种问题，这里简单介绍一下。

如下所示，下面的SQL语句之所有出现这种写法，是因为程序的查询界面，可能有多个输入性的查询条件，往往用户只填了一个或部分查询条件（业务情况，应该不用详细介绍，大家都能明白），但是程序里面没有通过判断查询条件生成不同的SQL语句，而是用一个SQL搞定，不管用户没有填写JobNo这个查询条件，下面这种写法：WHERE ISNULL(@JobNo, ”) = ”  OR JobNo = @JobNo都能满足条件，实现逻辑功能。

DECLARE @GenerateDateStart DATETIME ,    @GenerateDateEnd DATETIME ,    @JobNo NVARCHAR(200) ,    @GkNo NVARCHAR(200);SET @JobNo = ‘PT19B030’;SET @GkNo = ‘PV19-1-8050’;    SELECT    *  FROM      [dbo].[GEW_UnitConsumption] AS A            LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = ‘Y’                                                           AND A.GewUnitConsumptionId = B.RootUnitConsumptionID  WHERE     ( ISNULL(@JobNo, ”) = ”              OR A.JobNo = @JobNo            )            AND ( ISNULL(@GkNo, ”) = ”                  OR A.GkNo = @GkNo                );

其实，如果根据查询条件动态生成SQL语句，的确能避免查询条件中出现OR的情形，但是动态SQL语句没有上面语句简单和通熟易懂，尤其是查询条件较多的情况下。只能说各有利弊。这里暂且不讨论那种策略的优劣。

下面介绍一种技巧，如何避免OR引起的索引扫描或全表扫描问题。我们可以使用CASE WHEN改写一下这个SQL语句，就能避免OR引起的执行计划不走索引查找(Index Seek)的情况，如下所示：

DECLARE @GenerateDateStart DATETIME ,    @GenerateDateEnd DATETIME ,    @JobNo NVARCHAR(200) ,    @GkNo NVARCHAR(200);SET @JobNo = ‘PT19B030’;SET @GkNo = ‘PV19-1-8050’;   SELECT  *FROM    [dbo].[GEW_UnitConsumption] AS A        LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = ‘Y’                                                       AND A.GewUnitConsumptionId = B.RootUnitConsumptionIDWHERE   CASE WHEN ISNULL(@JobNo, ”) = ” THEN A.JobNo             ELSE @JobNo        END = JobNo        AND CASE WHEN ISNULL(@GkNo, ”) = ” THEN A.GkNo                 ELSE GkNo            END = @GkNo;

测试对比发现性能改善非常明显，当然这种优化技巧也是有局限性的，并不能解决所有OR引起的性能问题（没有银弹！）。如下所示，对于下面这种情况，这种技巧也是无能为力！

SELECT * FROM TEST1 WHERE A=12 OR B=500

——————————————分割线————————————————-

网友MSSQL123反馈：他测试的一个案例发现这种技巧无效，个人测试验证发现确实如此，后面发现个人遇到的仅仅是一个特殊个例（当时生产环境那个场景下确实生效了），后面经过大量测试发现，很多情况下CASE WHEN这种技巧无效，也就是说单个案例不具有通用性，后面进一步测试分析，发现我得出的结论是错误的。

当然在错误的基础上，进一步测试验证，发现还是有技巧优化OR引起的性能问题的，这也是我后续补充的原因,请见下文分析：

我们首先简单构造一个测试环境案例,测试环境为SQL Server 2014

CREATE TABLE TEST_OPTION_COMPILE (OBJECT_ID  INT, NAME VARCHAR(16)); CREATE CLUSTERED INDEX PK_TEST_OPTION_COMPILE ON TEST_OPTION_COMPILE(OBJECT_ID);   DECLARE @Index INT =0; WHILE @Index < 100000BEGIN     INSERT INTO TEST_OPTION_COMPILE    SELECT @Index, ‘kerry’+CAST(@Index AS VARCHAR(7));        SET @Index = @Index +1;END  CREATE INDEX IX_TEST_OPTION_COMPILE_N1 ON TEST_OPTION_COMPILE(NAME);UPDATE STATISTICS TEST_OPTION_COMPILE WITH FULLSCAN;

如下测试所示，发现这个例子中，CASE WHEN完全无效，使用这种SQL写法，依然走Index Scan

DECLARE @name VARCHAR(8);SET @name = ‘kerry8’ SELECT  NAMEFROM    dbo.TEST_OPTION_COMPILEWHERE   CASE WHEN ISNULL(@name, ”) = ” THEN NAME             ELSE @name        END = NAME; SELECT  NAMEFROM    dbo.TEST_OPTION_COMPILEWHERE   ( ISNULL(@name, ”) = ”          OR NAME = @name        )

如果我们在SQL后面加上OPTION(RECOMPILE)的话，那么SQL就会走索引查找（Index Seek),其实下面两个SQL语句，如果都加上OPTION(RECOMPILE)的话，它们都会走索引。这是什么情况呢？

接下来我们对比分析一下，看看SQL语句有无OPTION(RECOMPILE)的区别，如下所示：

 DECLARE @name VARCHAR(8);SET @name = ‘kerry8’ SELECT  NAMEFROM    dbo.TEST_OPTION_COMPILEWHERE   CASE WHEN ISNULL(@name, ”) = ” THEN NAME             ELSE @name        END = NAME ; SELECT  NAMEFROM    dbo.TEST_OPTION_COMPILEWHERE   CASE WHEN ISNULL(@name, ”) = ” THEN NAME             ELSE @name        END = NAME OPTION(RECOMPILE)

如下所示，如果没有OPTION(RECOMPILE)的话，执行计划走Index Scan，预估行数（Estimated Number of Rows）是100000， 而实际行数（Actual Number of Rows）是1，

如果SQL中有OPTION(RECOMPILE)的话，执行计划走Index Seek，预估行数（Estimated Number of Rows）是1， 而实际行数（Actual Number of Rows）是1，从对比我们可以看出，加上OPTION(RECOMPILE)的话，SQL的执行计划要准确很多，那么为什么呢？这里是因为OPTION(RECOMPILE)开启了Parameter Embedding Optimization

关于Parameter Embedding Optimization，这里简单介绍一下，详情参考Parameter Sniffing, Embedding, and the RECOMPILE Options 和参考资料的相关文档。

参数嗅探值使优化器可以使用参数值来得出基数估计。 WITH RECOMPILE和OPTION（RECOMPILE）均会生成查询计划，并根据每次执行时的实际参数值计算出估算值。

　　相比WITH RECOMPILE这种强制重编译的方式，OPTION(RECOMPILE)中的参数嵌入优化（Parameter Embedding Optimization）的机制更进一步：查询解析期间，查询参数被文字常量值替代。 解析器能够神奇的将复杂问题简单化，并且在随后的查询优化可能会进一步完善这些内容。

Microsoft在SQL Server 2008（后RTM）中引入了参数嵌入优化（Parameter Embedding Optimization）。 这个特性扩展了参数嗅探优化。 它能使用基数估计值来嗅探参数以影响计划。具体参考官方文档“Changed behaviour of OPTION RECOMPILE syntax in SQL Server 2008 SP1 cumulative update #5”

总结： 我们可以使用OPTION(RECOMPILE)（确切的说，是Parameter Embedding Optimization）这种技巧来避免查询条件中OR引起的性能问题，这确实是一个SQL Server优化技巧，至于我前面的结论，这是一个错误结论（使用CASE WHEN改写一下这个SQL语句，就能避免OR引起的执行计划不走索引查找(Index Seek)）。在缺乏严谨的论证、充分的测试就草率的得出了一个结论，以后要引以为戒！。

参考资料：

https://www.cnblogs.com/wy123/p/6262800.html

https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options