数据库|mysql教程
SQL,Server,索引维护
数据库-mysql教程
ios 新闻客户端 源码,vscode是写什么的,ubuntu界面 梨子,Tomcat如何加载jdk,爬虫动物宠物,服务器部署php环境,SEM和SEO的优劣势lzw
SQL Server 索引维护sql语句,有需要的朋友可以参考下。
某辅助销售网站源码,gcc与vscode,ubuntu发行系统,更改tomcat 编码,sqlite锁代码,java制作插件,前端的测试框架搭建,爬虫框架 谷歌,php 数据,SEO人才公寓,简单的个人音乐网站源码,网页样式模板,ext6 模板,aspx页面验证码下载,积分管理系统 开源,asp的电影程序源码lzw
好评返利 源码,ubuntu系统里截图,tomcat7升高版本乱码,入门爬虫+知乎,php命名空间不对为什么不报错,关于seo分类lzw
使用以下脚本查看数据库索引碎片的大小情况：
代码如下:
DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
以下使用脚本来处理维护作业：
代码如下:
/*Perform a ‘USE ‘ to select the database in which to run the script.*/
— Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr varchar(400);
DECLARE @objectid int;
Declare @IndexName varchar(500);
DECLARE @indexid int;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
DECLARE @TmpName varchar(500);
— Declare @TmpName =”
set @TmpName = ”
— 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,IndexName,IndexId, LogicalFrag
FROM #fraglist
WHERE INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0;
— Open the cursor.
OPEN indexes;
— Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @IndexName,@indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
if @frag < @maxfrag
Begin
SELECT @execstr = ‘ALTER INDEX [‘ + RTRIM(@IndexName) + ‘] ON [‘ + RTRIM(@tablename) + ‘] REORGANIZE WITH ( LOB_COMPACTION = ON ) ‘
print @maxfrag + ‘ ‘ + @execstr
End
else
Begin
SELECT @execstr = ‘ALTER INDEX [‘ + RTRIM(@IndexName) + ‘] ON [‘ + RTRIM(@tablename) + ‘] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )’
print @maxfrag + ‘ ‘ + @execstr
End
EXEC (@execstr);
–更新统计信息
IF @TmpName@tablename
BEGIN
SET @tmpName=@tableName
PRINT ‘UPDATE STATISTICS ‘+@TableName + ‘ WITH FULLSCAN ‘
EXEC (‘UPDATE STATISTICS ‘+@TableName + ‘ WITH FULLSCAN ‘)
END
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @IndexName,@indexid, @frag;
END;
— Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
— Delete the temporary table.
DROP TABLE #fraglist;
GO
