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

Откуда: Москва
Сообщений: 888
Добрый день:)

Ситуация следующая.
Есть SQLServer 2005: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

На нём база архив. В ней имеется таблица:

CREATE TABLE [dbo].[t_Pricelists](
[PLDate] [datetime] NOT NULL,
[PLType] [char](18) NOT NULL,
[Article] [char](25) NOT NULL,
[BPrice] [money] NOT NULL,
CONSTRAINT [PK_Pricelists] PRIMARY KEY CLUSTERED
(
[PLDate] ASC,
[PLType] ASC,
[Article] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Как видим, в данной таблице PK установлен на 3х полях. Соответственно размер индекса запредельный (в таблице около 250.000.000 записей) - 74 213,234 MB.
Также есть некластерные индексы (3 штуки) по полям PLDate, PLType, Article.

Таблица архивная, но к ней по большей части летят запросы с SELECT-ами. INSERT редко и ночью, т.е. скорость последнего непринципиальна (по крайней мере пока).

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

Вопрос к Гуру форума: можно ли оптимизировать индексную структуру? Какие ещё данные необходимо предоставить для того, чтобы вы могли мне что-либо посоветовать? Заранее благодарен
6 май 13, 12:37    [14262174]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Сергей Викт.
...Какие ещё данные необходимо предоставить для того, чтобы вы могли мне что-либо посоветовать?
Примеры запросов, например.

Сообщение было отредактировано: 6 май 13, 12:41
6 май 13, 12:41    [14262192]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Какие ещё данные необходимо предоставить для того, чтобы вы могли мне что-либо посоветовать? Заранее благодарен

Примеры конкретных "по большей части летят запросы с SELECT-ами"
6 май 13, 12:41    [14262196]     Ответить | Цитировать Сообщить модератору
 Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
SELECT [PLDate]
,[PLType]
,[Article]
,[BPrice]
FROM [Archive].[dbo].[t_Pricelists]
WHERE ([PLDate] BETWEEN '2012-12-01 00:00:00.000' AND GETDATE())
AND (PLType like '3M Korablik' OR PLType like 'Base Price')

Запросы в основном идут для формирования отчетов по прайсам и т.д.
Посмотрел даже этот запрос в плане выполнения: 98% веса - поиск в кластерном индексе и 2% в PLType.

Заранее извиняюсь за возможно глупые вопросы, просто с индексами вплотную сел работать впервые, но, как законопослушный человек литературу почитал:)
6 май 13, 12:47    [14262240]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Посмотрел даже этот запрос в плане выполнения: 98% веса - поиск в кластерном индексе и 2% в PLType.

Что может быть лучше index seek по кластерному индексу ?
6 май 13, 12:49    [14262254]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32178
Сергей Викт.
Как видим, в данной таблице PK установлен на 3х полях. Соответственно размер индекса запредельный (в таблице около 250.000.000 записей) - 74 213,234 MB.
Это нормально, кластерный индекс - это и есть таблица.

Сергей Викт.
Таблица архивная, но к ней по большей части летят запросы с SELECT-ами. INSERT редко и ночью, т.е. скорость последнего непринципиальна (по крайней мере пока).

Как я ни ковырял разными запросами данную табличку, в плане запроса всегда используется только кластерный индекс, либо я чего-то не так делаю.
Нужно смотреть конкретные тормозящие запросы, их планы и стоимость (цпу, чтения). Ну и покажите остальные индексы на таблицу.
6 май 13, 12:50    [14262257]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32178
Сергей Викт.
Посмотрел даже этот запрос в плане выполнения: 98% веса - поиск в кластерном индексе и 2% в PLType.
Поиск или скан?

Вообще для такого запроса идеально будет сделать индекс:
[PLType] , [PLDate] INCLUDE [Article], [BPrice]
И даже переделать запрос на 2 с UNION ALL

Но его размер будет равен размеру таблицы (впрочем, если для вас скорость инсёртов неважна, то не имеет значения)
6 май 13, 12:54    [14262280]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Благодарю вас за внимание, оказанной данной теме.

Glory
Что может быть лучше index seek по кластерному индексу ?


Согласен с вами, что лучше вряд ли что-либо есть. Но у меня есть задача высвободить место на харде:) Поэтому и спросил, возможно ли без особой потери во времени обработки запросов что-либо изменить. Думал про фейковый ключ (Identity column добавить например), а далее сделать некластерный индекс по PLDate и добавить в него 2 поля PLType и Article.

