SQL Server 自动重建出现碎片的索引

杀鸡取卵办法:应对这种景况有以下三种艺术:1、购买第三方软件2、自个儿编制程序叁个小软件来施行,不过那么些逻辑性须要相比高,何况编制程序的力量要有早晚档案的次序,这一个本人有时未有。3、使用本文介绍的格局,至于是甚,接着看:接纳SQLCMD在SQLServer上举办多少个剧本:SQLCMD:使用
sqlcmd 实用工具,能够在指令提醒符处、在 SQLCMD
情势下的“查询编辑器”中、在 Windows 脚本文件中要么在 SQL Server
代理作业的操作系统 (Cmd.exeState of Qatar 作业步骤中输入 Transact-SQL
语句、系统经过新昌梅林戏本文件。 此实用工具使用 ODBC 实行 Transact-SQL
批管理。详细语法能够到网络检索,这里就不贴出来。SQLCMD有三个很紧要的授命::r,记住,SQLCMD是大大小小写敏感的。当:r开采正在运营SQL脚本,它会告诉SQLCMD把这几个文件所援用的文本生龙活虎并放入调用脚本中。那将报告您,结束近日的单个查询。并再一次调度查询,把应该提到的询问放到适当的地点。其余,使用:r命令在贰个批管理中施行八个剧本,使得你能够定义八个单身的变量集,用于富含全部脚本,可是不包括GO终结符。从2006之后引入SQLCMD,能够用来以后替代osql工具。倘诺你素不相识SQLCMD,能够感觉它是贰个能从操作系统施行T-SQL命令宁海平调本的命令行工具。下边例子中,创设5个功效在TestDB数据库上有关联的sql文件。第3个脚本叫做CREATE_DB.sql,用于创设三个叫做TestDB的数据库。这些本子包括了4个其余的脚本,用于转移别的表、表插入、索引创设和存款和储蓄进程的创始。二个.bat文件用于创立用来施行SQLCMD命令。第一步:先创设叁个在C盘下的文书夹:C:\Scripts。然后把脚本寄放到那些文件夹中:脚本1:CREATE_DB.sql

1.索引碎片的发生?

譬喻:大家导入了某些顾客的资料,大家掌握此顾客的姓名是ZhangShan,大家想知道,在我们的作业数据库(eg:NorthWind卡塔尔国中,有何数据表的怎么字段设置了此姓名值ZhangShan,通过上边包车型大巴SQL,大家就足以落成此目标,此处的SQL搜索自英特网,在这做了部分更改。
后生可畏、找出数据是String类型
适用于寻觅Text,NText,Varchar,Nvarchar,Char,NChar等类型
1、创设存储进程:My_Search_StringInGivenTable 复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure
[dbo].[My_Search_StringInGivenTable] Script Date: 09/25/2011
15:37:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[My_Search_StringInGivenTable]
(@SearchString NVARCHAR(MAX), @Table_Schema sysname, @Table_Name
sysname) AS BEGIN DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX),
@PkColumn NVARCHAR(MAX) — Get all character columns SET @Columns =
STUFF((SELECT ‘, ‘ + QUOTENAME(Column_Name) FROM
INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN
(‘text’,’ntext’,’varchar’,’nvarchar’,’char’,’nchar’) AND TABLE_NAME =
@Table_Name ORDER BY COLUMN_NAME FOR XML PATH(”)),1,2,”) IF @Columns
IS NULL — no character columns RETURN -1 — Get columns for select
statement – we need to convert all columns to nvarchar(max) SET @Cols =
STUFF((SELECT ‘, cast(‘ + QUOTENAME(Column_Name) + ‘ as nvarchar(max))
as ‘ + QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE
DATA_TYPE IN (‘text’,’ntext’,’varchar’,’nvarchar’,’char’,’nchar’) AND
TABLE_NAME = @Table_Name ORDER BY COLUMN_NAME FOR XML
PATH(”)),1,2,”) SET @PkColumn = STUFF((SELECT N’ + ”|” + ‘ + ‘
cast(‘ + QUOTENAME(CU.COLUMN_NAME) + ‘ as nvarchar(max))’ FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME =
CU.TABLE_NAME AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA AND
Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE
=’PRIMARY KEY’ AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME
= @Table_Name ORDER BY CU.COLUMN_NAME FOR XML PATH(”)),1,9,”) IF
@PkColumn IS NULL SELECT @PkColumn = ‘cast(NULL as nvarchar(max))’ —
set select statement using dynamic UNPIVOT DECLARE @SQL NVARCHAR(MAX)
SET @SQL = ‘select *, ‘ + QUOTENAME(@Table_Schema,””) + ‘as [Table
Schema], ‘ + QUOTENAME(@Table_Name,””) + ‘ as [Table Name]’ + ‘
from (select ‘+ @PkColumn + ‘ as [PK Column], ‘ + @Cols + ‘ from ‘ +
QUOTENAME(@Table_Name) + ‘ )src UNPIVOT ([Column Value] for [Column
Name] IN (‘ + @Columns + ‘)) unpvt WHERE [Column Value] LIKE ”%” +
@SearchString + ”%”’ –print @SQL EXECUTE sp_ExecuteSQL @SQL,
N’@SearchString nvarchar(max卡塔尔(قطر‎’, @SearchString END
2、创造寻觅存款和储蓄进程:My_Search_String_AllTables
此存款和储蓄进程将遍历钦赐数据库的兼具表,并采用方面创造的蕴藏进度My_Search_StringInGivenTable来得到每种表的查找结果。
复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure
[dbo].[My_Search_String_AllTables] Script Date: 09/25/2011
15:41:58 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF
GO CREATE PROC [dbo].[My_Search_String_AllTables] ( @SearchString
NVARCHAR(MAX) ) AS BEGIN CREATE TABLE #RESULT ([PK COLUMN]
NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname,
[TABLE SCHEMA] sysname, [TABLE Name] sysname) DECLARE @Table_Name
sysname, @Table_Schema sysname DECLARE curAllTables CURSOR LOCAL
FORWARD_ONLY STATIC READ_ONLY FOR SELECT Table_Schema, Table_Name
FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER
BY Table_Schema, Table_Name OPEN curAllTables FETCH curAllTables INTO
@Table_Schema, @Table_Name WHILE (@@FETCH_STATUS = 0) — Loop through
all tables in the database BEGIN INSERT #RESULT EXECUTE
My_Search_StringInGivenTable @SearchString, @Table_Schema,
@Table_Name FETCH curAllTables INTO @Table_Schema, @Table_Name END —
while CLOSE curAllTables DEALLOCATE curAllTables — Return results
SELECT * FROM #RESULT ORDER BY [Table Name] END 使用示例 复制代码 代码如下: USE [NORTHWIND] GO DECLARE
@return_value int EXEC @return_value =
[dbo].[My_Search_String_AllTables] @SearchString = N’WantValue’
SELECT ‘Return Value’ = @return_value GO
还会有另二个本子,正是直接创设一个仓库储存进度来赢得所要结果,但个体认为前段时间那些形式更具灵活性
复制代码 代码如下: USE [NORTHWIND] GO
/****** Object: StoredProcedure [dbo].[ZL_SearchAllTables]
Script Date: 09/25/2011 15:44:10 ******/ SET ANSI_NULLS OFF GO
SET QUOTED_IDENTIFIER OFF GO CREATE PROC
[dbo].[ZL_SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256),
@ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = ”
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””) WHILE
@TableName IS NOT NULL BEGIN SET @ColumnName = ” SET @TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)) FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND
QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) @TableName AND
OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + ‘.’ +
QUOTENAME(TABLE_NAME) ), ‘IsMSShipped’ ) = 0 ) WHILE (@TableName IS NOT
NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT
MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME =
PARSENAME(@TableName, 1) AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’,
‘nvarchar’) AND QUOTENAME(COLUMN_NAME) @ColumnName ) IF @ColumnName IS
NOT NULL BEGIN INSERT INTO #Results EXEC ( ‘SELECT ”’ + @TableName +
‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630) FROM ‘ +
@TableName + ‘ (NOLOCK) ‘ + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ +
@SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results
END [code] 二、搜索数据是Int类型 适用于搜索smallint, tinyint, int,
bigint等体系 1、创制存款和储蓄进程 My_Search_IntInGivenTable [code] USE
[NORTHWIND] GO /****** Object: StoredProcedure
[dbo].[My_Search_IntInGivenTable] Script Date: 09/25/2011 15:45:46
******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE
PROCEDURE [dbo].[My_Search_IntInGivenTable] (@SearchValue INT,
@Table_Schema sysname, @Table_Name sysname卡塔尔(قطر‎ AS BEGIN DECLARE @Columns
NVARCHA奥迪Q5(MAX卡塔尔 , @Cols NVARCHAKuga(MAX卡塔尔国 , @PkColumn NVARCHA汉兰达(MAXState of Qatar , @SQL
NVARCHAWrangler(MAXState of Qatar –剖断并创办#Result表 IF OBJECT_ID(‘TempDB..#Result’,
‘U’) IS NOT NULL DROP TABLE #Result CREATE TABLE #RESULT ( [PK
COLUMN] NVARCHAR(MAX) , [COLUMN VALUE] BIGINT , [COLUMN Name]
SYSNAME , [TABLE SCHEMA] SYSNAME , [TABLE Name] SYSNAME )–先导寻找给定的表 DECLARE curAllTables CU奥迪Q3SOXC90 LOCAL FO大切诺基WA奥迪Q5D_ONLY
STATIC READ_ONLY FOR SELECT Table_Schema , Table_Name FROM
INFORMATION_SCHEMA.Tables WHERE Table_Name =@Table_Name OPEN
curAllTables WHILE 1 = 1 BEGIN FETCH curAllTables INTO @Table_Schema,
@Table_Name IF @@FETCH_STATUS 0 — Loop through all tables in the
database BREAK PRINT CHAR(13) + ‘Processing ‘ +
QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name) — Get all int
columns SET @Columns = STUFF(( SELECT ‘, ‘ + QUOTENAME(Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE LIKE ‘%int’ AND
TABLE_NAME = @Table_Name AND table_schema = @Table_Schema ORDER BY
COLUMN_NAME FOR XML PATH(”) ), 1, 2, ”) IF @Columns IS NULL BEGIN
PRINT ‘No int columns in the ‘ + QUOTENAME(@Table_Schema) + ‘.’ +
QUOTENAME(@Table_Name) CONTINUE END — Get columns for select statement

复制代码 代码如下:/* SCRIPT:
CREATE_DB.sql *//* 创建TestDB数据库 */

由于在表里大批量的插入、修改、删除操作而使索引页分歧。假如索引有了高的碎片,有三种情景,意气风发种境况是扫描索引须要开支比超级多的日子,另黄金年代种意况是在查询的时候索引根本不应用索引,都会导致质量减少。

  • we need to convert all columns to bigint SET @Cols = STUFF(( SELECT ‘,
    cast(‘ + QUOTENAME(Column_Name) + ‘ as bigint) as ‘ +
    QUOTENAME(Column_Name) FROM INFORMATION_SCHEMA.COLUMNS WHERE
    DATA_TYPE LIKE ‘%int’ AND TABLE_NAME = @Table_Name ORDER BY
    COLUMN_NAME FOR XML PATH(”) ), 1, 2, ”) — Create PK column(s) SET
    @PkColumn = STUFF(( SELECT N’ + ”|” + ‘ + ‘ cast(‘ +
    QUOTENAME(CU.COLUMN_NAME) + ‘ as nvarchar(max))’ FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU ON TC.TABLE_NAME =
    CU.TABLE_NAME AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA AND
    Tc.CONSTRAINT_NAME = CU.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE =
    ‘PRIMARY KEY’ AND TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME =
    @Table_Name ORDER BY CU.COLUMN_NAME FOR XML PATH(”) ), 1, 9, ”) IF
    @PkColumn IS NULL SELECT @PkColumn = ‘cast(NULL as nvarchar(max))’ —
    set select statement using dynamic UNPIVOT SET @SQL = ‘select *, ‘ +
    QUOTENAME(@Table_Schema, ””) + ‘as [Table Schema], ‘ +
    QUOTENAME(@Table_Name, ””) + ‘ as [Table Name]’ + ‘ from (select ‘
  • @PkColumn + ‘ as [PK Column], ‘ + @Cols + ‘ from ‘ +
    QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name) + ‘ )src
    UNPIVOT ([Column Value] for [Column Name] IN (‘ + @Columns + ‘))
    unpvt WHERE [Column Value] = @SearchValue’ –print @SQL — if we get
    errors, we may want to print generated SQL INSERT #RESULT ( [PK
    COLUMN] , [COLUMN VALUE] , [COLUMN Name] , [TABLE SCHEMA] ,
    [TABLE Name] ) EXECUTE sp_ExecuteSQL @SQL, N’@SearchValue int’,
    @SearchValue PRINT ‘Found ‘ + CAST(@@ROWCOUNT AS VARCHAR(10)) + ‘
    records in ‘ + QUOTENAME(@Table_Schema) + ‘.’ + QUOTENAME(@Table_Name)
    END CLOSE curAllTables DEALLOCATE curAllTables SELECT * FROM #RESULT
    ORDER BY [TABLE SCHEMA] , [TABLE Name] END
    2、创制搜索存款和储蓄进程My_Search_Int_AllTables,与地点肖似,此存款和储蓄进度将调用
    My_Search_IntInGivenTable来贯彻所遍历的每二个数据表的查找结果 复制代码 代码如下: USE [NORTHWIND] GO
    /****** Object: StoredProcedure
    [dbo].[My_Search_Int_AllTables] Script Date: 09/25/2011 15:48:29
    ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
    CREATE PROC [dbo].[My_Search_Int_AllTables] ( @SearchValue INT )
    AS BEGIN CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN
    VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA]
    sysname, [TABLE Name] sysname) DECLARE @Table_Name sysname,
    @Table_Schema sysname DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY
    STATIC READ_ONLY FOR SELECT Table_Schema, Table_Name FROM
    INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ‘BASE TABLE’ ORDER BY
    Table_Schema, Table_Name OPEN curAllTables FETCH curAllTables INTO
    @Table_Schema, @Table_Name WHILE (@@FETCH_STATUS = 0) — Loop through
    all tables in the database BEGIN INSERT #RESULT EXECUTE
    My_Search_StringInGivenTable @SearchValue, @Table_Schema,
    @Table_Name FETCH curAllTables INTO @Table_Schema, @Table_Name END —
    while CLOSE curAllTables DEALLOCATE curAllTables — Return results
    SELECT * FROM #RESULT ORDER BY [Table Name] END 使用示例 复制代码 代码如下: USE [NORTHWIND] GO DECLARE
    @return_value int EXEC @return_value =
    [dbo].[My_Search_Int_AllTables] @SearchValue = 68 SELECT ‘Return
    Value’ = @return_value GO Note:
    1、你能够借助地点风流罗曼蒂克、二中的第一个存款和储蓄进度来落到实处只寻找钦赐有些数据表的效应。
    2、对于任何数据类型如:Date,Real等等均能够此为参照进行改正。
    3、此办法对大型数据库会很耗费时间,所以尽量在小数据库上调解。当要求在大数据库上操作时,尽量避开数据库使用高峰时刻并要有耐烦。

— This is the main caller for each scriptSET NOCOUNT ONGO

2.碎片门类分为:

PPRADOINT ‘开头创办TestDB数据库’IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE
NAME = ‘TestDB’State of QatarDROP DATABASE TestDBGOCREATE DATABASE TestDBGO

2.1 内部破碎

:On Error exit

鉴于索引页里的数据插入或改善操作而发生,以数量作为荒疏矩阵的款型的布满而告终,那将招致数据页的增添,进而扩充查询时间。

:r c:\Scripts\CREATE_TABLES.sql:r c:\Scripts\TABLE_INSERTS.sql:r
c:\Scripts\CREATE_INDEXES.sql:r c:\Scripts\CREATE_PROCEDURES.sql

2.2外界破碎

P牧马人INT ‘创设实现’GO

出于索引/数据页的多寡插入或校勘而产生,以页码分离和在文件系统里不连贯的新的索引页的分红而告终,数据库服务器无法应用预读操作的亮点,因为:下多个相关联的数码页不挨着,而且这一个相关连的底下的页码或然在数据文件的任哪儿方。

脚本2:CREATE_INDEXES.sql

 

复制代码 代码如下:/* 成立索引 */PHighlanderINT
‘早先创设索引’GOUSE TestDBGOIF NOT EXISTS ( SELECT 1 FROM SYS.INDEXES
WHERE NAME = ‘IX_EMPLOYEE_LASTNAME’ ) CREATE INDEX
IX_EMPLOYEE_LASTNAME ON DBO.EMPLOYEE(LASTNAME, FIRSTNAME)GOIF NOT
EXISTS ( SELECT 1 FROM SYS.INDEXES WHERE NAME =
‘IX_TIMECARD_EMPLOYEEID’ ) CREATE INDEX IX_TIMECARD_EMPLOYEEID ON
DBO.TIMECARD(EMPLOYEEID)GO

 

脚本3:CREATE_PROCEDURES.sql