Скрипты SQL Server для обновления в базе индексов

Данный скрипт поможет перестраивать все индексы в базе. Это позволит оптимизировать работу индексов SQL Server, а значит, ускорить работу сайта в целом.

Скрипт реорганизует индексы по всем таблицам

CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable 'INSERT INTO #RowCounts
SELECT COUNT_BIG(*) AS NumberOfRows,
''?'' as TableName FROM ?'

---SELECT TableName,NumberOfRows FROM #RowCounts ORDER BY NumberOfRows DESC,TableName
declare @TableName nvarchar(256)
declare @NumberOfRows int
declare cur CURSOR LOCAL for
select TableName, NumberOfRows from #RowCounts

open cur

fetch next from cur into @TableName, @NumberOfRows

while @@FETCH_STATUS = 0 BEGIN
print @TableName
EXEC ('ALTER INDEX ALL ON ' +@TableName + ' REORGANIZE ;')
fetch next from cur into @TableName, @NumberOfRows
END

close cur
deallocate cur

DROP TABLE #RowCounts

Создаем батник для запуска SQL
cd c:\backup\
sqlcmd.exe -i reorg-index.sql -o reorg-index-log.txt

Назначенное задание - 1 р в день в 3 часа ночи.
Где ставить индексы:
на внешних ключах,
на полях даты, по которым идет сортировка,
на GUID,
на полях икоторые идут в where
на полях участвующих в JOIN
Поиск, где можно установить индексы:
select d.name AS DatabaseName, mid.*
from sys.dm_db_missing_index_details mid
join sys.databases d ON mid.database_id=d.database_id

Более сложный и менее стабильный способ (не берем его).

Создаем таблицу
USE [rudenas2]
GO
/****** Object: Table [dbo].[pr_index_stat] Script Date: 07.05.2015 9:34:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[pr_index_stat](
[proc_id] [int] NULL,
[start_time] [datetime] NULL,
[end_time] [datetime] NULL,
[database_id] [smallint] NULL,
[object_id] [int] NULL,
[table_name] [varchar](50) NULL,
[index_id] [int] NULL,
[index_name] [varchar](50) NULL,
[avg_frag_percent_before] [float] NULL,
[fragment_count_before] [bigint] NULL,
[pages_count_before] [bigint] NULL,
[fill_factor] [tinyint] NULL,
[partition_num] [int] NULL,
[avg_frag_percent_after] [float] NULL,
[fragment_count_after] [bigint] NULL,
[pages_count_after] [bigint] NULL,
[action] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Создаем хранимую процедуру
USE [rudenas2]
GO
/****** Object: StoredProcedure [dbo].[RescanIndex] Script Date: 07.05.2015 9:31:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[RescanIndex]
AS BEGIN

begin tran IndexServ

DECLARE @currentProcID INT
SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM pr_index_stat

--И заполняем таблицу данными о состоянии индексов
INSERT INTO pr_index_stat (
proc_id,
database_id,
[object_id],
table_name,
index_id,
index_name,
avg_frag_percent_before,
fragment_count_before,
pages_count_before,
fill_factor,
partition_num)
SELECT
@currentProcID,
dm.database_id,
dm.[object_id],
tbl.name,
dm.index_id,
idx.name,
dm.avg_fragmentation_in_percent,
dm.fragment_count,
dm.page_count,
idx.fill_factor,
dm.partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id
INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id
WHERE page_count > 8
AND avg_fragmentation_in_percent > 10
AND dm.index_id > 0
--Обьявим необходимые переменные
DECLARE @partitioncount INT --Количество секций
DECLARE @action VARCHAR(10) --Действие, которые мы будем делать с индексом
DECLARE @start_time DATETIME --Начало выполнения запроса ALTER INDEX
DECLARE @end_time DATETIME --Конец выполнения запроса ALTER INDEX
--см описание таблицы
DECLARE @object_id INT
DECLARE @index_id INT
DECLARE @tableName VARCHAR(250)
DECLARE @indexName VARCHAR(250)
DECLARE @defrag FLOAT
DECLARE @partition_num INT
DECLARE @fill_factor INT
--Сам запрос, который мы будем выполнять, я поставил MAX, потому как иногда меняю такие скрипты, и забываю поправить размер данной переменной, в результате получаю ошибку.
DECLARE @sql NVARCHAR(MAX)

--Далее объявляем курсор
DECLARE defragCur CURSOR FOR
SELECT
[object_id],
index_id,
table_name,
index_name,
avg_frag_percent_before,
fill_factor,
partition_num
FROM pr_index_stat
WHERE proc_id = @currentProcID
ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы

OPEN defragCur
FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @object_id AND index_id = @index_id;

--В моем случае, важно держать неможко пустого места на страницах, потому, что вставка в тоже таблицы имеете место, и не хочеться тратить драгоценное время пользователей на разбиение страниц
IF (@fill_factor != 80)
BEGIN
SET @sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)'
SET @action = 'rebuild80'
END
ELSE
BEGIN --Тут все просто, действуем по рекомендации MS
IF (@defrag > 30) --Если фрагментация больше 30%, делаем REBUILD
BEGIN
SET @sql = @sql + N' REBUILD'
SET @action = 'rebuild'
END
ELSE --В противном случае REORGINIZE
BEGIN
SET @sql = @sql + N' REORGANIZE'
SET @action = 'reorginize'
END
END

--Если есть несколько секций
IF @partitioncount > 1
SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5))

print @sql --Вывод выполняемого запроса

--Фиксируем время старта
SET @start_time = GETDATE()
EXEC sp_executesql @sql
--И время завершения
SET @end_time = GETDATE()

--Сохраняем время в таблицу
UPDATE pr_index_stat
SET
start_time = @start_time,
end_time = @end_time,
[action] = @action
WHERE proc_id = @currentProcID
AND [object_id] = @object_id
AND index_id = @index_id

FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
END
CLOSE defragCur
DEALLOCATE defragCur

UPDATE dba
SET
dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent,
dba.fragment_count_after = dm.fragment_count,
dba.pages_count_after = dm.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
INNER JOIN pr_index_stat dba
ON dm.[object_id] = dba.[object_id]
AND dm.index_id = dba.index_id
WHERE dba.proc_id = @currentProcID
AND dm.index_id > 0
commit tran

end

Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.