alexeyvg
Нужно смотреть конкретные тормозящие запросы, их планы и стоимость (цпу, чтения). Ну и покажите остальные индексы на таблицу.


Понял, пойду раскуривать профайлер, попрошу народ заливать прайсы и формировать отчеты, что б была нагрузка, буду изучать:)

CREATE NONCLUSTERED INDEX [IX_Pricelists_PLDate] ON [dbo].[t_Pricelists]
(
[PLDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Relation_7_FK] ON [dbo].[t_Pricelists]
(
[Article] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Relation_9_FK] ON [dbo].[t_Pricelists]
(
[PLType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Просто важный момент освободить место на жестких дисках:(

P.S. Просто t_Pricelist это только одна таблица, а таких в архивной базе достаточно много. Вот пытаюсь разобраться и что-либо придумать :)
6 май 13, 12:57    [14262310]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Glory
Что может быть лучше index seek по кластерному индексу ?


Согласен с вами, что лучше вряд ли что-либо есть. Но у меня есть задача высвободить место на харде:)

если вы про кластерный индекс, то он и есть таблица - что там освобождать
6 май 13, 12:59    [14262316]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
alexeyvg
Вообще для такого запроса идеально будет сделать индекс:
[PLType] , [PLDate] INCLUDE [Article], [BPrice]
И даже переделать запрос на 2 с UNION ALL

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


Скокрость инсертов действительно не имеет значения, т.к. прайсы грузят централизованно и ночью, за 4-6 часов всё успевает отработать.

alexeyvg, т.е если создать такой индекс, как вы описали и в запросе указать WITH INDEX, должно проскакивать быстрее?

Поясню, по полю Article условие при запросе к таблице ставится редко.
6 май 13, 13:01    [14262329]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
если вы про кластерный индекс, то он и есть таблица - что там освобождать


Видимо я не правильно понял сам принцип.
Индекс "весит" 75 Гб. Он по 3м полям первичного ключа (PLDate,PLType,Article).
Я имею ввиду, что, грубо говоря, если добавить какое либо поле типа bigint например и сделать его PK, то размер физический размер индекса не изменится?:(
6 май 13, 13:04    [14262339]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Индекс "весит" 75 Гб. Он по 3м полям первичного ключа (PLDate,PLType,Article).

Это не просто индекс. Это кластерный индекс.
6 май 13, 13:06    [14262351]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
Это не просто индекс. Это кластерный индекс.

Это так. И он содержит не только указатели, а и сами данные:( Сижу перечитываю теорию)
6 май 13, 13:08    [14262371]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Glory
Это не просто индекс. Это кластерный индекс.

Это так. И он содержит не только указатели, а и сами данные:( Сижу перечитываю теорию)

Тогда как изменение таблицы не поменяет размер кластерного индекса ?
6 май 13, 13:10    [14262390]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
Тогда как изменение таблицы не поменяет размер кластерного индекса ?


:( Я вас понял... Буду пробовать что-либо придумать. О результатах отпишусь, если не закроют тему:)
6 май 13, 13:15    [14262430]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
Добавить поле:
ID int identity (1, 1) not null

Перенести на него кластерный индекс.

И только потом подумать над тем, какие некластерные лучше создать для генерации отчетов.
6 май 13, 13:15    [14262432]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ROLpogo
Добавить поле:
ID int identity (1, 1) not null

Перенести на него кластерный индекс.

И только потом подумать над тем, какие некластерные лучше создать для генерации отчетов.
Не порите чушь. Для отчетов кластерный индекс по identity нафиг не нужен. И особенно не порите чуть про то, что сначала надо что-то делать, а потом уже думать.
6 май 13, 13:18    [14262455]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Glory
Member

Откуда:
Сообщений: 104751
Сергей Викт.
Glory
Тогда как изменение таблицы не поменяет размер кластерного индекса ?


:( Я вас понял... Буду пробовать что-либо придумать. О результатах отпишусь, если не закроют тему:)

- Дополнительные поля добавлять не нужно. Тем более для индекса, который не будет использоваться
- Можно подумать о замене [char] на [varchar]. Но для этого нужно посмотреть заполненность этих полей
- Можно удалить некластерный индекс PLDate
- Можно подумать над удалением некластерного индекса Article.
6 май 13, 13:23    [14262492]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Glory
- Дополнительные поля добавлять не нужно. Тем более для индекса, который не будет использоваться
- Можно подумать о замене [char] на [varchar]. Но для этого нужно посмотреть заполненность этих полей
- Можно удалить некластерный индекс PLDate
- Можно подумать над удалением некластерного индекса Article.


Спасибо огромное за рекомендации. Я как раз собирал статистику использования индекса PLDate и пока ни разу нигде не встретил:)

По 1 пункту полностью согласен, это Мегарешение, которое пришло мне в голову отменяется)
По 2 пункту: буду смотреть.
6 май 13, 13:29    [14262529]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
babaEGA
Member

Откуда: Москва
Сообщений: 289
Сергей Викт.,
А вам места не хватает только на тестовом сервере? На боевом нормально места?
6 май 13, 13:29    [14262531]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
ROLpogo
Member

Откуда: Реутов
Сообщений: 219
Гавриленко Сергей Алексеевич,

Я предложил свой вариант решения задачи "освобождения места на диске". При переносе кластерного на ID мы срезаем порядка 10 Гб с каждого некластерного.
6 май 13, 13:30    [14262541]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
babaEGA
Сергей Викт.,
А вам места не хватает только на тестовом сервере? На боевом нормально места?


На тестовом:) Не бейте сильно, но это тож важная вещь:)
6 май 13, 13:34    [14262564]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Сергей Викт.
.....

