sql indeks bakım

hocam aslında yanlış ekran attım. ekran linki aşağıdadır. yapmam gerekir mi

yapmam gerekir ise aşağıdaki komutu kopyalayıp yapıştıracağım. bir sorun olurmu canlı çalıştırırsam

veya sizin index rebuild yapmak için başka bir sorgunuz varsa verirmisini

DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model','distribution')
ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'''

-- create table cursor
EXEC (@cmd)
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
BEGIN
-- SQL 2005 yada sonrası
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
END
ELSE
BEGIN
-- SQL 2000 komutu
DBCC DBREINDEX(@Table,' ',@fillfactor)
END

FETCH NEXT FROM TableCursor INTO @Table
END

CLOSE TableCursor
DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

hocam indeksleri düzeltmem için verdiğiniz linkte tam olarak hangi komutları çalıştırmam lazım ve rebuilt işlemini nasıl çalıştırırım

Hocam 

üste verdiğiniz scripti çalıştırmam yeterlimi

2) rebuild index maintenance yaptım fakata öyle kaldı bunu çalıştıram için ne yaapmam lazım

3) aşağıda örnek var bu örnek işimi görürmü.

USE tredas
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (ONLINE=ON)';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

hocam kısacası bu benim işimi görürmü %80 olanları düzeltirmi

Bu örnekte olabilir, %30 a kadar bozul olan indexleri reorganize eder, %30 ve üzeri bozuk olanlar rebuild eder. Canlı sistem çalışırken yapmayın, gece uygun ssaatlerde yapın, yoksa herkes yavaşlar.

Merhaba Yavuz bey,

rebuild ettiğim halde rebuild olmayan indexlerim mevcut.Neden rebuild olmuyor olabilirler ?

SELECT
ps.object_id,
i.name as IndexName,
OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
OBJECT_NAME (ps.object_id) as ObjectName,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent desc

ile fragmentation  oranı 66,6666666666667

rebuild etmek için

ALTER INDEX [unique_seq_no]
ON [dbo].[sbc_progress_payment] REBUILD

komutunu kullanmaktayım bazı indexlerim rebuild oldu fakat bazıları rebuild olmamaktalar ..konu ile ilgili yardımınızı beklemekteyim..saygılar,

Yavuz Hoca, kısaca - özetle, indekslerin bozulmasının sebebi nedir ?

Neden bozulurlar ?

Teşekkürler.

Sorgu yoğunluğuna bağlı olarak yapı zamanla bozulur. Benim her gece çalışan indexlemem aşağıdadır.

CREATE PROC Indexleme
AS BEGIN
DECLARE @Db NVARCHAR(100);
SET @Db = 'Database')

SET NOCOUNT ON;
DECLARE
@OBJECT_ID INT,
@INDEX_NAME sysname,
@SCHEMA_NAME sysname,
@OBJECT_NAME sysname,
@AVG_FRAG float,
@command varchar(8000),
@RebuildCount int,
@ReOrganizeCount int

CREATE TABLE #tempIM (
[ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[INDEX_NAME] sysname NULL,
[OBJECT_ID] INT NULL,
[SCHEMA_NAME] sysname NULL,
[OBJECT_NAME] sysname NULL,
[AVG_FRAG] float
)
SELECT @RebuildCount=0,@ReOrganizeCount=0

--Get Fragentation values
SELECT @command=
'Use ' + @Db + ';
INSERT INTO #tempIM (OBJECT_ID, INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG)
SELECT
ps.object_id,
i.name as IndexName,
OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
OBJECT_NAME (ps.object_id) as ObjectName,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, ''LIMITED'') ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 30 AND ps.index_id > 0
and ps.database_id=DB_ID('''+@Db+''')
ORDER BY avg_fragmentation_in_percent desc
'

exec(@command)
DECLARE c CURSOR FAST_FORWARD FOR
SELECT OBJECT_ID,INDEX_NAME, SCHEMA_NAME, OBJECT_NAME, AVG_FRAG
FROM #tempIM
OPEN c
FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
WHILE @@FETCH_STATUS = 0
BEGIN
--Reorganize or Rebuild
IF @AVG_FRAG>30 BEGIN
SELECT @command = 'Use ' + @Db + '; ALTER INDEX [' + @INDEX_NAME +'] ON ['
+ @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REBUILD ';
SET @RebuildCount = @RebuildCount+1
END ELSE BEGIN
SELECT @command = 'Use ' + @Db + '; ALTER INDEX [' + @INDEX_NAME +'] ON ['
+ @SCHEMA_NAME + '].[' + @OBJECT_NAME + '] REORGANIZE ';
SET @ReOrganizeCount = @ReOrganizeCount+1
END

BEGIN TRY
EXEC (@command);
END TRY
BEGIN CATCH
END CATCH

FETCH NEXT FROM c INTO @OBJECT_ID, @INDEX_NAME, @SCHEMA_NAME, @OBJECT_NAME, @AVG_FRAG
END
CLOSE c
DEALLOCATE c

DROP TABLE #tempIM

SELECT cast(@RebuildCount as varchar(5))+' index Rebuild,'+cast(@ReOrganizeCount as varchar(5))+' index Reorganize edilmistir.' as Result
END