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

Page History: Reducing SQL Server Index Fragmentation

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: 2010/06/24 00:36


To view info

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==

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 (오전 12:33:11) Bek: defragment: 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 (mghc_board, @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.