CityBus: ускоряем работу часть 1/2 - CityBus Работа с программой - М2М-CityBus - Статьи - Эффективная навигация с М2М
19.01.2017
Эффективная Навигация М2М
Меню сайта
Категории раздела
CityBus Работа с программой [3]
Форма входа
Вход через Google
Вход через Вконтакте
Вход через Facebook
Партнеры
Реклама

CityBus: ускоряем работу часть 1/2
Во время написания статьи Management Studio был заменен с Русской версии 2008, на Английскую 2012, и снова на Русскую 2012. Будьте внимательны!
Часть 1 из 2.

Рано или поздно пользователи CityBus сталкиваются с проблемой производительности сервера SQL, и как следствие - работы программы.
Обычно производительность сервера падает по мере накопления данных в программе, и встает вопрос в выполнении периодических очисток базы данных, дефрагментации индексов, сокращения размера файлов базы данных.

Данная статья рассмотрит как раз эти вопросы.

Для работы вам потребуется знать как подключиться к серверу SQL, где расположены базы данных CityBus, вероятнее всего под пользователем с административными правами (например, пользователь "sa"), потребуется установленное средство управления Management Studio 2008 или новее (Версия 2008 - меньший размер, быстрее ставится  http://www.microsoft.com/ru-ru/download/details.aspx?id=7593  , Версия 2012 - ставится дольше,  http://www.microsoft.com/ru-ru/download/details.aspx?id=29062 ), свежая голова. База данных CityBus обычно называется BN_PATP - именно такое название использовано во всех скриптах.

При работе с БД в качестве мер по ускорению работы с ней обычно используют несколько:
1. Очистку данных в таблицах - чем меньше данных у вас тем быстрее работает SQL.
2. Дефрагментацию индексов базы данных - чем более упорядочены индексы тем быстрее работают запросы на поиск информации
3. Сжатие файлов базы данных - тут все достаточно очевидно. Замечу, что физический рост файлов достаточно не значительно сказывается на быстродействии SQL.
4. Дефрагментацию дисков и файлов на нем - чем меньше приходится делать операций чтения БД с диска тем быстрее идет работа.

Статья не рассматривает возможности изменения "железной" конфигурации сервера (наращивание памяти, замена дисков на более скоростные, использование RAID и так далее) - т.е. "пытаемся выжать" все что можно из того, что уже есть.

1. Выясняем объемы таблиц.

Скорость работы зависит от размера таблиц, посему задача №1 узнать размер таблиц. Для замера размера таблиц и их индексов можно использовать скрипт приведенный по данной ссылке (измените "use BN" на "use BN_PATP").
На выходе таблица, отсортированная по уменьшению размера (больший размер в первых строках) таблицы данных.

Вероятнее всего это будут три таблицы:
  • dbo.Sys_Dev_Archivedata - история перемещения транспорта (координаты)
  • dbo.PATP_Events - регистрация событий типа тревожной кнопки, непроходов контрольных точек и так далее
  • dbo.PATP_OrderControl - данные из планов работы ТС на день.
Дальнейшие рассказы будут касаться именно данных трех таблиц.

Так же размеры можно посмотреть через Management Studio - щелчком правой кнопки мышки на нужно таблице, Свойства (Propererties), закладка Хранилище (Storage), размеры данных, индекса, число строк - указаны справа.


2. Очищаем данные.

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


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


3. Дефрагментация индексов


Наиболее быстро набирающий фрагментацию будет индекс таблицы dbo.Sys_Dev_Archivedata - там индекс один
Для выяснения текущего состояния индекса можно использовать скрипт по ссылке (с заменой названий таблиц, индексов на нужные).

Или возможность Managements Studio, щелчок правой кнопкой на индексе, Свойства, закладка Фрагментация - посмотреть процент фрагементации.


Чем меньше указанный процент, тем лучше.
Для дефрагментации можно использовать два действия:
1. Реорганизация индексов - при этом работа с индексом продолжается, но несколько (не значительно) падает производительность системы. Процесс может быть прерван без потери уже произведенных действий.
2. Перестроение индексов - индекс будет не доступен (зависит от версии SQL - в более старших индекс версиях доступ в работе) - индекс фактически создается заново, старый удаляется. При прерывании процесса результат уже произведенных действий будет утерян.
Для выполнения действий через Management Studio нужно найти в списке таблиц (Tables) нужную, раскрыть ее, найти пункт "Индексы" (Indexes), раскрыть его, щелкнуть правой кнопкой на нужном индексе, выбрать требуемое действие.

Далее появится окно, где будет указан процент фрагментации, и кнопка ОК для выполнения требуемого действия. Внимание! На открытие окна может потребоваться длительное время, так как в этот момент система подсчитывает процент фрагментации!


Для индекса таблицы Sys_Dev_ArchiveData я использую реорганизацию, при этом, даже эта операция требует много часов (зависит от размеров конечно же таблиц, и использования сервера).
Потому, дабы не остановить работу диспетчерской службы действия по дефрагментации выполняются ночью (нет нагрузки на систему), с прерыванием работы задания в определенное время.

Для этого скрипт реорганизации запускается через задания (jobs). Для создания задания можно использовать Management Studio

Для этого в Management Studio (скрипт задания приводится ниже, картинки кликабельны):

Раскрыть "Агент SQL Server"
Щелчок правой кнопкой на Задания (Jobs)
Выбрать Создать задание... (Create Jobs)

Указать название (Name) задания: 
01 CB - Реорганизация Sys_dev_Archivedata

Открыть "Шаги" (Steps), нажать "Создать" (Create)
Указать название (произвольное), тип запуска
Указать в поле скрипта 


USE bn_patp;
/****** Сценарий для команды SelectTopNRows среды SSMS  ******/

ALTER INDEX IX_SYS_DEV_ArchiveData ON bn_patp.dbo.SYS_DEV_ArchiveData
REORGANIZE ; 

GO

Нажать ОК

Выбрать закладку "Расписания" (Shedules)
Создать (Create) или выбрать (Pick) расписание, с параметрами как на рисунке:

Повторяющееся задание (Recurring), Включено (стоит галка)
Запуск - Ежедневно (Daily), в 23:50

Нажать ОК

Нажать ОК в окне "Свойства задания"

Так же для создания задания можно использовать данный скрипт, выполнив его в Management Studio.

Так как задание может выполняться очень долго, и все это время работа с программой будет крайне затруднена, то следует прервать задание в определенное время (большее количество выходов на линию у меня после 06:00, потому многие скрипты прерываются именно в 06:00). Для этого опять же выполняем скрипт посредством заданий.

Создайте еще одно задание, как описано выше, но выберите:
Название: 01_01 CB прерывание задания 01 (в данном случае не важно)
В качестве шага запустить скрипт SQL:

USE msdb ;
GO

EXEC dbo.sp_stop_job
    N'01 CB - Реорганизация Sys_dev_Archivedata' ; -- тут указать имя задания
GO

Запускать каждый день, в 06:00 (добавьте или выберите соответствующее расписание).
Так же для создания задания можно использовать данный скрипт, выполнив его в Management Studio.

Для меньших размером индексов можно применить и перестроение. Например для таблицы PATP_OrderControl.
Данная таблица требует более редких воздействий, потому действия можно выполнять, например, раз в неделю, по субботам, в 00:00. Однако предусмотрим задание, которое будет принудительно прерывать работу, как и в прошлом случае.

Имя задания: 02 CB - Реорганизация PAPT_OrderControl
Так как скрипт прекращающий его работу ориентируется на имя задания - то нужно его указать точно.

Шаги: 1 - запуск скрипта SQL с кодом
use bn_patp
alter index all ON bn_patp.dbo.PATP_OrderControl REBUILD ;
go
Периодичность: Раз в неделю, по субботам, в 00:00
Скрипт создания задания тут.


Имя задания: 02_01 CB Прерывание задания 02
Шаги: 1 - запуск скрипта SQL с кодом
USE msdb ;
GO
EXEC dbo.sp_stop_job
    N'02 CB - Реорганизация PAPT_OrderControl' ; -- тут указать имя задания
GO
Периодичность: раз в неделю, по субботам, в 06:00
Скрипт создания задания тут.


Тоже самое касается и таблицы PATP_Events, дабы не создавать нагрузку разведем задание с предыдущими - запуск раз в неделю, по воскресеньям, в 00:00. Так же предусмотрим задание прерывания.

Имя задания: 03 CB - Реорганизация PAPT_Events
Так как скрипт прекращающий его работу ориентируется на имя задания - то нужно его указать точно.

Шаги: 1 - запуск скрипта SQL с кодом
use bn_patp
alter index all ON bn_patp.dbo.PATP_Events REBUILD ;
go
Периодичность: Раз в неделю, по воскресеньям, в 00:00
Скрипт создания задания тут.


Имя задания: 03_01 CB Прерывание задания 03
Шаги: 1 - запуск скрипта SQL с кодом
USE msdb ;
GO
EXEC dbo.sp_stop_job
    N'03 CB - Реорганизация PAPT_Events' ; -- тут указать имя задания
GO
Периодичность: раз в неделю, по воскресеньям, в 06:00
Скрипт создания задания тут.


В результате у вас должно получиться примерно вот так



4. Сокращение размера файлов БД после очистки.

После удаления данных из таблиц так же не плохо уменьшить размер файлов, занимаемых базой данных. Однако, сокращение размеров файлов не очень хорошо сказывается на производительности (за счет фрагментации данных), потому данным пунктом, если у вас нет острой необходимости пользоваться не стоит.
Любая база данных состоит как минимум из двух файлов:
  • Файл базы данных
  • Файл журнала транзакций
Для уменьшения размера базы данных следует использовать ее сжатие.
Сжатие можно выполнить посредством Management Studio


Щелчок правой кнопкой на базе данных, Задачи, Сжать, выбрать нужный пункт.
База данных - сжатие сразу всех файлов.
Файлы - сжатие конкретного файла (выбрать файл базы данных).
Так же можно использовать данный скрипт для сжатия файла данных (до минимального размера используемых данных):
USE [BN_PATP]
GO
DBCC SHRINKFILE (N'BN_Data' , 0, TRUNCATEONLY)
GO

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

5. Дефрагментация файлов на диске.

Дефрагментация файлов производится средствами windows или сторонними утилитами. Для планирование времени дефрагментации следует использовать Планировщик задач. Возможно следует ограничить время работы задания до нужного вам времени.

Скрипт дефрагментации  диска C: (cmd файл):
defrag.exe c:

Замените название вашего диска на нужный.

Замечу, что возможно потребуется остановка служб SQL, это можно сделать следующим скриптом (cmd):
net stop "MSSQLSERVER"
ну а запустить, соотвественно 
net start "MSSQLSERVER"





Источник: CityBus, SQL, ускорение, очистка, индексы, дефрагментация, сжатие,база данных
Похожие материалы
Категория: CityBus Работа с программой | Добавил: logoff (24.09.2012) | Автор: Бондарь Михаил aka LogOff
Просмотров: 1467 | Теги: очистка, SqL, CityBus, Ускорение, Индексы | Рейтинг: 5.0/2
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Поиск
TOP 10 Популярное
1 Оборудование> Гранит-Навигатор 4.14
2 Оборудование> Абонентский терминал M2M Cyber GLX
3 Оборудование> Гранит-Навигатор 4.14: Настройка терминала
4 Прочее> "Красные" против "Синих" - статистка космических запусков России (СССР) и США
5 CyberFleet, CrossPoint> КиберФлит: Учет топлива при помощи датчиков
6 CyberFleet, CrossPoint> CyberFleet: Переустановка CyberFleet
7 Оборудование> M2M Cyber GLX: отправка команд
8 Прочее> SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 1/2 (теория, inner join)
9 CyberFleet, CrossPoint> КиберФлит: Причины расхождения пробега по данным одометра и карте
10 CyberFleet, CrossPoint> КиберФлит: Подключаем карту Google
Наш опрос
Тахографы следует?
Всего ответов: 21
LogOff © 2017
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС