Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Всем доброго дня!
На SQL2005 stdedt x64 выполняю дефрагментацию индексов с помощью скрипта (приведен ниже). Поставил степень фрагментации 80 для примера. Но после выполнения скрипта все равно осталось около 60 кластеризованных индексов со степенью фрагментации более 80%.

Подскажите, пожалуйста, как побороть это?

USE dbsql_Work;
GO
-- Ensure a USE <databasename> statement has been executed first.
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 > 80.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';
        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
30 сен 11, 11:29    [11360575]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А сколько страниц в этих индексах?
30 сен 11, 11:55    [11360917]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
tpg,

page_count от 5 до 7.

И с количеством таких индексов наврал, из 120 шт. И это только с фрагментацией больше 80%.
30 сен 11, 12:10    [11361168]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
page_count от 5 до 7.

И страницы эти наверное разбросаны по разным экстентам ?
30 сен 11, 12:11    [11361184]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Glory
RomanYeliseyev
page_count от 5 до 7.

И страницы эти наверное разбросаны по разным экстентам ?


Сорри, а как проверить?

PS: чувствую куда Вы клоните... Если на разных, то не дефрагментируется - где-то читал уже по этому поводу.
30 сен 11, 12:17    [11361250]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
DBCC INDEXDEFRAG
30 сен 11, 12:19    [11361274]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
DBCC SHOWCONTIG конечно же
30 сен 11, 12:19    [11361276]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Glory
DBCC INDEXDEFRAG


exec sp_msforeachtable N'DBCC INDEXDEFRAG (dbsql_work, ''?'')';
провожу еженедельно
30 сен 11, 12:32    [11361416]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Уважаемые, подскажите, пожалуйста, как вывести информацию по этим экстентам для всех индексов с фрагментацией более 30? Выбирать каждый (а таких у меня 770 штук) индекс замучаюсь вручную.

И подскажите, а степень фрагментации может увеличиться в базе с двумя файлами данных (mdf + ndf)?
2 окт 11, 12:49    [11369598]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
Уважаемые, подскажите, пожалуйста, как вывести информацию по этим экстентам для всех индексов с фрагментацией более 30?

да не проведете вы дефрагментацию смешанных экстентов
2 окт 11, 14:18    [11369677]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Glory
RomanYeliseyev
Уважаемые, подскажите, пожалуйста, как вывести информацию по этим экстентам для всех индексов с фрагментацией более 30?

да не проведете вы дефрагментацию смешанных экстентов

И что теперь с этим делать? Жить так дальше?

SQL под 1С используется.
3 окт 11, 13:13    [11373407]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
RomanYeliseyev
И что теперь с этим делать? Жить так дальше?
А чем мешают-то?
3 окт 11, 13:14    [11373413]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
И что теперь с этим делать? Жить так дальше?

Какой смысл дефрагментировать индекс с размером меньше 64Кб ?
3 окт 11, 13:15    [11373420]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Количество индексов таких смущает. Правда самый большой индекс, размеров в сотню гигов дефрагментируется на ура. Вся база весит 250Гб.
3 окт 11, 15:25    [11374804]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
iljy
Member

Откуда:
Сообщений: 8711
RomanYeliseyev
Количество индексов таких смущает.

Встаньте перед зеркалом и строго наругайтесь. Ваша база, вы чего хотите-то?
3 окт 11, 15:35    [11374885]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
iljy
RomanYeliseyev
Количество индексов таких смущает.

Встаньте перед зеркалом и строго наругайтесь. Ваша база, вы чего хотите-то?


Придется :о)
Количество фрагментированных индексов снизить хотел, но судя по всему не получится (с учетом прочитанного и сказанного здесь).

А некластеризованные индексы можно дефрагментировать?
3 окт 11, 16:14    [11375366]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
Количество фрагментированных индексов снизить хотел, но судя по всему не получится

