Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
Имеется БД, содержащяя таблицы, с именами типа HistoryDDDDMM, содержащие данные АСУТП за месяц:
CREATE TABLE History200408 ( IdTag INTEGER Not Null , Value DECIMAL Null , Quality INTEGER Null , DateTime VARCHAR ( 23 ) Not Null )
Соответсвенно, в каждой таблице накапливается порядка 3 млн. записей за месяц.
На данный момент размер файла БД достиг 3.7 Гб. Данные хранятся в одном файле. Файловая система диска: NTFS.
Проблема: выборка данных стала осуществляться очень медленно. Причем сама выборка осуществляется грамотно: данные выбираются только из нужных таблиц (в случае выборки за несколько месяцев).
Полгода назад размер БД был в два раза меньше, и выборка происходила гораздо быстрее (раза в три-четыре), хотя, естественно, данных в таблицах было так же много (2-3 млн.)
Вопрос: посоветуйте, что можно сделать для убыстрения выборки? Может, в свойствах БД задать не один, а несколько файлов?
8 сен 05, 18:50    [1860803]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Breakneck
Member

Откуда: Kiev
Сообщений: 2454
А что, индексов нет совсем?
Какого рода выборки делаются?
Select * from History200408 ?
Или select value, quality from History200408 where value = 1?
8 сен 05, 18:55    [1860814]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Озушки сколько? Качественные изменения могут произойти если индексы перестанут помещаться в память.
8 сен 05, 19:03    [1860843]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
С индексами все в порядке, они есть, и по нужным полям.
Выборки делаются типа таких:
Проблема именно в том, что полгода назад такая выборка работала быстро с примерно таким же количесвом записей, например, в таблице History200501 (январь), а сегодня выборка осуществляется очень медленно в такой же таблице History200509, и в той же History200501. Т.е. дело не в выборке, а в организации БД. Я грешу на большой размер файла БД. Поможет ли разбиение БД на несколько меньших физических файлов?
8 сен 05, 19:12    [1860873]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

Kamsky wrote:
> С индексами все в порядке, они есть, и по нужным полям.
> Выборки делаются типа таких:
типа каких?
а можно полный DDL и примеры запросов?

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

8 сен 05, 19:21    [1860895]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> Поможет ли разбиение БД на несколько меньших физических файлов?

А это смотреть нада :)
Может и помочь а может только головняка добавить
8 сен 05, 19:22    [1860897]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
Сорри, забыл указать вид выборки:
'select top 1 [DateTime],[Value] ' +
'from [dbo].[' + @TableName + '] ' +
'where [IdTag]=' + Ltrim (Rtrim (Cast (@IdTag as varchar(32)))) + ' and ' +
'[DateTime]<' + @TextStart + ' and [Quality] in (0,1) ' +
'order by [DateTime] desc'
8 сен 05, 19:25    [1860906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Роман Дынник
Member

Откуда:
Сообщений: 3324
ну попробуйте раскидайте HistoryYYYYMM по разным файловым группам.
может помочь, а может и не помочь - от железа зависит.

Кстати индексы у вас в отдельный файл вынесены или вместе с таблицами? - из-за этого терять можете много.
8 сен 05, 19:29    [1860911]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Роман Дынник
Member

Откуда:
Сообщений: 3324
автор
'select top 1 [DateTime],[Value] ' +
'from [dbo].[' + @TableName + '] ' +
'where [IdTag]=' + Ltrim (Rtrim (Cast (@IdTag as varchar(32)))) + ' and ' +
'[DateTime]<' + @TextStart + ' and [Quality] in (0,1) ' +
'order by [DateTime] desc'


а вот такой способ обращения вам следовало бы заменить на использование partitioned view.
8 сен 05, 19:32    [1860916]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
давайте посчитаем.
размер данных одной таблицы будет равен (в МБ)
(4 + 9 + 4 + 23 ) * 10^6 / 1024 / 1024 = 114. Ну сделаем скидку на FILL FACTOR - 120 МБ.
При выполнении запроса SQL Server кеширует в памяти данные. Давайте посчитаем, к какому диапазону времени одновременно выполняются запросы? насколько вероятно, что все диапазоны поместятся в памяти?

Насчет нескольких файлов - при наличии нескольких процессоров может помочь (а может и нет).

Кстати, а почему не используются партицированные представления?Это Ваш случай, мне кажется.

Как ведет себя процессор?
8 сен 05, 19:34    [1860921]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
Роман Дынник
Кстати индексы у вас в отдельный файл вынесены или вместе с таблицами? - из-за этого терять можете много.

А как вынести индексы в отдельный файл?
8 сен 05, 19:37    [1860927]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Crimean
Member

Откуда:
Сообщений: 13148
И кластерный по DateTime просиЦо
Про секционированное представление уже сказали, присоединюсь
8 сен 05, 19:37    [1860928]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Роман Дынник
Member

Откуда:
Сообщений: 3324
Kamsky
А как вынести индексы в отдельный файл?

правильнее будет сказать в отдельную файловую группу (см. опцию ON filegroup).

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) 
[ WITH < index_option > [ ,...n] ] 
[ ON filegroup ]

