正文
SQL Server 索引的自动维护 <第十三篇>
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
在有大量事务的数据库中，表和索引随着时间的推移而碎片化。因此，为了增进性能，应该定期检查表和索引的碎片，并对具有大量碎片的进行整理。
1、确定当前数据库中所有需要分析碎片的表。
2、确定所有表和索引的碎片。
3、考虑一下因素以确定需要进行碎片整理的表和索引。
- 高的碎片水平-avg_fragmentation_in_percent大于20%；
- 不是非常小的表或索引-也就是page_count大于8的；
4、整理具有大量碎片的表和索引；
这里给出一个样板SQL存储过程，它执行以下操作；
- 遍历系统上的所有数据库并确认符合碎片条件的每个数据库中表上的索引，并将它们保存到一个临时表中；
- 根据碎片水平，重新整理碎片较少的索引并重建碎片很多的索引。
CREATE PROCEDURE IndexDefrag
ASDECLARE @DBName NVARCHAR(255)
,@TableName NVARCHAR(255)
,@SchemaName NVARCHAR(255)
,@IndexName NVARCHAR(255)
,@PctFrag DECIMALDECLARE @Defrag NVARCHAR(MAX)IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'#Frag'))
DROP TABLE #FragCREATE TABLE #Frag
(DBName NVARCHAR(255)
,TableName NVARCHAR(255)
,SchemaName NVARCHAR(255)
,IndexName NVARCHAR(255)
,AvgFragment DECIMAL)EXEC sp_msforeachdb 'INSERT INTO #Frag (
DBName,
TableName,
SchemaName,
IndexName,
AvgFragment
) SELECT ''?'' AS DBName
,t.Name AS TableName
,sc.Name AS SchemaName
,i.name AS IndexName
,s.avg_fragmentation_in_percent
FROM ?.sys.dm_db_index_physical_stats(DB_ID(''?''), NULL, NULL,
NULL, ''Sampled'') AS s
JOIN ?.sys.indexes i
ON s.Object_Id = i.Object_id
AND s.Index_id = i.Index_id
JOIN ?.sys.tables t
ON i.Object_id = t.Object_Id
JOIN ?.sys.schemas sc
ON t.schema_id = sc.SCHEMA_ID
WHERE s.avg_fragmentation_in_percent > 20
AND t.TYPE = ''U''
AND s.page_count > 8
ORDER BY TableName,IndexName'DECLARE cList CURSOR
FOR SELECT * FROM #FragOPEN cList
FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFrag
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PctFrag BETWEEN 20.0 AND 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE'
EXEC sp_executesql @Defrag
PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName
END
ELSE IF @PctFrag > 40.0
BEGIN
SET @Defrag = N'ALTER INDEX ' + @IndexName + ' ON ' + @DBName + '.' + @SchemaName + '.' + @TableName + ' REBUILD'
EXEC sp_executesql @Defrag
PRINT 'Rebuild index: '+ @DBName + '.' + @SchemaName + '.' + @TableName +'.' + @IndexName
END FETCH NEXT FROM cList
INTO @DBName, @TableName,@SchemaName,@IndexName,@PctFragEND
CLOSE cList
DEALLOCATE cListDROP TABLE #Frag
为了自动化碎片分析过程，可以从SQL Server企业管理器中用以下简单的步骤创建一个SQL Server任务。
1、开启SQL Server代理;
2、打开Management Studio，右键单击，选择新建=》任务；
3、在新建任务对话框的“常规”页面中，输入任务名称和其他细节：
4、在新建任务对话框的“步骤”页面中，单击“新建”并输入用户数据库的SQL命令。
5、在新建任务步骤对话框“高级”页面上，输入报告碎片分析结果的输出文件名称：
6、单击“确定”按钮，返回新建作业对话框；
7、在新建任务对话框“计划”页面，单击“新建计划”，并输入运行SQL Server任务的合适计划：
安排这个存储过程在非高峰执行。为了确定数据库的数据库模式，记录整天的SQL Server：SQL Statistics\Batch Requests/sec性能计数器，它将展示数据库负载的波动。
8、单击“确定”按钮，返回新建任务对话框。
9、输入所有信息后，单击新建任务对话框中的“确定”按钮创建SQL Server任务。创建计划在一个固定时间间隔(每周)运行sp_indexDefrag存储过程的SQL Server任务。
10、确保SQL Server代理运行，这样SQL Server任务将自动根据设置的计划运行。
这个SQL任务将在每个星期天的凌晨1点分析每个数据库并且进行碎片整理。
SQL Server 索引的自动维护 <第十三篇>的更多相关文章- SQL Server 索引 之 书签查找 <;第十一篇>;
一.书签查找的概念 书签可以帮助SQL Server快速从非聚集索引条目导向到对应的行,其实这东西几句话我就能说明白. 如果表有聚集索引(区段结构),那么书签就是从非聚集索引找到聚集索引后,利用聚集索 ...
- SQL Server 索引知识-应用,维护
创建聚集索引 a索引键最好唯一(如果不唯一会隐形建立uniquier列(4字节)确保唯一,也就是这列都会复制到所有非聚集索引中) b聚集索引列所占空间应尽量小(否则也会使非聚集索引的空间变大) c聚集 ...
- 转： SQL Server索引的维护 - 索引碎片、填充因子
转:http://www.cnblogs.com/kissdodog/archive/2013/06/14/3135412.html 实际上,索引的维护主要包括以下两个方面: 页拆分 碎片 这两个问题 ...
- SQL Server索引的维护 - 索引碎片、填充因子 <;第三篇>;
实际上,索引的维护主要包括以下两个方面: 页拆分 碎片 这两个问题都和页密度有关,虽然两者的表现形式在本质上有所区别,但是故障排除工具是一样的,因为处理是相同的. 对于非常小的表(比64KB小得多), ...
- SQL Server 索引维护（1）——系统常见的索引问题
前言: 在很多系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次D ...
- SQL Server 索引维护：系统常见的索引问题
在很多系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次DTA,里 ...
- SQL Server索引 (原理、存储)聚集索引、非聚集索引、堆 <;第一篇>;
一.存储结构 在SQL Server中,有许多不同的可用排列规则选项. 二进制:按字符的数字表示形式排序(ASCII码中,用数字32表示空格,用68表示字母"D").因为所有内容都 ...
- SQL Server 索引介绍
数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力 详细出处参考:http://www.jb51.net/article/30950.ht ...
- SQL Server索引设计 <;第五篇>;
SQL Server索引的设计主要考虑因素如下: 检查WHERE条件和连接条件列: 使用窄索引: 检查列的选择性: 检查列的数据类型: 考虑列顺序: 考虑索引类型(聚集索引OR非聚集索引): 一.检查 ...
随机推荐- 使用maven命令建立java项目
在terminal中输入: mvn archetype:generate -DgroupId={project-packaging} -DartifactId={project-name} -Darc ...
- 软件工程个人第二小项目——wc
github源码和工程文件地址:https://github.com/HuChengLing/wc 基本要求:要实现wc的基本功能即文件中字符数.单词数.行数的统计. 主要功能:文件中字符数.单词数. ...
- hdu 1166 线段树(sum+单点修改)
敌兵布阵 Time Limit: 2000/1000 MS (Java/Others) Memory Limit: 65536/32768 K (Java/Others) Total Submi ...
- C#代码中插入X86汇编
这两天在看C# SIMD相关的东西, 在爆栈上面搜到一段代码, 表示很震惊, 还是得贴出来… [UnmanagedFunctionPointer(CallingConvention.StdCall)] ...
- hihoCoder week16 RMQ-ST算法
RMQ问题 用的st表吧,读入数据挺多的,输出数据也挺多的 我还用了 cout<<endl; T了.. 真的是 做题不带脑子的 #include <bits/stdc++.h> ...
- Cortex-M4 Core Registers
Cortex-M4 Core Registers Goal: visualizing what happens to the Cortex-M4 core registers after reset ...
- [BZOJ1565][NOI2009]植物大战僵尸-[网络流-最小割+最大点权闭合子图+拓扑排序]
Description 传送门 Solution em本题知识点是用网络流求最大点权闭合子图. 闭合图定义:图中任何一个点u,若有边u->v,则v必定也在图中. 建图:运用最小割思想,将S向点权 ...
- 【实习项目记录】(一)加密算法MD5和RSA
什么是md5加密? MD5的全称是Message-Digest Algorithm 5(信息-摘要算法),在90年代初由MIT Laboratory for Computer Science和RSA ...
- 树链剖分【洛谷P1505】 [国家集训队]旅游
P1505 [国家集训队]旅游 题目描述 Ray 乐忠于旅游,这次他来到了T 城.T 城是一个水上城市,一共有 N 个景点,有些景点之间会用一座桥连接.为了方便游客到达每个景点但又为了节约成本,T 城 ...
- 获取安装后Apache、MySQL、Nginx、PHP编译时参数
# cat /usr/local/apache2/build/config.nice //获取Apache编译时的参数 #!/bin/sh # #Created by configure & ...
一.书签查找的概念 书签可以帮助SQL Server快速从非聚集索引条目导向到对应的行,其实这东西几句话我就能说明白. 如果表有聚集索引(区段结构),那么书签就是从非聚集索引找到聚集索引后,利用聚集索 ...
创建聚集索引 a索引键最好唯一(如果不唯一会隐形建立uniquier列(4字节)确保唯一,也就是这列都会复制到所有非聚集索引中) b聚集索引列所占空间应尽量小(否则也会使非聚集索引的空间变大) c聚集 ...
转:http://www.cnblogs.com/kissdodog/archive/2013/06/14/3135412.html 实际上,索引的维护主要包括以下两个方面: 页拆分 碎片 这两个问题 ...
实际上,索引的维护主要包括以下两个方面: 页拆分 碎片 这两个问题都和页密度有关,虽然两者的表现形式在本质上有所区别,但是故障排除工具是一样的,因为处理是相同的. 对于非常小的表(比64KB小得多), ...
前言: 在很多系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次D ...
在很多系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引(DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次DTA,里 ...
一.存储结构 在SQL Server中,有许多不同的可用排列规则选项. 二进制:按字符的数字表示形式排序(ASCII码中,用数字32表示空格,用68表示字母"D").因为所有内容都 ...
数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力 详细出处参考:http://www.jb51.net/article/30950.ht ...
SQL Server索引的设计主要考虑因素如下: 检查WHERE条件和连接条件列: 使用窄索引: 检查列的选择性: 检查列的数据类型: 考虑列顺序: 考虑索引类型(聚集索引OR非聚集索引): 一.检查 ...
- 使用maven命令建立java项目
在terminal中输入: mvn archetype:generate -DgroupId={project-packaging} -DartifactId={project-name} -Darc ...
- 软件工程个人第二小项目——wc
github源码和工程文件地址:https://github.com/HuChengLing/wc 基本要求:要实现wc的基本功能即文件中字符数.单词数.行数的统计. 主要功能:文件中字符数.单词数. ...
- hdu 1166 线段树(sum+单点修改)
敌兵布阵 Time Limit: 2000/1000 MS (Java/Others) Memory Limit: 65536/32768 K (Java/Others) Total Submi ...
- C#代码中插入X86汇编
这两天在看C# SIMD相关的东西, 在爆栈上面搜到一段代码, 表示很震惊, 还是得贴出来… [UnmanagedFunctionPointer(CallingConvention.StdCall)] ...
- hihoCoder week16 RMQ-ST算法
RMQ问题 用的st表吧,读入数据挺多的,输出数据也挺多的 我还用了 cout<<endl; T了.. 真的是 做题不带脑子的 #include <bits/stdc++.h> ...
- Cortex-M4 Core Registers
Cortex-M4 Core Registers Goal: visualizing what happens to the Cortex-M4 core registers after reset ...
- [BZOJ1565][NOI2009]植物大战僵尸-[网络流-最小割+最大点权闭合子图+拓扑排序]
Description 传送门 Solution em本题知识点是用网络流求最大点权闭合子图. 闭合图定义:图中任何一个点u,若有边u->v,则v必定也在图中. 建图:运用最小割思想,将S向点权 ...
- 【实习项目记录】(一)加密算法MD5和RSA
什么是md5加密? MD5的全称是Message-Digest Algorithm 5(信息-摘要算法),在90年代初由MIT Laboratory for Computer Science和RSA ...
- 树链剖分【洛谷P1505】 [国家集训队]旅游
P1505 [国家集训队]旅游 题目描述 Ray 乐忠于旅游,这次他来到了T 城.T 城是一个水上城市,一共有 N 个景点,有些景点之间会用一座桥连接.为了方便游客到达每个景点但又为了节约成本,T 城 ...
- 获取安装后Apache、MySQL、Nginx、PHP编译时参数
# cat /usr/local/apache2/build/config.nice //获取Apache编译时的参数 #!/bin/sh # #Created by configure & ...