Это самоцель или вы предпологаете выиграит от этого что-то ?
3 окт 11, 16:16    [11375399]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
iljy
Member

Откуда:
Сообщений: 8711
RomanYeliseyev,

не "не получится", а смысла нет. Почитайте про принципы распределения памяти и про смешаные экстенты.

Дефрагментировать некластерные индексы можно и нужно, но тоже только большие.
3 окт 11, 16:17    [11375409]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Glory
RomanYeliseyev
Количество фрагментированных индексов снизить хотел, но судя по всему не получится

Это самоцель или вы предпологаете выиграит от этого что-то ?


Надеюсь получить прирост производительности. Но возможно это количество фрагментированных кластерных индексов на столько мало, что не получу ничего от этого.

Это как с винтом: увидел невероятную фрагментацию и задался целью убрать ее любой ценой.
3 окт 11, 16:57    [11375905]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
iljy
RomanYeliseyev,

не "не получится", а смысла нет. Почитайте про принципы распределения памяти и про смешаные экстенты.

Дефрагментировать некластерные индексы можно и нужно, но тоже только большие.


Про смешанные экстенты читал уже, почитаю еще, чтобы закрепилось.

Некластерных индексов у меня вообще 1700 штук с page_count от 2 до 40. Как бороться, подскажите, пожалуйста, или пните в нужном направлении?
3 окт 11, 17:01    [11375941]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
Некластерных индексов у меня вообще 1700 штук с page_count от 2 до 40. Как бороться, подскажите, пожалуйста, или пните в нужном направлении?

Индексы, размер которых больше-равен 8 страницам, удалить и создать

Индексы, размер которых меньше 8 страницам, удалить, добавить в таблицу фиктивных данных так, чтобы размер индекса превысил 8 страниц, потом создать индекс и удалить фиктивные данные.
3 окт 11, 17:05    [11375989]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31990
RomanYeliseyev
Это как с винтом: увидел невероятную фрагментацию и задался целью убрать ее любой ценой.
С винтом аналогия неправильная. Никто не называет "невероятной" дефрагментацию финда, на котором фрагментировано 60 секторов. А у вас именно так.
RomanYeliseyev
Но возможно это количество фрагментированных кластерных индексов на столько мало, что не получу ничего от этого.
В данном солучае оно не просто мало: эта фрагментация сделана как раз для повышения производительности, уменьшить её можно, только выделив каждой таблице персональный экстент и соответственно увеличив количество IO и увеличив потребность в памяти для кеширования.
3 окт 11, 17:05    [11375995]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
RomanYeliseyev
Member

Откуда:
Сообщений: 137
Glory
RomanYeliseyev
Некластерных индексов у меня вообще 1700 штук с page_count от 2 до 40. Как бороться, подскажите, пожалуйста, или пните в нужном направлении?

Индексы, размер которых больше-равен 8 страницам, удалить и создать

Индексы, размер которых меньше 8 страницам, удалить, добавить в таблицу фиктивных данных так, чтобы размер индекса превысил 8 страниц, потом создать индекс и удалить фиктивные данные.


Скриптом поделитесь, если есть таковой? Всем полезен будет, не только я благодарен буду... :о)
3 окт 11, 17:18    [11376142]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
Glory
Member

Откуда:
Сообщений: 104751
RomanYeliseyev
Скриптом поделитесь, если есть таковой?

Скриптом чего ?
3 окт 11, 17:20    [11376163]     Ответить | Цитировать Сообщить модератору
 Re: Дефрагментация индексов  [new]
iljy
Member

Откуда:
Сообщений: 8711
RomanYeliseyev
Скриптом поделитесь, если есть таковой? Всем полезен будет, не только я благодарен буду... :о)

Не будет. Не нужен никому такой скрипт, вам уже объяснили почему. Не надо влезать в потроха сервера, работы которых вы не понимаете.
3 окт 11, 17:24    [11376204]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить