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

Откуда:
Сообщений: 36
Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Проблема: есть задержки с выполнением операций над базой:
1) sys.dm_os_wait_stats показал что на PAGEIOLATCH_EX приходится 30 %
2) статистка FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) ORDER BY page_io_latch_wait_in_ms DESC;
показала что значительные задержки есть по одной таблице - вижу что page_io_latch_wait_in_ms, page_lock_count, page_io_latch_wait_count увеличены по ней
3) посмотрел статистику FROM sys.dm_exec_query_stats qs с планом запроса и вижу что там только
Clustered Index Insert. у таблицы (одна из самых быольших по размеру в базе) 4 некластеризованных индекса. все они в одной файловой группе.
файловая группа на RAID 10, есть отдельный винт под логи и отдельный винт под ось

Вопрос: 1) имеет ли смысл создать дополнительную файловую группу на том же RAID где и основная и вынести туда половину индексов с целью снизить ожидание заблокированных страниц? (Средняя длина очереди диска в среднем 50)
2) Есть какие нить критерии для использования FillFactor? я вижу что идет вставка в индекс, но не понимаю на что именно уходит время: на вставку страниц, перенос их. Есть ли возможность детально просмотреть на что уходит время?
16 авг 12, 10:13    [13018824]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
Приведите структуру таблицы с индексами.
16 авг 12, 10:58    [13019024]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,

я слабо разбираюсь в MSSQL и не совсем вас понимаю. как это сделать? вывести сюда скрипты по созданию таблицы?
сразу обозначу что в качестве приложения 1с, и таблица создана по умолчанию 1с в рамках конфигурации - изменения в регистр 1с не вносились.
16 авг 12, 11:07    [13019076]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
munster,

Да, нужен скрипт создания таблицы с индексами. Хотя бы кластерный.
16 авг 12, 11:11    [13019107]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,

+
CREATE UNIQUE CLUSTERED INDEX [_InfoR17375_ByPeriod_TRN] ON [dbo].[_InfoRg17375]
(
[_Period] ASC,
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [_InfoR17375_ByResource17466_RTRN] ON [dbo].[_InfoRg17375]
(
[_Fld17417RRef] ASC,
[_Period] ASC,
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Объект: Index [_InfoR17375_ByResource17467_RTRN] Дата сценария: 08/16/2012 11:26:22 ******/
CREATE UNIQUE NONCLUSTERED INDEX [_InfoR17375_ByResource17467_RTRN] ON [dbo].[_InfoRg17375]
(
[_Fld17419RRef] ASC,
[_Period] ASC,
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


USE [KA]
GO
/****** Объект: Table [dbo].[_InfoRg17375] Дата сценария: 08/16/2012 11:02:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[_InfoRg17375](
[_Period] [datetime] NOT NULL,
[_RecorderTRef] [binary](4) NOT NULL,
[_RecorderRRef] [binary](16) NOT NULL,
[_LineNo] [numeric](9, 0) NOT NULL,
[_Active] [binary](1) NOT NULL,
[_Fld17376] [numeric](10, 0) NOT NULL,
[_Fld17377RRef] [binary](16) NOT NULL,
[_Fld17378] [binary](1) NOT NULL,
[_Fld17379RRef] [binary](16) NOT NULL,
[_Fld17380RRef] [binary](16) NOT NULL,
[_Fld17381_TYPE] [binary](1) NOT NULL,
[_Fld17381_RTRef] [binary](4) NOT NULL,
[_Fld17381_RRRef] [binary](16) NOT NULL,
[_Fld17382_TYPE] [binary](1) NOT NULL,
[_Fld17382_RTRef] [binary](4) NOT NULL,
[_Fld17382_RRRef] [binary](16) NOT NULL,
[_Fld17383_TYPE] [binary](1) NOT NULL,
[_Fld17383_RTRef] [binary](4) NOT NULL,
[_Fld17383_RRRef] [binary](16) NOT NULL,
[_Fld17384RRef] [binary](16) NOT NULL,
[_Fld17385RRef] [binary](16) NOT NULL,
[_Fld17386RRef] [binary](16) NOT NULL,
[_Fld17387RRef] [binary](16) NOT NULL,
[_Fld17388_TYPE] [binary](1) NOT NULL,
[_Fld17388_RTRef] [binary](4) NOT NULL,
[_Fld17388_RRRef] [binary](16) NOT NULL,
[_Fld17389_TYPE] [binary](1) NOT NULL,
[_Fld17389_RTRef] [binary](4) NOT NULL,
[_Fld17389_RRRef] [binary](16) NOT NULL,
[_Fld17390] [binary](1) NOT NULL,
[_Fld17391] [binary](1) NOT NULL,
[_Fld17392] [binary](1) NOT NULL,
[_Fld17393RRef] [binary](16) NOT NULL,
[_Fld17394RRef] [binary](16) NOT NULL,
[_Fld17395RRef] [binary](16) NOT NULL,
[_Fld17396] [numeric](15, 3) NOT NULL,
[_Fld17397] [numeric](14, 3) NOT NULL,
[_Fld17398RRef] [binary](16) NOT NULL,
[_Fld17399] [numeric](15, 2) NOT NULL,
[_Fld17400] [numeric](15, 2) NOT NULL,
[_Fld17401_TYPE] [binary](1) NOT NULL,
[_Fld17401_RTRef] [binary](4) NOT NULL,
[_Fld17401_RRRef] [binary](16) NOT NULL,
[_Fld17402_TYPE] [binary](1) NOT NULL,
[_Fld17402_RTRef] [binary](4) NOT NULL,
[_Fld17402_RRRef] [binary](16) NOT NULL,
[_Fld17403_TYPE] [binary](1) NOT NULL,
[_Fld17403_RTRef] [binary](4) NOT NULL,
[_Fld17403_RRRef] [binary](16) NOT NULL,
[_Fld17404_TYPE] [binary](1) NOT NULL,
[_Fld17404_RTRef] [binary](4) NOT NULL,
[_Fld17404_RRRef] [binary](16) NOT NULL,
[_Fld17405_TYPE] [binary](1) NOT NULL,
[_Fld17405_RTRef] [binary](4) NOT NULL,
[_Fld17405_RRRef] [binary](16) NOT NULL,
[_Fld17406_TYPE] [binary](1) NOT NULL,
[_Fld17406_RTRef] [binary](4) NOT NULL,
[_Fld17406_RRRef] [binary](16) NOT NULL,
[_Fld17407_TYPE] [binary](1) NOT NULL,
[_Fld17407_RTRef] [binary](4) NOT NULL,
[_Fld17407_RRRef] [binary](16) NOT NULL,
[_Fld17408_TYPE] [binary](1) NOT NULL,
[_Fld17408_RTRef] [binary](4) NOT NULL,
[_Fld17408_RRRef] [binary](16) NOT NULL,
[_Fld17409_TYPE] [binary](1) NOT NULL,
[_Fld17409_RTRef] [binary](4) NOT NULL,
[_Fld17409_RRRef] [binary](16) NOT NULL,
[_Fld17410RRef] [binary](16) NOT NULL,
[_Fld17411RRef] [binary](16) NOT NULL,
[_Fld17412_TYPE] [binary](1) NOT NULL,
[_Fld17412_RTRef] [binary](4) NOT NULL,
[_Fld17412_RRRef] [binary](16) NOT NULL,
[_Fld17413] [numeric](10, 5) NOT NULL,
[_Fld17414] [numeric](10, 5) NOT NULL,
[_Fld17415RRef] [binary](16) NOT NULL,
[_Fld17416] [binary](1) NOT NULL,
[_Fld17417RRef] [binary](16) NOT NULL,
[_Fld17418RRef] [binary](16) NOT NULL,
[_Fld17419RRef] [binary](16) NOT NULL,
[_Fld17420RRef] [binary](16) NOT NULL,
[_Fld17421RRef] [binary](16) NOT NULL,
[_Fld17422RRef] [binary](16) NOT NULL,
[_Fld17423RRef] [binary](16) NOT NULL,
[_Fld17424_TYPE] [binary](1) NOT NULL,
[_Fld17424_RTRef] [binary](4) NOT NULL,
[_Fld17424_RRRef] [binary](16) NOT NULL,
[_Fld17425] [binary](1) NOT NULL,
[_Fld17426] [binary](1) NOT NULL,
[_Fld17427] [binary](1) NOT NULL,
[_Fld17428] [binary](1) NOT NULL,
[_Fld17429RRef] [binary](16) NOT NULL,
[_Fld17430RRef] [binary](16) NOT NULL,
[_Fld17431RRef] [binary](16) NOT NULL,
[_Fld17432RRef] [binary](16) NOT NULL,
[_Fld17433_TYPE] [binary](1) NOT NULL,
[_Fld17433_RTRef] [binary](4) NOT NULL,
[_Fld17433_RRRef] [binary](16) NOT NULL,
[_Fld17434RRef] [binary](16) NOT NULL,
[_Fld17435_TYPE] [binary](1) NOT NULL,
[_Fld17435_RTRef] [binary](4) NOT NULL,
[_Fld17435_RRRef] [binary](16) NOT NULL,
[_Fld17436RRef] [binary](16) NOT NULL,
[_Fld17437_TYPE] [binary](1) NOT NULL,
[_Fld17437_RTRef] [binary](4) NOT NULL,
[_Fld17437_RRRef] [binary](16) NOT NULL,
[_Fld17438RRef] [binary](16) NOT NULL,
[_Fld17439RRef] [binary](16) NOT NULL,
[_Fld17440RRef] [binary](16) NOT NULL,
[_Fld17441RRef] [binary](16) NOT NULL,
[_Fld17442] [binary](1) NOT NULL,
[_Fld17443RRef] [binary](16) NOT NULL,
[_Fld17444RRef] [binary](16) NOT NULL,
[_Fld17445RRef] [binary](16) NOT NULL,
[_Fld17446RRef] [binary](16) NOT NULL,
[_Fld17447RRef] [binary](16) NOT NULL,
[_Fld17448RRef] [binary](16) NOT NULL,
[_Fld17449] [numeric](15, 2) NOT NULL,
[_Fld17450] [numeric](15, 2) NOT NULL,
[_Fld17451] [numeric](15, 2) NOT NULL,
[_Fld17452] [numeric](15, 2) NOT NULL,
[_Fld17453] [numeric](15, 2) NOT NULL,
[_Fld17454RRef] [binary](16) NOT NULL,
[_Fld17455RRef] [binary](16) NOT NULL,
[_Fld17456RRef] [binary](16) NOT NULL,
[_Fld17457RRef] [binary](16) NOT NULL,
[_Fld17458RRef] [binary](16) NOT NULL,
[_Fld17459RRef] [binary](16) NOT NULL,
[_Fld17460_TYPE] [binary](1) NOT NULL,
[_Fld17460_RTRef] [binary](4) NOT NULL,
[_Fld17460_RRRef] [binary](16) NOT NULL,
[_Fld17461RRef] [binary](16) NOT NULL,
[_Fld17462RRef] [binary](16) NOT NULL,
[_Fld17463RRef] [binary](16) NOT NULL,
[_Fld17464] [numeric](15, 3) NOT NULL,
[_Fld17465RRef] [binary](16) NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


Сообщение было отредактировано: 16 авг 12, 11:45
16 авг 12, 11:28    [13019224]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
munster,

Есть уверенность, что в

CREATE UNIQUE CLUSTERED INDEX [_InfoR17375_ByPeriod_TRN] ON [dbo].[_InfoRg17375]
(
[_Period] ASC,
[_RecorderTRef] ASC,
[_RecorderRRef] ASC,
[_LineNo] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

встака идет "в конец" и Вы не нарываетесь на Page Split?
16 авг 12, 11:47    [13019370]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,

не могу отредактировать ваше сообщение и уведить какими тегами сворачиваете сообщение. Напишите один тег пожалуйста.

я не знаю как проверить идет ли Page Split. документы разными датами идут и скорее всего он есть. но вот как просмотреть детально есть ли он?
16 авг 12, 12:20    [13019588]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
а если есть PageSplit, то тогда я так понимаю переходим к вопросу по FillFactor?
16 авг 12, 12:34    [13019675]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
munster
а если есть PageSplit, то тогда я так понимаю переходим к вопросу по FillFactor?


Нет. К вопросу о необходимости именно кластерного индекса.
16 авг 12, 12:42    [13019744]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,

вот тут уже диалог продолжить не смогу. так как не хватает знаний.
во первых где то проскальзывала мысль что самим майкрософтом рекомендуется везде кластеризированный индекс
во вторых будут ли некластерные индексы работать без кластерного..
в третих не знаю чем может аукнуться изменение типового решения. буду разбираться в своих же вопросах
16 авг 12, 14:05    [13020509]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
nicescar
Member

Откуда:
Сообщений: 94
munster
во первых где то проскальзывала мысль что самим майкрософтом рекомендуется везде кластеризированный индекс
во вторых будут ли некластерные индексы работать без кластерного..
в третих не знаю чем может аукнуться изменение типового решения. буду разбираться в своих же вопросах

1 - мысль такая действительно пропагандируется МС-ом, однако, подходить к этому вопросу нужно с осторожностью. Чтобы не страдать потом от кучи сплитов.
2 - работать то они будут, но вот запросы, которые используют эти индексы, могут замедлиться сильно.
16 авг 12, 14:11    [13020559]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
1
Guest
nicescar

А теперь медлено и не торопясь - на основании чего -' но вот запросы, которые используют эти индексы, могут замедлиться сильно.' ?
16 авг 12, 14:43    [13020814]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
автор
я не знаю как проверить идет ли Page Split.


Попробуйте
USE <DB>

select AllocUnitName, count([AllocUnitName]) [Splits]
from ::fn_dblog(null, null)
where Operation = N'LOP_DELETE_SPLIT' and parsename(AllocUnitName,3) <> 'sys'
group by AllocUnitName

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



Только там, где его действительно имеет смысл иметь.

munster
во вторых будут ли некластерные индексы работать без кластерного..


Будут.

munster
в третих не знаю чем может аукнуться изменение типового решения


Ну, тогда, хотя бы с филфактором поиграйтесь.
16 авг 12, 14:48    [13020852]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,


Запрос выполнил. но проблемной таблицы здесь и в помине нет

dbo._AccumRgT19567._Accum19567_ByDims_TRRRRRN 60
dbo._AccumRgTn18374._Accum18374_ByDims_TRRRRRRRNN 27
dbo._AccumRgTn18374._Accum18374_ByDims18372_RT 23
dbo._AccumRgTn18374._Accum18374_ByDims18373_RT 16
dbo._AccumRgTn18374._Accum18374_ByDims18371_RT 16
dbo._AccumRg19558._Accum19558_ByDims19566_RTRN 16
dbo._AccumRg19606._Accum19606_ByPeriod_TRN 9
dbo._AccumRg19670._Accum19670_ByPeriod_TRN 9
dbo._AccumRg18357.Nomenklatura 8
dbo._AccumRgT19567._Accum19567_ByDims19566_TR 7
dbo._AccumRg19606._Accum19606_ByDims19616_RTRN 7
dbo._AccumRg19670._Accum19670_ByProperty19683_RTRN 4
dbo._AccumRg19670._Accum19670_ByProperty19684_RTRN 4
dbo._InfoRg16163._InfoR16163_BySimpleKey_B 3
dbo._AccumRg19670._Accum19670_ByProperty19682_RTRN 3
dbo._AccumRg19606._Accum19606_ByRecorder_RN 2
dbo._AccumRg19558._Accum19558_ByRecorder_RN 2
dbo._AccumRg19558.Sklad 2
dbo._AccumRg19670._Accum19670_ByRecorder_RN 2
dbo._AccumRg18357._Accum18357_ByRecorder_RN 1

по поводу филлфактора: начал думать в этом направлении но таблица в 60GB. просто не поиграешься. есть ли какие нить критерии для его использования?
16 авг 12, 16:20    [13021668]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
nicescar
Member

Откуда:
Сообщений: 94
1
nicescar

А теперь медлено и не торопясь - на основании чего -' но вот запросы, которые используют эти индексы, могут замедлиться сильно.' ?

ну предположим, есть запрос, по неравенству отбирающий поле _Period. Если удалить кластерный индекс, то этот запрос будет работать дольше.
16 авг 12, 16:35    [13021786]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
munster
Member

Откуда:
Сообщений: 36
pkarklin,

Забыл поблагодарить. Спасибо за интересный запрос - пригодится в копилке знаний.
вопрос про филфактор отпал - разве что в будущем буду смотреть на проблемных таблицах с большим PageSplit
16 авг 12, 16:41    [13021839]     Ответить | Цитировать Сообщить модератору
 Re: Clustered Index Insert  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
nicescar
1
nicescar

А теперь медлено и не торопясь - на основании чего -' но вот запросы, которые используют эти индексы, могут замедлиться сильно.' ?

ну предположим, есть запрос, по неравенству отбирающий поле _Period. Если удалить кластерный индекс, то этот запрос будет работать дольше.
По-моему никто не говорил про удаление кластерного, речь шла о том, чтобы поменять кластерный в некластерный.

Так что можно еще раз, медлено и не торопясь - на основании чего -' но вот запросы, которые используют эти индексы, могут замедлиться сильно.' ?
Почему некластерные индексы вдруг должны начать сильно медленне работать?
16 авг 12, 21:59    [13023397]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить