目次

インデックスの編成　再構築　統計情報の再取得

http://msdn.microsoft.com/ja-jp/library/ms189858%28v=SQL.90%29.aspx

統計情報の取得

SELECT
 so.name
 , ss.name
 , ss.auto_created
 , ss.user_created
 , ss.no_recompute
 , STATS_DATE(ss.object_id, ss.stats_id)
FROM
 sys.objects AS so
 LEFT JOIN
     sys.stats AS ss
 ON
     ss.object_id = so.object_id
WHERE
 type = ‘U’
ORDER BY
 so.name

統計情報の更新

EXEC sp_updatestats;

断片化の取得

declare @dbid smallint

set @dbid = DB_ID()

--インデックスの断片化(%)の検索【Simple Ver】
SELECT
b.name AS INDEX_NAME
, avg_fragmentation_in_percent as [全体の断片化％]
,a.*
FROM
sys.dm_db_index_physical_stats(@dbid, NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON
a.object_id = b.object_id
AND a.index_id = b.index_id
order by
avg_fragmentation_in_percent,
b.name

インデックスの再構築

USE [TESTDB]
GO
DECLARE @TableName sysname, @IndexName sysname
DECLARE @basesql nvarchar(max), @sql nvarchar(max)
DECLARE @Edition nvarchar(max)

SET @basesql = 'ALTER INDEX @1 On @2 REBUILD'

DECLARE IXC CURSOR FOR
SELECT
  OBJECT_NAME(object_id) AS TableName
  , name AS IndexName
FROM
  sys.indexes
WHERE
  OBJECT_SCHEMA_NAME (object_id) <> 'sys'
  AND
  index_id > 0
ORDER BY 1

OPEN IXC

FETCH NEXT FROM IXC
INTO @TableName, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @TableName + ':' + @IndexName
  
  SET @sql = REPLACE(@basesql, '@1', @IndexName)
  SET @sql = REPLACE(@sql, '@2', @TableName)
 
  EXECUTE (@sql)
  
  FETCH NEXT FROM IXC
  INTO @TableName, @IndexName   
END

CLOSE IXC
DEALLOCATE IXC