Скрипты для реиндексации, перестройки индексов в SQL 2005

02.07.10

База данных - Инструменты администратора БД

Пару скриптов, их доработка и обсуждение

SQL я знаю достаточно мало. Особенно тонкости. Поэтому просьба к знающим людям - дополните меня и ответьте на вопросы, которые есть в статье.

Как советуют знающие люди в своих статьях (в частности Гилев в статье по созданию базы 1с в SQL 2005) нужно регулярно дефрагментировать индексы и обновлять статистику. Для этого есть штатные средства и простой скрипт


http://www.gilev.ru/1c/mssql/dbreindex.htm

 


 

USE db1cut
GO
DECLARE @MyTable varchar(252)
DECLARE @MyIndex varchar(252)
DECLARE @DSQL varchar(8000)
DECLARE MyCursor CURSOR FOR
SELECT o.name, i.name
FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id
WHERE (o.xtype = 'U') AND (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0) AND (i.dpages > 0)
ORDER BY o.name, i.indid

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex
WHILE @@FETCH_STATUS=0

BEGIN
PRINT 'Перестройка индекса '+@MyIndex+' из таблицы '+@MyTable
SET @DSQL = ' BEGIN TRY
ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = ON,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
END TRY

BEGIN CATCH ALTER INDEX '+@MyIndex+' ON '+@MyTable+
' REBUILD WITH (ONLINE = OFF,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON) END CATCH;'
EXEC(@DSQL)

FETCH NEXT FROM MyCursor INTO @MyTable, @MyIndex

END
CLOSE MyCursor
DEALLOCATE MyCursor

 


Тут вроде всё понятно - пробегает по индексам и перестраивает их... Обычно перестройка (Rebuild) сопровождается обновлением статистики, но параметр STATISTICS_NORECOMPUTE = ON отключает пересчет статистики. В связи с этим вопрос: из каких соображение в данном скрипте отключен пересчет статистики? Ведь в любом случае после реорганизации/перестройки требуется пересчитать статистику...Или всё-таки нет?

Но данный скрип имеет недостаток - на больших базах долго выполняется. При круглосуточной работе неприменим. Для этого есть скрипты с избирательной перестройкой, дефрагментацией

 

http://mamyshev.spaces.live.com/blog/cns!89D4F9A2EB82023D!127.trak

 


use master

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_defragment_frag_indexes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_defragment_frag_indexes]

go



CREATE PROCEDURE sp_defragment_frag_indexes @maxfrag DECIMAL, @whatif VARCHAR (6) = '' AS



-- Объявляем необходимые переменные

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

DECLARE @tableidchar VARCHAR(255)



-- На всякий случай проверяем,что база данных пользовательская

SELECT @dbname = db_name();



IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

BEGIN

PRINT ' Эта процедура не может быть запущена для системных БД';

RETURN

END



-- Начинаем проверку уровня фрагментации

-- Вначале объявляем курсор

DECLARE tables CURSOR FOR

SELECT convert(varchar,so.id) FROM sysobjects so JOIN sysindexes si ON so.id = si.id WHERE so.type ='U' AND si.indid < 2 AND si.rows > 0;

-- Затем создаем временную таблицу для хранения информации о фрагментации

CREATE TABLE #fraglist (ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255),IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL);

-- Открываем курсор

OPEN tables

-- Для каждой таблицы в базе данных выполняем команду DBCC SHOWCONTIG

FETCH NEXT FROM tables INTO @tableidchar

WHILE @@FETCH_STATUS = 0

BEGIN

-- Проходим по всем индексам для таблицы

INSERT INTO #fraglist

EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT

FROM tables

INTO @tableidchar

END

-- Закрываем курсор

CLOSE tables

DEALLOCATE tables

-- Для проверки выводим информацию из временной таблицы

SELECT * FROM #fraglist



-- Теперь необходимо произвести дефрагментацию

-- Вначале опять объявляем курсор

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity FROM #fraglist f JOIN sysobjects so ON f.ObjectId=so.id WHERE ScanDensity <= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Выводим для проверки информацию о начале дефрагментации

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Открываем курсор

OPEN indexes

-- Проходим циклом по всем индексам

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC INDEXDEFRAG (' + @dbname + ', ' + RTRIM(@tablename) + ', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

SELECT 'Выполняем: ' + @execstr;

if @whatif <> 'whatif' EXEC (@execstr);

SET QUOTED_IDENTIFIER OFF

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

-- Затем закрываем курсор

CLOSE indexes;

DEALLOCATE indexes;



-- Отчитываемся о времени завершения

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE());



-- Удаляем временную таблицу

DROP TABLE #fraglist;

GO

---------------------------------------------------------------------------------------------



Использование:

---------------------------------------------------------------------------------------------

-- Дефрагментировать индексы таблиц базы данных Test, для которых значение параметра Scan Density < 80%



USE Test

EXEC sp_defragment_frag_indexes 80.00;
 



Скрипт производит выборочную дефрагментацию регистров...НО! Скрип предназначен для SQL 2000 и для дефрагментации использует конструкцию 

DBCC INDEXDEFRAG

Для 2005 она подходит, но советуют использовать конструкцию  

Alter Index....Reorganize. Соответственно строчка заменяется.

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE;'  

Кроме того, в скрипте нет обновления статистики

Если добавить, то кусок скрипта нужно исправить на


-- Проходим циклом по всем индексам

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'ALTER INDEX [' + RTRIM(@indexname) + '] ON [' + RTRIM(@tablename) + '] REORGANIZE;'

PRINT 'Выполняем: ' + @execstr;

if @whatif <> 'whatif' EXEC (@execstr);

SELECT @execstr = 'UPDATE STATISTICS '+RTRIM(@tablename) + ' ' + RTRIM(@indexname)

PRINT 'Выполняем: ' + @execstr;

if @whatif <> 'whatif' EXEC (@execstr);

SET QUOTED_IDENTIFIER OFF

FETCH NEXT FROM indexes INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

 

 


Чуть поискав ещё можно найти скрипт, которые более подробнее анализирует индексы перед дефрагментацией. И кроме того для некоторых из них делает перестройку.

 

http://msmvps.com/blogs/gladchenko/archive/2008/03/31/1563721.aspx



-- INDEX REBUILD or REORGANIZE
DECLARE @SQL varchar(256), @DB_ID int;
SET @DB_ID = (SELECT DB_ID());

DECLARE reindex CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT 'ALTER INDEX ALL ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);' AS [Инструкция T-SQL]
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE afp.database_id = @DB_ID
AND afp.index_type_desc IN ('CLUSTERED INDEX')
AND (afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND afp.page_count > 12
UNION ALL
SELECT [Инструкция T-SQL] =
CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent <= 60
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);'
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
END
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN sys.indexes AS i
ON (afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND afp.database_id = @DB_ID
AND afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND (
(afp.avg_fragmentation_in_percent >= 10 AND afp.avg_fragmentation_in_percent < 15)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
)
AND afp.page_count > 12
AND afp.OBJECT_ID NOT IN (
SELECT OBJECT_ID
FROM sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED')
WHERE database_id = @DB_ID
AND index_type_desc IN ('CLUSTERED INDEX')
AND (avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
AND page_count > 1
)
ORDER BY [Инструкция T-SQL]

OPEN GLOBAL reindex
WHILE 1 = 1
BEGIN
FETCH reindex INTO @SQL
IF @@fetch_status <> 0 BREAK
-- EXEC(@SQL)
PRINT @SQL
END
CLOSE GLOBAL reindex
DEALLOCATE reindex

 


 

Скрипт опять-таки забывает про обновление статистики при реорганизации...Может всё-таки обновлять статистику не надо? Если я правильно изменил (ну не знаю я это язык, а проверить ещё не успел), то кусок с обновление статистики будет таким:

 

CASE
WHEN afp.avg_fragmentation_in_percent >= 15
OR afp.avg_page_space_used_in_percent <= 60
THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);'
WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
  THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;
 UPDATE STATISTICS ['+OBJECT_NAME(afp.OBJECT_ID) + ']  [' + i.name + '] ;'
END

 

Но и эти скрипты с выборочной дефрагментацией, как показала моя частная практика, неэффективен по времени и результату... Его, конечно, можно ночью запускать, но уже днем через пару часов интенсивной работы начинаются ЖУТКИЕ тормоза. Вплоть до того, что проведение по управленческому учету документов реализации длится более 10 МИНУТ! Ещё год назад я выяснил, что задержка происходит в одном месте - регистр "Партии товаров на складах". Именно он фрагментируется до состояния каши каждые 3 часа... 

Тогда же был написан скрипт по точечной дефрагментации индексов этого регистра. Таблицу sql этого регистра можно посмотреть с помощью какой-нибудь обработки (той, которой пользуюсь, в инете больше не нашел). После этого выполняются последовательно реорганизация и обновления статистики для каждого индекса самой таблицы и таблицы итогов. Это было на SQL 2000.

После перехода на SQL 2005 я решил отказаться от точечной дефрагментации, т.к. на бухгалтерской копии этой же базы, которая крутится на SQL 2005 уже 2 года, таких тормозов с проведением не было (хотя железо там слабее). Но фиг... опять словил те же самые грабли... И написал этот скрипт заново, только теперь с rebuild... В связи с этим вопрос. Rebuild шести индексов каждые 3 часа с очисткой кэша чем чревата кроме устранения всех тормозов в проведении документов??

Скрипт получился такой:

ALTER INDEX _Accum12425_ByDims19545_RTRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByPeriod_TRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByProperty18792_RTRN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12425_ByRecorder_RN ON dbo._AccumRg12425 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12443_ByDims_TRRRRRRRRRN ON dbo._AccumRgT12443 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
ALTER INDEX _Accum12443_ByDims19545_TR ON dbo._AccumRgT12443 REBUILD WITH (SORT_IN_TEMPDB = ON);
go
DBCC FREEPROCCACHE

 

Выполняется каждые 3 часа в пиковое время: с 7 до 21... 

 

Осталось сделать выбор на ночной скрипт выборочной дефрагментации, о которых я говорил выше... Какой посоветуете?

Да и на выходной в ночь, наверное нужно запускать полную перестройку индексов и обновление статистики?

См. также

Автоподбор ролей для профилей и групп доступа в любых типовых базах 1С УТ 11, КА 2, ERP2, Розница 2/3, УНФ 16/3, БП 3, ЗУП 3 и подобных (УФ, Платформа 8.3.14+)

Инструменты администратора БД Роли и права 8.3.14 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:Документооборот 1С:Зарплата и кадры государственного учреждения 3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x 1С:Управление нашей фирмой 3.0 1С:Розница 3.0 Платные (руб)

Роли… Вы тратите много времени и сил на подбор ролей среди около 2400 в ERP или 1500 в Рознице 2, пытаясь понять какими правами они обладают? Вы все время смотрите права в конфигураторе или отчетах чтоб создать нормальные профили доступа? Вы хотите наглядно видеть какие права дает профиль и редактировать все в простом виде? А может хотите просто указать подсистему и дать права на просмотр и добавление на объекты и не лезть в дебри прав и чтоб обработка сама подобрала нужные роли? Все это теперь стало возможно! Обновление от 15.12.2023, версия 1.1.

12000 руб.

06.12.2023    2756    11    1    

30

Infostart УДиФ: Управление данными и формами

Инструменты администратора БД Инструментарий разработчика Роли и права Платформа 1С v8.3 Конфигурации 1cv8 Россия Платные (руб)

Расширение позволяет без изменения кода конфигурации выполнять проверки при вводе данных, скрывать от пользователя недоступные ему данные, выполнять код в обработчиках. Не изменяет данные конфигурации, легко устанавливается практически на любую конфигурацию на управляемых формах.

10000 руб.

10.11.2023    3244    10    1    

31

SALE! 30%

PowerTools

Инструментарий разработчика Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Россия Платные (руб)

Универсальный инструмент программиста для администрирования конфигураций. Сборник наиболее часто используемых обработок под единым интерфейсом.

3600 2520 руб.

14.01.2013    177341    1070    0    

846

Ускоренное проведение документов (x4), устранение ошибок 60/62 счетов и зачет авансов (Бухгалтерия 3.0)

Закрытие периода Инструменты администратора БД Корректировка данных Бухгалтерский учет 1С:Бухгалтерия 3.0 Россия Бухгалтерский учет Платные (руб)

Расширение «Оперативное проведение» в 4 раза уменьшает время проведения документов и закрытия месяца. Является комплексным решением проблем 62 и 60 счетов. Оптимизирует проведение при включенной функциональной опции «Раздельный учет НДС». Используется в более 10 организациях уже 2 года. Совместимо с конфигурацией Бухгалтерия 3.0 (+КОРП).

14400 руб.

29.04.2020    27149    78    146    

59

"Менеджер потоков 2.1": УПП: "Восстановление партий"

Инструменты администратора БД Платформа 1С v8.3 1С:Управление производственным предприятием Россия Бухгалтерский учет Управленческий учет Платные (руб)

Как оптимизировать то, что, считалось, не поддается оптимизации? Как повысить доступность базы данных? Как проводить самую «времяемкую» операцию не по паре раз в неделю, а по несколько раз в день*? Ответ есть!

20000 руб.

12.09.2019    11706    5    9    

7

Брандмауэр для сервера 1С Предприятие 8 - внешнее управление сеансами

Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Платные (руб)

Управление возможностью начала и возобновления сеансов пользователей по различным условиям, ограничение общего числа возможных сеансов для работы с информационной базой, резервирование возможности работы с информационной базой определенных польззователей, запрет запуска нескольких сеансов для пользователя, журнализация событий начала (возобновления) и завершения (гибернации) сеансов, ведение списка активных сеансов для информационных баз кластера серверов

3600 руб.

06.02.2017    31041    31    18    

47

Система хранения присоединенных файлов в томах на диске

Инструменты администратора БД Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Управление производственным предприятием Платные (руб)

Конфигурация Комплексная автоматизация 1.1 (и УПП 1.3 тоже) хранит файлы и изображения в справочнике Хранилище дополнительной информации в реквизите Хранилище типа ХранилищеЗначений. Та же история с ВложениямиЭлектроннойПочты. Но при этом присоединенные файлы в Электронном документообороте хранит в томах на диске. Эта доработка позволяет использовать стандартный механизм хранения файлов, изображений и вложений электронных писем в томах на диске. При этом можно разделить тома хранения по объектам конфигурации.

4200 руб.

10.11.2015    61228    87    59    

72

Хранилище файлов на SQL

Инструменты администратора БД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Управленческий учет Платные (руб)

Привязка файлов / сканов к объектам 1С с сохранением их на SQL-сервере

12000 руб.

09.10.2019    10894    5    8    

9
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. markers 274 07.07.10 06:56 Сейчас в теме
Подсказать не могу, но однозначно плюс за сборку всего в 1 месте!
2. zzz_natali 61 07.07.10 10:28 Сейчас в теме
Тебе сюда:
http://www.sql.ru/forum
Но там народ слишком манерный(профессиональный) - разжевывать не будут. Что-то на молекулярном уровне подскажут, а концептуально и академически прокачивайся сам.
5. anig99 2841 07.07.10 20:08 Сейчас в теме
(2) вот-вот....я в зависать ещё в одном форуме... мне пока хватает тут (:
3. rozer 305 07.07.10 13:04 Сейчас в теме
Начинающим можно посоветовать использовать Maintenance Plans...
4. anig99 2841 07.07.10 20:07 Сейчас в теме
(3) Maintenance Plans - вещь в данном случае неподходящая
6. idef 10.07.10 08:50 Сейчас в теме
Rebuild шести индексов каждые 3 часа с очисткой кэша чем чревата кроме устранения всех тормозов в проведении документов??

Череват тормозами базы на время перестройки индекса

Для какой-то оценки проделанной вами работы неплохо привести статистику: размер базы, размер соответствующих таблиц и индексов, время выполнения запроса до и после скрипта, скорость деградации производительности в течении 3 часов между запусками скриптов. Кроме того неплохо знать конфигурацию сервера и конфигурацию 1С.

ПС. А какой смысл очистки кэша каждые 3 часа?
Вы понимаете что этой операцией вы удаляете скомпилированные запросы?
7. anig99 2841 12.07.10 08:07 Сейчас в теме
(6) Да... Понимаю, что очисткой кэша удаляю скомпилированные запросы. Но очистку кэша рекомендуют делать после обновления статистики. Практика показывает, что иногда достаточно только очистки кэша, чтобы скорость записи в регистр увеличилась.
Размер базы 100 гБ.
Деградация производительности... Снижение скорости оперативного проведения по регистру партии товаров на складах с 3-4 секунд до 60-90 секунд. Снижение скорости проведение по регистру партии товаров на складах в документах более 3-4 дней с 5-6 секунд до 500 и более секунд. При этом фрагментация индексов регистра партии товаров на складах не превышает 5 процентов...

Платформа 1с 8.2.11.236 Конфа УПП 1.3 Win 2003 x64 SQL 2005 x64. 2 четырех ядерных XEON по 2ghz, 16 Гб оперативы на IBM серваке.... Но... Эти же самые затыки наблюдались на всех версиях 8.1 и УПП 1.2. На 32х битной оси и SQL 2000.
8. idef 12.07.10 16:10 Сейчас в теме
(7) ИМХО проблемы у вас не в фрагментации индексов, а в большом количестве приходов, в результате чего Скуль считает более эффективным сканирование таблиц, чем использование индексов. Тут надо смотреть план запроса и сам тормозной запрос, возможно его легче переделать, чем каждый раз "греметь винтами", кста, а дисковую систему почему сокрыли???
9. anig99 2841 13.07.10 08:36 Сейчас в теме
(8) и как бы в 1с этого не учли? По-моему, тут больше проблема в том, что постоянно исправляют документы за прошлые числа (не будет развивать тему запрета изменений задним числом и т.д. - 1с тупо не учел специфику РЕАЛЬНОЙ работы и не сделал удобного механизма альтернативного неоперативному исправлению и перепроведения документов).
Согласен, что нужно анализировать запросы на уровне SQL. Только опыта в этом никакого. Да и после этого не совсем понятно, что делать.
11. idef 13.07.10 19:36 Сейчас в теме
(9) А вы считаете что в 1С всЁ учитывают ;-)
А когда через год база будет 200Гб - каждый час реиндексировать(дефрагментировать) базу???

Проблема здесь в том что производительность в v8 надо рассматривать в рамках всей системы, т.е. аппаратная платформа+сервер БД+Сервер приложений+платформа 1С.
Базы размером более 50Г уже можно считать большими и настройка таких баз должна выполнятся индивидуально. Возьмите к примеру файловые БД - у них очень хорошая производительность для объемов до 1-2Гб в однопользовательском режиме, а с некоторого объема уже начинаются приколы.
Я не считаю что ваш путь не правильный. Но, может стоит начать разбираться с индексами и статистикой? Это даст гораздо больший выиграш в скорости и масштабируемости. При этом все те регламенты, которые предусмотрены в сервере БД никто не отменял.
Если много INSERT/UPDATE/DELETE в базе, то статистика очень быстро устаревает(характерно для OLTP баз) ее нужно обновлять периодически.
А вы знаете, что обновление статистики выполняется сервером автоматически для некоторых индексов, а для некоторых автообновление отключено. Проверьте включено-ли автообновление статистики примерно так:
DBCC SHOW_STATISTICS (N'dbo._acc1', _Acc1_ByCode_SR)
или так:
EXEC sp_autostats 'dbo._acc1'
Смысл параметров я думаю понятен.
Еще есть синхронное и асинхронное обновление статистик.
Статистика очень тонкий инструмент в сервере SQL. Теперь я думаю понятно почему большинство скриптов которые вы нашли не обновляет статистики.

(10) Диски разные, но это могут быть RAID[0,1,5,6,10]? Нужное подчеркнуть.
Монитор производительности - очередь записи на диск, например???
объем оперативы = Объем базы/4, ИМХО конечно.
12. anig99 2841 14.07.10 12:54 Сейчас в теме
(11) зачем всю? В том то и дело, что провал в производительности происходит только для 1 конкретного регистра...
13. anig99 2841 14.07.10 12:57 Сейчас в теме
(11) а в остальном буду копать дальше
14. anig99 2841 16.07.10 11:52 Сейчас в теме
(11) уточняю... automatically recompute statistics в параметрах индекса - это и есть автообновление статистики для конкретного индекса? Если да, то они были включены. Сейчас для пробы я их отключил и оставил только скрипт по перестройке этих конкретных индексов с цикличностью в час.
15. idef 20.07.10 17:53 Сейчас в теме
(14) Да. Но зачем отключать? Для экспериментов? И каковы результаты?
16. anig99 2841 21.07.10 07:56 Сейчас в теме
(15) так как знания мои малы - прибегаю к шаманству (: Уже по результатам отключения можно сделать предположение. Автообновление статистики вещь хорошая, но при определенных условиях статистика может формироваться неверная, что приводит к резкому увеличению времени записи в регистр. А чем чаще обновлять статистику - тем больше вероятность, что это произойдет.
Сейчас периодичность обновления статистики - 3 часа. Полет нормальный.
17. idef 22.07.10 18:48 Сейчас в теме
(16) А можно текст задания, который обновляет статистику на всеобщее рассмотрение?
18. anig99 2841 22.07.10 22:59 Сейчас в теме
(17) если я правильно все понял, то после перестройки индексов статистики обновляется в любом случае. Поэтому использую скрипт, который идет последний в статье. На данный момент перешел на исполнение этого скрипта раз в сутки + 1-2 раза в день вручную, если начинаются затыки.
19. idef 23.07.10 12:55 Сейчас в теме
(18) Однозначно после ALTER INDEX статистика будет обновлена, но только если включено автообновление, а оно у вас похоже включено.
А какие размеры имеют индексы и таблицы и сколько времени это занимает?
Какая утилизация ресурсов?
Я это к тому, что может допустимый вариант решения: ночью реиндексация, а в течении дня обновление статистики несколько раз.
10. anig99 2841 13.07.10 08:41 Сейчас в теме
(8) Пока я вижу практический результат от реиндексации, обновления статистики и очистки кэша - устранения провала в производительности. Может попробовать только обновление статистики?

По дискам - tempdb, log и сама база данных разнесены на физически разные диски...
Думаете проблема может быть во фрагментации? На диске с базой ничего другого нет.
20. MICK77 13 13.10.11 12:12 Сейчас в теме
уже проскакивало :
"общее
- Обновление статистики - каждые 2 часа
- DBCC FREEPROCCACHE - каждые 2 часа"

только вот общее - это для всех баз включая системные или только пользовательские?
21. anig99 2841 13.10.11 21:37 Сейчас в теме
(20) всю статистику обновлять - это долго. очистку кэша - можно. Перестраивать несколько индексов и обновлять по ним статистику с очисткой кэша - вот всё, что нужно. У меня это происходит сейчас 3 раза в день. Занимает меньше 2 минут - результат поразительный.
На другой базе иногда вручную делаю очистку кэша. Для чего? У меня есть проверка, которая делате много-много мелких запросов. Иногда один этот запрос начинает выполнятся больше секунды - запрос должен выполнится больше сотни тысяч раз. Такая производительность не устраивает. Очистка кэша (иногда не с первого раза) снижает время выполнения до приемлемых 0,08-0,04 сек в зависимости от близости к текущей дате по времени.
22. vis_tmp 32 08.02.17 09:36 Сейчас в теме
А для SQL 2008 можно применять без изменений?
Оставьте свое сообщение