Welcome Guest, you are in: Login

MUST Creative Engineering Laboratory

RSS RSS

Navigation



Technical Doc



Search the wiki
»

MUST Corp.

MUST Corp.

www.must.or.kr

 Microsoft CERTIFIED Partner Software Development, Web Development, Data Platform

 Microsoft Small Business Specialist

MCSD

Microsoft Certified IT Professional

Microsoft Certified Professional Developer

Reducing SQL Server Index Fragmentation

RSS
Modified on 2010/06/24 00:53 by Administrator Categorized as Database

To view info

if you can understand numbers you can check current defragmentation, think about it and then decide, make 2nd step or not

DECLARE @TableName sysname
DECLARE cur_showfragmentation CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_showfragmentation
FETCH NEXT FROM cur_showfragmentation INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT 'Show fragmentation for the ' + @TableName + ' table'
SET @TableName = '[' + @TableName + ']'
DBCC SHOWCONTIG (@TableName)
FETCH NEXT FROM cur_showfragmentation INTO @TableName
END
CLOSE cur_showfragmentation
DEALLOCATE cur_showfragmentation

To defragment

don't forget to change db name
use db, and in the middle there is line
IF @indid <> 255 DBCC INDEXDEFRAG ([DBNAME], @TableName, @indid)



DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
SET @TableName='[' + @TableName + ']'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table'
IF @indid <> 255 DBCC INDEXDEFRAG ([DBNAME], @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
SET @TableName='[' + @TableName + ']'
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch

MUST Creative Engineering Laboratory

ImageImage Image Image

Image Image Image Image Image Image Image

Copyright © 2010 MUST Corp. All rights reserved. must@must.or.kr
This Program is released under the GNU General Public License v2. View the GNU General Public License v2 or visit the GNU website.