MSSQL索引与查询性能

/*
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
*/
--建自增变量的表
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xw_DateSubsection]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
    create table xw_DateSubsection 
    ( 
        nRec int identity(1,1) primary key not null,    --自增变量列
        caption varchar(100), 
        subsection varchar(7000)
    )
ALTER TABLE dbo.xw_DateSubsection
    DROP CONSTRAINT PK_xw_DateSubsection  --删除主键

ALTER TABLE dbo.xw_DateSubsection ADD CONSTRAINT
    PK_xw_DateSubsection PRIMARY KEY CLUSTERED 
    (
    nRec
    ) ON [PRIMARY]         --建主键（主键并不一定是聚集索引，这时可以在其他列上建聚集索引，当没有建聚集索引时，默认情况下主键就是聚集索引）

end
go

IF OBJECT_ID ( 'dbo.lab_table3' ) IS NOT NULL
BEGIN
    DROP TABLE lab_table3
END
GO
CREATE TABLE dbo.lab_table3
    (
        col1  INT IDENTITY(1,1) PRIMARY KEY CLUSTERED WITH FILLFACTOR = 90,
        col2  VARCHAR(10) NOT NULL DEFAULT 'Harry',
        col3  VARCHAR(10) NOT NULL DEFAULT 'Brenda',
        col4  VARCHAR(10) NOT NULL DEFAULT 'Larry'
    )

use fztest
dbcc DROPCLEANBUFFERS
dbcc FREEPROCCACHE

exec sp_helpindex 'dbo.DlyNdx'
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'IDX_Dlyndx_date_xw')
drop index DlyNdx.IDX_Dlyndx_date_xw
create clustered index IDX_Dlyndx_date_xw on dbo.dlyndx(date)  ON [PRIMARY]  --建聚集

IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'PK_Dlyndx_xw')
ALTER TABLE [dbo].[DlyNdx] DROP CONSTRAINT [PK_Dlyndx_xw]
ALTER TABLE dbo.DlyNdx ADD  CONSTRAINT PK_Dlyndx_xw PRIMARY KEY NONCLUSTERED (Vchcode)  ON [PRIMARY] --建唯一，非聚集(主键)

IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'IDX_Dlyndx_Vchtype_xw')
drop index dbo.DlyNdx.IDX_Dlyndx_Vchtype_xw
create NONCLUSTERED index IDX_Dlyndx_Vchtype_xw on dbo.dlyndx(VchType)  ON [PRIMARY] --建不唯一，非聚集

--create UNIQUE NONCLUSTERED index IDX_Dlyndx_Vchtype_xw on dbo.dlyndx(VchType,BranchId2,Period) 
--INCLUDE(Draft) --sql2000没有该功能 
--WITH (FILLFACTOR = 80,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) 
--ON [PRIMARY]

select * from dbo.DlyNdx

select * from dbo.DlyNdx
where date>='2011-12-31'

--查询表上索引的叶子数量和碎片情况
/*
select s.avg_fragmentation_in_percent,s.fragment_count,s.page_count,s.avg_page_space_used_in_percent
    ,s.record_count,avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(DB_ID('df'),OBJECT_ID(N'dbo.ctxs_splmbg1'),NULL,NULL,'Sampled'
) as s
*/
--显示 Employee 表的碎片信息
DBCC SHOWCONTIG ('dbo.DlyNdx');

--对索引进行碎片整理
DBCC INDEXDEFRAG (fztest, "dbo.DlyNdx", PK_Dlyndx_xw)
--使用填充因子值 70 对 DlyNdx 表重建所有索引
DBCC DBREINDEX ("dbo.DlyNdx", " ", 70);

/*
--########################################################################
--使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引,自动重新组织或重新生成数据库中平均碎片超过 10％ 的所有分区
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID('df'), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
SET NOCOUNT OFF;
GO
*/
--########################################################################
--使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理,对数据库中碎片数量在声明的阈值之上的所有索引进行碎片整理.
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
use s52
go
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO
--########################################################################

CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);
INSERT INTO #fraglist 
EXEC('DBCC SHOWCONTIG (''dbo.DlyNdx'') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
--EXEC('DBCC SHOWCONTIG (''dbo.DlyNdx'')')

select * from #fraglist

SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= 30 AND 
      INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0  --索引的深度    --根据指定的表标识号、索引或统计信息名称以及属性名称，返回已命名的索引或统计信息属性值。

select INDEXPROPERTY (ObjectId, IndexName, 'IndexFillFactor') from #fraglist
select INDEXPROPERTY (ObjectId, IndexName, 'IsClustered') from #fraglist
select INDEXPROPERTY (ObjectId, IndexName, 'IsDisabled') from #fraglist
select INDEXPROPERTY (ObjectId, IndexName, 'IsUnique') from #fraglist
--IndexFillFactor 创建索引或最后重新生成索引时使用的填充因子值
--IsClustered 索引是聚集的
--IsDisabled  索引被禁用
--IsUnique  索引是唯一的

DROP TABLE #fraglist;