< index_option > :: = 
    { PAD_INDEX | 
        FILLFACTOR = fillfactor | 
        IGNORE_DUP_KEY | 
        DROP_EXISTING | 
    STATISTICS_NORECOMPUTE | 
    SORT_IN_TEMPDB  
}
8 сен 05, 19:47    [1860948]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
AAron

Давайте посчитаем, к какому диапазону времени одновременно выполняются запросы?

Запрос по умолчанию выполняется за неделю последнюю неделю текущего месяца, т. е. данных , я так понимаю, на 17 Мб.

AAron

Кстати, а почему не используются партицированные представления?Это Ваш случай, мне кажется.
Как ведет себя процессор?

Процессор (он один) практически не загружен во время выполнения запроса.
А про партицированные представления я не знаю. Переделывать механизм выборки - затруднительно. Выборка осуществляется приложением, написанном на Delphi, исходники у меня отсутствуют. Вижу лишь в базе хранимые процедуры, осуществляющие выборки, и предполагаю, что они вызываются клиентским приложением.
8 сен 05, 19:51    [1860954]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Роман Дынник
Member

Откуда:
Сообщений: 3324
Kamsky

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

А почитать? не так все сложно...

'select top 1 [DateTime],[Value] ' +
'from [dbo].[' + @TableName + '] ' +
'where [IdTag]=' + Ltrim (Rtrim (Cast (@IdTag as varchar(32)))) + ' and ' +
'[DateTime]<' + @TextStart + ' and [Quality] in (0,1) ' +
'order by [DateTime] desc'

Это ведь из хранимой процедуры вызывается, следовательно переписать обращение можно (исходников delphi не потребуется), не говоря уж о том что нужно :)
8 сен 05, 20:04    [1860976]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
а счетчики смотрели в момент выполнения запроса? И вообще, что значит медленно? Если процессор не загружен, а работает "медленно", значит надо искать проблемы, например, в дисках.
9 сен 05, 00:58    [1861336]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
AAron
Если процессор не загружен, а работает "медленно", значит надо искать проблемы, например, в дисках.

Проблемы с дисками есть: диск один, обычный IDE, не самый быстрый. Но, к сожалению, заменить "железо" возможности нет, да и на компе постоянно должно работать ПО АСУТП. Поэтому вопрос стоит именно в оптимизаци БД.
9 сен 05, 05:51    [1861425]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
aleks2
Guest
Kamsky
Имеется БД, содержащяя таблицы, с именами типа HistoryDDDDMM, содержащие данные АСУТП за месяц:
CREATE TABLE History200408 ( IdTag INTEGER Not Null , Value DECIMAL Null , Quality INTEGER Null , DateTime VARCHAR ( 23 ) Not Null )
Соответсвенно, в каждой таблице накапливается порядка 3 млн. записей за месяц.
На данный момент размер файла БД достиг 3.7 Гб. Данные хранятся в одном файле. Файловая система диска: NTFS.
Проблема: выборка данных стала осуществляться очень медленно. Причем сама выборка осуществляется грамотно: данные выбираются только из нужных таблиц (в случае выборки за несколько месяцев).
Полгода назад размер БД был в два раза меньше, и выборка происходила гораздо быстрее (раза в три-четыре), хотя, естественно, данных в таблицах было так же много (2-3 млн.)
Вопрос: посоветуйте, что можно сделать для убыстрения выборки? Может, в свойствах БД задать не один, а несколько файлов?