CREATE NONCLUSTERED INDEX [IX_Pricelists_PLDate] ON [dbo].[t_Pricelists]
(
[PLDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Relation_7_FK] ON [dbo].[t_Pricelists]
(
[Article] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [Relation_9_FK] ON [dbo].[t_Pricelists]
(
[PLType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


Просто важный момент освободить место на жестких дисках:(

P.S. Просто t_Pricelist это только одна таблица, а таких в архивной базе достаточно много. Вот пытаюсь разобраться и что-либо придумать :)


с учетом вашего кластерного индекса возникает сомнение в необходимости вообще в индексе [IX_Pricelists_PLDate], он нужен будет в случае запроса типа select PLDate from t_Pricelists where PLDate in (.....) т.е. где скан исключительно по этому полю...

А вообще кластерный индекс по рекомендациям должен быть монотонно возрастающим и минимальным по размеру... ибо по сути делая некластерный индекс на эту таблицу, все значения кластерного индекса по умолчанию будут попадать в уровень нелиста. В Вашем случае, я склоняюсь к суррогатному ключу типа ID int IDENTITY(1,1)... У Вас ведь 3 индекса некластерных, по 1му полю для поиска, и плюс будет в дополнении ко всему этому добавляться все поля (3 поля) кластерного индекса - это к теме дискового пространства...
6 май 13, 13:35    [14262571]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Алексей Куренков


В Вашем случае, я склоняюсь к суррогатному ключу типа ID int IDENTITY(1,1)... У Вас ведь 3 индекса некластерных, по 1му полю для поиска, и плюс будет в дополнении ко всему этому добавляться все поля (3 поля) кластерного индекса - это к теме дискового пространства...


Попробую обязательно все варианты. Спасибо!
Пока разбираюсь с тем, что предложил Glory
6 май 13, 13:39    [14262603]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
ROLpogo
Гавриленко Сергей Алексеевич,

Я предложил свой вариант решения задачи "освобождения места на диске". При переносе кластерного на ID мы срезаем порядка 10 Гб с каждого некластерного.
Придется все равно сделать unique constraint по полям нынешнего кластерного индекса, т.е. сделать четверный некластерный. Это раз. Второе: индексы развешены на каждое их полей кластерного индекса, т.е. треть кластерного индекса в них уже присутствует. По месту не будет особой экономии, а вот запрос, который сейчас использует кластерный индекс, может свалится в скан по новому некластерному взамен ключа, бо там нет цены. Это было три.
6 май 13, 13:43    [14262643]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить