none
Тормоза базы и проблемы с обновлением статистики отдельных таблиц на SQL 2005 RRS feed

  • Вопрос

  • день добрый.

    SQL 2005 и 1C 8.2 УПП. База перевалила за 40Gb и при проводке доков по складу стала жутко тормозить. На форумах пишат делать обновление статистики для связ таблиц кажд 5 минут. Вычислили эти таблицы, в Execute руками процесс занимает ок 5 минут, так что шедулер я поставил на каждый час, но в итоге он не выполняет ничего, а постоянно натыкается на якобы недоступные таблицы, которые есть в реальности, и в разных логах он ругается на с разные таблицы...

    сам скрипт

    use [upp]
    GO
    UPDATE STATISTICS [dbo].[_AccumRg20188] 
    UPDATE STATISTICS [dbo].[_AccumRg20674] 
    UPDATE STATISTICS [dbo].[_AccumRg20697] 
    UPDATE STATISTICS [dbo].[_AccumRg20752] 
    UPDATE STATISTICS [dbo].[_AccumRg21067] 
    UPDATE STATISTICS [dbo].[_AccumRgT20214] 
    UPDATE STATISTICS [dbo].[_AccumRgT20695] 
    UPDATE STATISTICS [dbo].[_AccumRgT20723] 
    UPDATE STATISTICS [dbo].[_AccumRgT20782] 
    UPDATE STATISTICS [dbo].[_AccumRgTn21088] 
    GO
    UPDATE STATISTICS [dbo].[_AccumRgT21369]
    UPDATE STATISTICS [dbo].[_AccumRg21358] 
    UPDATE STATISTICS [dbo].[_AccumRgT21356] 
    UPDATE STATISTICS [dbo].[_AccumRg21346] 
    UPDATE STATISTICS [dbo].[_AccumRgT21405] 
    UPDATE STATISTICS [dbo].[_AccumRg21396] 
    UPDATE STATISTICS [dbo].[_AccumRgT21417] 
    UPDATE STATISTICS [dbo].[_AccumRg21407]
    GO

    и вот что после выдают логи (кусок)

    Report was generated on "SRV".
    Maintenance Plan: Upd Stat Sklad Upp
    Duration: 00:00:00
    Status: Warning: One or more tasks failed..
    Details:
    Execute T-SQL Statement Task (SRV)
    Execute TSQL on sa
    Execution time out: 0
    Task start: 2012-10-03T16:00:01.
    Task end: 2012-10-03T16:00:01.
    Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[_AccumRg20188] 
    UPDATE STATISTICS [dbo].[_AccumRg20674] 
    UPDATE STATISTICS [dbo].[_AccumRg20697] 
    UPDATE STATISTICS [dbo].[_AccumRg20752] 
    UPDATE STATISTICS [dbo].[_AccumRg21067] 
    UPDATE STATISTICS [dbo].[_AccumRgT20214] 
    UPDATE STATISTICS [dbo].[_AccumRgT20695] 
    UPDATE STATISTICS [dbo].[_AccumRgT20723] 
    UPDATE STATISTICS [dbo].[_AccumRgT20782] 
    UPDATE STATISTICS [dbo].[_AccumRgTn21088] 
    " failed with the following error: "Table '_AccumRg20188' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    
    Execute T-SQL Statement Task (SRV)
    Execute TSQL on sa
    Execution time out: 0
    Task start: 2012-10-03T16:00:01.
    Task end: 2012-10-03T16:00:01.
    Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[_AccumRgT21369]
    UPDATE STATISTICS [dbo].[_AccumRg21358] 
    UPDATE STATISTICS [dbo].[_AccumRgT21356] 
    UPDATE STATISTICS [dbo].[_AccumRg21346] 
    UPDATE STATISTICS [dbo].[_AccumRgT21405] 
    UPDATE STATISTICS [dbo].[_AccumRg21396] 
    UPDATE STATISTICS [dbo].[_AccumRgT21417] 
    UPDATE STATISTICS [dbo].[_AccumRg21407]
    " failed with the following error: "Table '_AccumRgT21369' does not exist.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Но при этом сами таблицы точно есть, и при отдельном ручном Execute все проходит.

    Пока обновление статистики проходит по ночам в полном объеме без выборки, но требуется делать не менее каждого часа именно эти места. Как это победить, и кто знает нормальный скрипт для данного решения?

    4 октября 2012 г. 5:26

Ответы

  • О, какой феерический набор заблуждений. :)

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

    2. При автообновлении full scan, как правило, не происходит. Потому то и нет при нём диких тормозов, которые вас пугают. 

    3. Делать full scan для обновления статистик не нужно. Эта операция не должна быть частью нормального процесса. Если вам не терпится, делайте её раз в неделю-месяц, допустим, в соответствующие окна обслуживания. Но, поверьте, существенно более сложно организованные и объёмные БД обходятся без этого годами.

    4. Процентов на 90 уверен в том, что ваши проблемы никак не связаны со статистикой. Просто база ваша стала слишком большой для вашего железа. А устройство 1С-а в смысле работы с базой достаточно примитивно, так что поуправлять вам особенно то и нечем.  Подумайте над какой-то архивацией данных или улучшением железа(память и ускорение IO(можете подумать про SSD)).

    Рекомендации 1с-ников достаточно примитивны и рассчитаны на базы небольшого размера, где их слабая применимость скрадывается мощью современного железа. Ирония заключается в том, что это же железо позволяет и вовсе без этих зубодробительных рекомендаций обходиться. А как только вы выходите за эти границы, то сразу же по полной огребаете проблем из-за чудовищной избыточности данных рекомендаций, которые придуманы как максимально универсальные и придуманы людьми, которые не работали с большими базами на сравительно слабом железе.

    Про флаг 2371 забудьте, он не про 2005 sql server.

    PS. Если вы расскажете про то, что вы ночью делаете с индексами, то, наверняка, тоже услышите тут пару-тройку мнений о том, что у вас с этим неправильно.
    • Изменено Roman Sergeev 4 октября 2012 г. 8:17
    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 9:04
    4 октября 2012 г. 8:14
  • Угу.

    1) Изучите показатель PLE(page life expectancy) вашего сервера. Если он покажет, что памяти не хватает, то добавьте её, если есть возможность. Плохо, конечно, что все FB-DIMM's вы уже заняли - дороже обойдётся.

    2) Я правильно понимаю, что разделы G и H у вас лежат на одном массиве? Ещё и RAID5 снизу, полагаю? Поздравляю, это худшее, что вы могли сделать со своим сервером. Вам необходимо, как минимум, разнести ваши базы по нескольким физическим дискам. Есть стандартная рекомендация - разносить файлы данных и журналов транзакций по разным дискам. При высокой загрузке ещё и каждую базу желательно класть на свой персональный диск или даже несколько.

    Понятно, что вы сейчас можете упереться в физические и экономические ограничения(ограниченность вместимости корзины сервера, невозможность апгрейда за разумные с точки зрения вашей организации деньги). Но в вашей ситуации другого выхода нет. Всё остальное, включая неоптимальность ваших maintenance plans, вторично.

    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 11:43
    4 октября 2012 г. 10:47
    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 12:16
    4 октября 2012 г. 11:53
  • Профайлер запустите и смотрите, что происходит в процессе "проводки 2 доков одновременно". Обратите внимание сперва на самые медленные запросы.   
    • Помечено в качестве ответа Z-Admin 5 октября 2012 г. 8:19
    5 октября 2012 г. 7:13

Все ответы

  • а зачем насиловать сервер обновлением статистики каждый час...причём для объектов, которые в этом не нуждаются? может быть проще установить автоматическое обновление + флаг трассировки 2371!?

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


    http://www.t-sql.ru

    4 октября 2012 г. 5:52
    Отвечающий
  • Да вот так 1с ники советуют, а автоматическое обновление может затормозитьь ночные процессы бекапа и реструктуризации индексов, они и так 1,5 часа выполняются, базы все круглосуточно работают, плюс ночью идет глобальный обмен с другой базой по складу.

    Я вообще опасаюсь автообновления статистики, сервак не резиновый, а нагрузки в разное время прыгают и за потолок очереди чтения диска, рейд на пределе возможного. Так хоть я знаю и пользователи, что в 00 минут каждого часа например может подтормаживать на пару минут, но не в любое время, как при автообдейте...

    и что за флаг 2371?

    сейчас сделали просто визардом обновление по некоторым из этих таблиц, отметив их галочками, скрипт получается такой

    use [upp]
    GO
    UPDATE STATISTICS [dbo].[_AccumRg20674] 
    WITH FULLSCAN
    GO
    use [upp]
    GO
    UPDATE STATISTICS [dbo].[_AccumRg20697] 
    WITH FULLSCAN
    GO
    use [upp]
    GO
    UPDATE STATISTICS [dbo].[_AccumRgT20695] 
    WITH FULLSCAN
    GO
    use [upp]
    GO
    UPDATE STATISTICS [dbo].[_AccumRgT20723] 
    WITH FULLSCAN

    Все пока проходит, только время много делает по 2-3 минуты на кажд 10 минут (переставил на 30мин)

    но базе при этом лучше не стало((...

    При этом и раньше я этот же скрипт уже использовал с Fullscan, но как выполнение отдельного скрипта, чтобы просто вписывать таблицы руками, а не копаться в выборе в окне визарда, но шедулер натыкался на блокировки постоянно и бросал выполнение.



    • Изменено Z-Admin 4 октября 2012 г. 6:14
    4 октября 2012 г. 6:08
  • О, какой феерический набор заблуждений. :)

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

    2. При автообновлении full scan, как правило, не происходит. Потому то и нет при нём диких тормозов, которые вас пугают. 

    3. Делать full scan для обновления статистик не нужно. Эта операция не должна быть частью нормального процесса. Если вам не терпится, делайте её раз в неделю-месяц, допустим, в соответствующие окна обслуживания. Но, поверьте, существенно более сложно организованные и объёмные БД обходятся без этого годами.

    4. Процентов на 90 уверен в том, что ваши проблемы никак не связаны со статистикой. Просто база ваша стала слишком большой для вашего железа. А устройство 1С-а в смысле работы с базой достаточно примитивно, так что поуправлять вам особенно то и нечем.  Подумайте над какой-то архивацией данных или улучшением железа(память и ускорение IO(можете подумать про SSD)).

    Рекомендации 1с-ников достаточно примитивны и рассчитаны на базы небольшого размера, где их слабая применимость скрадывается мощью современного железа. Ирония заключается в том, что это же железо позволяет и вовсе без этих зубодробительных рекомендаций обходиться. А как только вы выходите за эти границы, то сразу же по полной огребаете проблем из-за чудовищной избыточности данных рекомендаций, которые придуманы как максимально универсальные и придуманы людьми, которые не работали с большими базами на сравительно слабом железе.

    Про флаг 2371 забудьте, он не про 2005 sql server.

    PS. Если вы расскажете про то, что вы ночью делаете с индексами, то, наверняка, тоже услышите тут пару-тройку мнений о том, что у вас с этим неправильно.
    • Изменено Roman Sergeev 4 октября 2012 г. 8:17
    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 9:04
    4 октября 2012 г. 8:14
  • PS. Если вы расскажете про то, что вы ночью делаете с индексами, то, наверняка, тоже услышите тут пару-тройку мнений о том, что у вас с этим неправильно.


    А там все стандартно, - Maintenance Plan Wizard - Reorganize Index, Update Statistics,Execute T-SQL Statement Task (DBCC FREEPROCCACHE), Clean Up History, Maintenance Cleanup Task (delete old backups), Back Up Database (Full)/ все сие на 2 базы (кроме бекапа, он еще и системные тянет)

    AvtoAlfa.mdf 12 326 010 880 upp.mdf 37 497 733 120 (срезали прошлый год инструментами 1С, не совсем удачно, от чего размер и не сильно убавился)

    на все это 1,5 часа уходит, плюс часто по ночам идет переброска данных из Альфы в УПП по складу.

    железо:

    Компьютер:
          Тип компьютера                                    ACPI мультипроцессорный x64 компьютер
          Операционная система                              Microsoft Windows Server 2003 R2, Standard Edition
          Пакет обновления ОС                               Service Pack 2
    
        Системная плата:
          Тип ЦП                                            2x QuadCore Intel Xeon E5335, 2000 MHz (6 x 333)
          Системная плата                                   Intel Alcolu S5000PAL  (1 Adaptive Slot, 1 PCI-E Riser Slot, 8 FB-DIMM, Video, Dual Gigabit LAN)
          Чипсет системной платы                            Intel Blackford 5000P
          Системная память                                  32761 Мб  (DDR2-667 Fully Buffered ECC DDR2 SDRAM)
          Channel0-DIMM1: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (3-3-3-9 @ 200 МГц)  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel0-DIMM2: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel1-DIMM1: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (3-3-3-9 @ 200 МГц)  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel1-DIMM2: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel2-DIMM1: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (3-3-3-9 @ 200 МГц)  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel2-DIMM2: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel3-DIMM1: Kingston KINGSTON                 4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (3-3-3-9 @ 200 МГц)  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          Channel3-DIMM2: Hynix HYMP151F72CP4N3-Y5          4 Гб DDR2-667 ECC DDR2 SDRAM FB-DIMM  (4-4-4-12 @ 266 МГц)  (5-5-5-15 @ 333 МГц)
          
        Хранение данных:
          Контроллер IDE                                    Intel(R) 631xESB/6321ESB Ultra ATA Storage Controller - 269E
          Контроллер хранения данных                        Adaptec RAID 3805
          Дисковый накопитель                               Adaptec Array SCSI Disk Device  (68 Гб) system
          Дисковый накопитель                               Adaptec Array SCSI Disk Device  (68 Гб) shadow
          Дисковый накопитель                               Adaptec Array SCSI Disk Device  (558 Гб) DataBase
          Оптический накопитель                             Slimtype DVD A  DS8AZP
          SMART-статус жёстких дисков                       Неизвестно
    
        Разделы:
          C: (NTFS)                                         34545 Мб (25454 Мб свободно)Система
          E: (NTFS)                                         35338 Мб (33666 Мб свободно)
          F: (NTFS)                                         69884 Мб (45194 Мб свободно)Теневые копии
          G: (NTFS)                                         145.0 Гб (30.1 Гб свободно) SQL базы
          H: (NTFS)                                         413.6 Гб (24.0 Гб свободно) бекапы+ доп базы
          Общий объём                                       695.1 Гб (156.0 Гб свободно)

    а всего на серваке все базы более 100Г, но постоянная активность только у этих 2х

    УПП 2 года от роду, забивалась почти с нуля. на ней ок 100 юзверей плюс на Альфе около 50ти




    • Изменено Z-Admin 4 октября 2012 г. 10:28
    4 октября 2012 г. 9:24
  • Угу.

    1) Изучите показатель PLE(page life expectancy) вашего сервера. Если он покажет, что памяти не хватает, то добавьте её, если есть возможность. Плохо, конечно, что все FB-DIMM's вы уже заняли - дороже обойдётся.

    2) Я правильно понимаю, что разделы G и H у вас лежат на одном массиве? Ещё и RAID5 снизу, полагаю? Поздравляю, это худшее, что вы могли сделать со своим сервером. Вам необходимо, как минимум, разнести ваши базы по нескольким физическим дискам. Есть стандартная рекомендация - разносить файлы данных и журналов транзакций по разным дискам. При высокой загрузке ещё и каждую базу желательно класть на свой персональный диск или даже несколько.

    Понятно, что вы сейчас можете упереться в физические и экономические ограничения(ограниченность вместимости корзины сервера, невозможность апгрейда за разумные с точки зрения вашей организации деньги). Но в вашей ситуации другого выхода нет. Всё остальное, включая неоптимальность ваших maintenance plans, вторично.

    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 11:43
    4 октября 2012 г. 10:47
  • Угу.

    1) Изучите показатель PLE(page life expectancy) вашего сервера. Если он покажет, что памяти не хватает, то добавьте её, если есть возможность. Плохо, конечно, что все FB-DIMM's вы уже заняли - дороже обойдётся.

    2) Я правильно понимаю, что разделы G и H у вас лежат на одном массиве? Ещё и RAID5 снизу, полагаю?

    Физически  в отчетах производительности память не жалуется, там очереди маленькие, а вот рейд упирается в потолок при проводке доков или даже выписке по ЗП, RAID 10 на 4 дисках, и 2 отдельно под систему и тень. корзина больше не вмещает, да и сам рейд за 6Гб/с не прыгнет если расширить, ведь канал-то единый на мать...
    4 октября 2012 г. 10:59
  • Вы правда думаете, что вам светит упереться на RAID10 из четырёх дисков в 6 Гб/с? У вас из SSD RAID? Если нет, то при OLTP нагрузке, а 1С даёт именно её, вы упираетесь в ограниченность возможностей жёсткого диска по произвольному доступу. Вы смотрели реальный трансфер с диска в процессе проведения документов? А длину очереди?
    4 октября 2012 г. 11:11
  • 1) как посмотреть трансфер?

    2) длинна очереди бывает и в 12 страниц но чаще 1,5-2 на пиках

    3) тогда при включенном автоабдейте статистики ночные Update Statistics и DBCC FREEPROCCACHE отменить из Maintenance Plan?
    • Изменено Z-Admin 4 октября 2012 г. 11:53
    4 октября 2012 г. 11:32
    • Помечено в качестве ответа Z-Admin 4 октября 2012 г. 12:16
    4 октября 2012 г. 11:53
  • у меня есть и то что там не описано,

    например раздел MS SQL где я выбрал на угад неск параметров и, напирмер

    Batch Requests/sec max - 501 среднее 76, упираясь почти постоянно в потолок

    Transaction max -60, ср -20

    % использования физ диска  max -374 средний -10

    ср длинна очереди диска max -25, ср -0,7

    еще в потолке глухо Sql Compilations - 481 max, ср 160...

    вот картинка Perfomanse

    SQL Monitor system

    А есть скрипт SQl определения в онлайне проблемного места ??? в памяти, в таблице..., доки больше 2х одновременно не проводятся даже, а в копии этой базы без пользователей на этом же сервере все проводится...








    • Изменено Z-Admin 4 октября 2012 г. 12:50
    4 октября 2012 г. 12:25
  •  У вас из SSD RAID?

    У нас SSD умирают за пол года на автобусах только на чтение кино, ставить их на сервер, это на пару дней, или неделю... а спец SSD стоют сами как сервер. как все таки найти узкое место? понятно что на объединенной RAID системе под все сразу далеко не уедешь, но проводка 2 доков одновременно не должна вешать остальных, еще пол года назад такого не было, началось с весны, примерно как  обновили версию 1C ядра, но сейчас вообще тормоза, при этом все дургие базы то работают на этом же сервере...

    можно ли через СКУль найти косяк в таблицах и статистике базы 1С (дубликаты, левые запросы на непонятно чего)?

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


    • Изменено Z-Admin 5 октября 2012 г. 7:05
    5 октября 2012 г. 6:44
  • Профайлер запустите и смотрите, что происходит в процессе "проводки 2 доков одновременно". Обратите внимание сперва на самые медленные запросы.   
    • Помечено в качестве ответа Z-Admin 5 октября 2012 г. 8:19
    5 октября 2012 г. 7:13
  • Профайлер запустите и смотрите,   

    попробую 

    В SQL есть разные отчеты, например Index Physical Statistics, но он выдает слишком много инфы.

    удалось сделать его лишь для старой базы без среза, но в ней на глаз не мало таблиц рекомендованных к rebuild, и в приложении к ним есть те, чья фрагментация более 50 и даже 75 %...
    А есть скрипт с выборкой только таблиц рекомендованных к rebuild, чтобы потом нормально вытащить их для скрипта восстановления? и выборка по фрагментации с сортировкой по возрастанию свыше там 25%? и желательно для таблиц в которых более 10000 строк, а не мелочь всякую... в родном отчете объем не указан(((
    а то в этом xls отчете черт ногу сломит, на 40000 строк...

    5 октября 2012 г. 8:18