Статистику обновить+ (как уже звучало) кластерный индекс (DateTime DESC, IdTag, Quality)
----------
и будет тебе щастье...
9 сен 05, 06:44    [1861457]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
trayal
Member

Откуда: Пенза
Сообщений: 471
Kamsky
CREATE TABLE History200408 ( IdTag INTEGER Not Null , Value DECIMAL Null , Quality INTEGER Null , DateTime VARCHAR ( 23 ) Not Null )

Почему поле DateTime типа varchar?
Если хранится время, то почему бы не использовать предназначенный для этого тип datetime?
Опять же, экономия места (:)) : 23 vs 8.
9 сен 05, 08:25    [1861507]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
Kamsky
Member

Откуда: Уфа
Сообщений: 18
Менять что-либо в структуре БД затруднительно, так же как и изменять механизм выборки данных - все это дело работает на непрерывно работающем АРМ АСУТП.
А на какие счетчики можно посмотреть, чтобы увидеть причину заторможенности?
10 сен 05, 19:41    [1866154]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
ВМоисеев
Member

Откуда: Редкино
Сообщений: 2420
>Проблемы с дисками есть: диск один, обычный IDE, не самый быстрый ...

Можешь ли добавить дополнительный винчестер, небольшой но скоростной и перенести туда виртуальную память. NT хорошо на это отзывается.

С уважением, Владимир.
10 сен 05, 19:58    [1866164]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
aleks2
Guest
ВМоисеев
>Проблемы с дисками есть: диск один, обычный IDE, не самый быстрый ...

Можешь ли добавить дополнительный винчестер, небольшой но скоростной и перенести туда виртуальную память. NT хорошо на это отзывается.

С уважением, Владимир.


Беда только в том, что MS SQL не очень то пользуется виртуальной памятью...
11 сен 05, 19:05    [1866845]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
aleks2
Guest
Kamsky
Менять что-либо в структуре БД затруднительно, так же как и изменять механизм выборки данных - все это дело работает на непрерывно работающем АРМ АСУТП.
А на какие счетчики можно посмотреть, чтобы увидеть причину заторможенности?


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

Вот рассмотрим для примера:

>>Проблема: выборка данных стала осуществляться очень медленно.

>>1) Причем сама выборка осуществляется грамотно: данные выбираются только из нужных таблиц (в случае выборки за несколько месяцев).

>> 2) Полгода назад размер БД был в два раза меньше, и выборка происходила гораздо быстрее (раза в три-четыре), хотя, естественно, данных в таблицах было так же много (2-3 млн.)

Зависимость скорости выборки от размеров базы (при равных объемах выборки) означает СКАНИРОВАНИЕ таблиц => индексы не используются => как же это согласуется с "выборка осуществляется грамотно"?
-----------
Еще раз повторюсь: правильный кластерный индекс - стоит дюжины самых быстрых дисков.
Ибо ни при каких обстоятельствах поиск перебором (сканирование таблицы) не может быть быстрее метода деления отрезка отрезка пополам (поиск по индексу).
11 сен 05, 19:11    [1866850]     Ответить | Цитировать Сообщить модератору
 Re: Помогите улучшить производительность БД  [new]
msdatabaseru
Guest
1 проверить на отсутствие мертвых блокировок

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

3 имеет смысл просмотреть показания счетчиков производительности машины
наиболее критичные :
процессор, память , диск
реже бывают проблемы с сетью.

если какой то ресурс постоянно перегружен возможно имеет смысл обновить соответствующее оборудование
11 сен 05, 22:45    [1866981]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить