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

Откуда: Москва (Муром)
Сообщений: 74925
Ennor Tiegael
При этом точечные выборки по нему не осуществляются, вытягивается всегда "простыня" из нескольких тысяч или десятков тысяч строк. В результате сервер выбирает сканирование кластерника, и все ложится...


Не замечал отказа оптимизатора от использования "коротких" индексов после включения версионности на нескольких немаленьких бд. 14 байт добавляется к строке, но они не входят в сам индекс. Почему сервер должен отказаться от его использования - непонятно.
1 фев 13, 09:31    [13860271]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
pkarklin
14 байт добавляется к строке, но они не входят в сам индекс. Почему сервер должен отказаться от его использования - непонятно.
А куда же они входят, в таком случае? Лежат на leaf level'е, как миленькие.
1 фев 13, 09:37    [13860291]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
Ennor Tiegael
pkarklin
14 байт добавляется к строке, но они не входят в сам индекс. Почему сервер должен отказаться от его использования - непонятно.
А куда же они входят, в таком случае? Лежат на leaf level'е, как миленькие.
Зазве не отдельно лежат, как при INCLUDE?
1 фев 13, 09:39    [13860300]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

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

Простите, а где лежит INCLUDE, как не там же? Или после 2005 версии архитектура включения поменялась?..
1 фев 13, 09:43    [13860312]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Гость333
Поправка: индексы в онлайне можно перестраивать и при выключенном на базе RCSI. 14-байтовый ID при этом всё равно добавляется

Поправка на поправку: ни фига он не добавляется. Только при включенном на базе READ_COMMITTED_SNAPSHOT или ALLOW_SNAPSHOT_ISOLATION...
1 фев 13, 09:43    [13860315]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Ennor Tiegael
А куда же они входят, в таком случае? Лежат на leaf level'е, как миленькие.


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

Сообщение было отредактировано: 1 фев 13, 10:05
1 фев 13, 09:58    [13860354]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Создал базу с RCSI, в ней таблицу с PK типа int и столбцом int not null. По столбцу построил индекс.
Типичный дамп записи leaf level'а при построении индекса в офлайне:
Slot 406, Offset 0xea6, Length 9, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 9

Memory Dump @0x000000000E70CEA6

0000000000000000: 06010000 00170d00 00†††††††††††††††††.........

Типичный дамп записи при перестроении индекса в онлайне:
Slot 212, Offset 0x1440, Length 24, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 24

Memory Dump @0x000000000E70D440

0000000000000000: 46000100 00000e13 00000000 00000000 †F...............
0000000000000010: 0000c632 00000000 †††††††††††††††††††..Æ2....

Почему-то на 15 байт запись растолстела, вместо 14.

Добавил в индекс include-поле, также типа int not null.

Дамп записи для офлайна:
Slot 189, Offset 0x9f9, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000FD9A9F9

0000000000000000: 06010000 00600d00 00020000 00††††††††.....`.......

За счёт include-поля запись индекса потолстела на 4 байта.

Дамп записи для онлайна:
Slot 159, Offset 0x11c4, Length 28, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 28

Memory Dump @0x000000001548B1C4

0000000000000000: 46000100 00000809 00000200 00000000 †F...... ........
0000000000000010: 00000000 0000e832 00000000 ††††††††††......è2....

Для базы с ALLOW_SNAPSHOT_ISOLATION картина такая же. MSSQL 2008R2.
1 фев 13, 10:08    [13860381]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
HoBTID
Member

Откуда:
Сообщений: 929
Гость333,
Ценная информация, много народу сэкономит деньги на покупке Enterprise Edition.
1 фев 13, 10:29    [13860465]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
HoBTID
Member

Откуда:
Сообщений: 929
В смысле, не будут ее покупать
1 фев 13, 10:30    [13860470]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

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

Тему свою расскрыть не хотите?
1 фев 13, 10:32    [13860484]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
HoBTID
Member

Откуда:
Сообщений: 929
pkarklin
Нет, кому надо и так поняли.
1 фев 13, 10:36    [13860500]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
HoBTID
Нет, кому надо и так поняли.


Версионность поддерживается всеми редакциями, включая Express. Ваша сентеция по поводу EE абсолютно непонятна.
1 фев 13, 10:38    [13860510]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
pkarklin
HoBTID
Нет, кому надо и так поняли.


Версионность поддерживается всеми редакциями, включая Express. Ваша сентеция по поводу EE абсолютно непонятна.
Видимо, речь не о поддержки RCSI, а о поддержке онлайн-перестроения.
1 фев 13, 10:43    [13860533]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Ennor Tiegael
Видимо, речь не о поддержки RCSI, а о поддержке онлайн-перестроения.


Да, онлайн индексирование поддерживается только в ЕЕ. Но даже в Express редакции включение RCS и оффлайн индексирование добавит "лишнюю" служебную информацию для поддержки версионности.

Сообщение было отредактировано: 1 фев 13, 10:47
1 фев 13, 10:47    [13860567]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Гость333
Member

Откуда:
Сообщений: 3683
pkarklin
Ennor Tiegael
А куда же они входят, в таком случае? Лежат на leaf level'е, как миленькие.


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

При построении плана запроса оптимизатор всё равно неким образом учитывает физические параметры таблиц/индексов. Приведу пример.

Подготовим таблицу с 10000 записей, первичным ключом типа int и индексом по другому полю с типом int:
SET NOCOUNT ON;
CREATE TABLE dbo.Test(id INT IDENTITY CONSTRAINT PK_Test PRIMARY KEY WITH FILLFACTOR = 100, a AS id + 1, b VARCHAR(5) DEFAULT 'x');
BEGIN TRANSACTION;
GO
INSERT dbo.Test DEFAULT VALUES;
GO 10000
COMMIT TRANSACTION;
CREATE INDEX I_Test ON dbo.Test(a) WITH(FILLFACTOR = 100);

Посмотрим, сколько места занимает таблица:
sp_spaceused 'dbo.Test'

data=184 KB, index_size=176 KB

Посмотрим план выполнения такого запроса:
SELECT * FROM dbo.Test WHERE a BETWEEN 1 AND 100;

Видим Clustered Index Scan + Filter. При этом оптимизатор понимает, что нужно просканировать 10000 записей, а на выходе будет примерно 98.9902 записей. И всё равно отказывается от использования индекса по полю "a".

Далее, "чуть-чуть" ухудшим параметры кластерного индекса:
ALTER INDEX PK_Test ON dbo.Test REBUILD WITH(FILLFACTOR = 1);

При этом получилось:
data=16000 KB, index=208 KB


Смотрим план того же запроса:
SELECT * FROM dbo.Test WHERE a BETWEEN 1 AND 100;

Когда оптимизатор понимает, сколько данных ему нужно прочитать теперь при скане кластерного индекса, он в ужасе говорит "да ну вас нафиг, ребята, с вашими приколами" — и делает Index Seek по индексу по полю "a".

PS. Правда, когда я сделал филфактор I_Test также равным единице, Clustered Index Scan не вернулся.
1 фев 13, 11:01    [13860632]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Гость333,

автор
Когда оптимизатор понимает, сколько данных ему нужно прочитать


Он смотрит на заполненность страницы, а не на длину одной строки.
1 фев 13, 11:06    [13860651]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Гость333
Member

Откуда:
Сообщений: 3683
pkarklin
Но даже в Express редакции включение RCS и оффлайн индексирование добавит "лишнюю" служебную информацию для поддержки версионности.

Неа, не добавит. Офлайн-индексирование наоборот, убирает из индекса атрибуты VERSIONING_INFO.
1 фев 13, 11:07    [13860654]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
pkarklin
Ennor Tiegael
А куда же они входят, в таком случае? Лежат на leaf level'е, как миленькие.


Это служебная информация, не влияющая на размер ключа индекса. Основываясь на какой информации сервер откажется использовать этот индекс?
Основываясь на эффективной плотности данных, т.е. на количестве страниц, которые нужно прочесть, чтобы получить искомые X строк.

Примерно то же происходит, если выставить низкий филфактор, порядка 30-60.
1 фев 13, 11:16    [13860685]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Crimean
Member

Откуда:
Сообщений: 13148
я не смог воспроизвести отказ от использования индекса после он-лайн перестрояния
да и после updateusage разница в занимаемом размере как-то не впечатляет, хотя и присутствует
1 фев 13, 12:14    [13861096]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Гость333,

Зато добавит первая же операция модификации.

USE master
GO
SELECT @@version
GO


Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) 
Mar 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 row(s) affected)

CREATE DATABASE Test1
GO

ALTER DATABASE Test1 SET READ_COMMITTED_SNAPSHOT ON
GO

USE Test1
GO

CREATE TABLE dbo.T1(col1 int NOT NULL)
GO

CREATE INDEX IX_T1_col1 ON dbo.T1 (col1)
GO

INSERT dbo.T1 VALUES(1), (2), (3)
GO

DBCC IND(Test1, T1, 2)
GO


PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 77 NULL NULL 2105058535 2 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 55 1 77 2105058535 2 1 72057594038845440 In-row data 2 0 0 0 0 0

(2 row(s) affected)


DBCC TRACEON(3604);
GO
 
DBCC PAGE(Test1, 1, 55, 1);
GO

DATA:


Slot 0, Offset 0x60, Length 27, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27

Memory Dump @0x000000000BB4C060

0000000000000000: 46010000 00150000 00010000 00000000 †F...............
0000000000000010: 00000000 00070000 000000†††††††††††††...........

Slot 1, Offset 0x7b, Length 27, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27

Memory Dump @0x000000000BB4C07B

0000000000000000: 46020000 00150000 00010001 00000000 †F...............
0000000000000010: 00000000 00070000 000000†††††††††††††...........

Slot 2, Offset 0x96, Length 27, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27

Memory Dump @0x000000000BB4C096

0000000000000000: 46030000 00150000 00010002 00000000 †F...............
0000000000000010: 00000000 00070000 000000†††††††††††††...........

ALTER INDEX IX_T1_col1 ON dbo.T1 REBUILD WITH (ONLINE = OFF)
GO
DBCC IND(Test1, T1, 2)
GO


PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 80 NULL NULL 2105058535 2 1 72057594038976512 In-row data 10 NULL 0 0 0 0
1 77 1 80 2105058535 2 1 72057594038976512 In-row data 2 0 0 0 0 0

(2 row(s) affected)

DBCC PAGE(Test1, 1, 77, 1);
GO


DATA:


Slot 0, Offset 0x60, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000FB6A060

0000000000000000: 06010000 00150000 00010000 00††††††††.............

Slot 1, Offset 0x6d, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000FB6A06D

0000000000000000: 06020000 00150000 00010001 00††††††††.............

Slot 2, Offset 0x7a, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000FB6A07A

0000000000000000: 06030000 00150000 00010002 00††††††††.............


UPDATE dbo.T1 SET col1 = 4 WHERE col1 = 1
GO
DBCC PAGE(Test1, 1, 77, 1);
GO


DATA:


Slot 0, Offset 0x6d, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000DC3A06D

0000000000000000: 06020000 00150000 00010001 00††††††††.............

Slot 1, Offset 0x7a, Length 13, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = Record Size = 13

Memory Dump @0x000000000DC3A07A

0000000000000000: 06030000 00150000 00010002 00††††††††.............

Slot 2, Offset 0xd8, Length 27, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27

Memory Dump @0x000000000DC3A0D8

0000000000000000: 46040000 00150000 00010000 00000000 †F...............
0000000000000010: 00000000 000f0000 000000†††††††††††††...........
1 фев 13, 13:00    [13861437]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Гость333
Member

Откуда:
Сообщений: 3683
pkarklin
Гость333,

Зато добавит первая же операция модификации.

Да. Я хотел об этом написать, но отвлекли :)
Я подумал, что под "офлайн-индексированием" вы имеете в виду "перестроение индекса в офлайне".
1 фев 13, 13:08    [13861488]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

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

Ага, кажется начинаю понимать. У меня основная проблема с онлайн ребилдом была на индексах, построенных на полях внешних ключей, обычно int + int кластерника. При инсерте записи добавлялись с VERSIONING_INFO в комплекте, но первый же оффлайн ребилд это дело чистил. Логика работы базы была такова, что после вставки эти поля никогда не обновлялись - форейны же. Поэтому де факто раздутым был только хвост индекса.

Ну и первый же онлайн-ребилд превращал его в тыкву целиком, после чего у оптимизатора перещелкивало, и в пять ночи у меня звонил телефон
1 фев 13, 16:06    [13862812]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
словил дэдлок при ребилде индекса с ONLINE=ON

RCSI не включен, но таблица lo_users с хинтом (nolock) в процедуре - виновнике дэдлока

ребилдом индекса наложена блокировка Sch-M.

я ожидал, что индекс не будет заблокирован при ONLINE=ON.

или здесь только RCSI может помочь?

К сообщению приложен файл (ddd.xdl - 4Kb) cкачать
4 фев 13, 10:51    [13870876]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Ennor Tiegael
Member

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

Я вижу
trancount="1"
в выжившем процессе. У вас там в процедуре ребилда, надеюсь, транзакций никаких не открывается?
4 фев 13, 11:16    [13871031]     Ответить | Цитировать Сообщить модератору
 Re: подскажите способ заставить сервер работать 24/7  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
Ennor Tiegael
Shurgenz,

Я вижу
trancount="1"
в выжившем процессе. У вас там в процедуре ребилда, надеюсь, транзакций никаких не открывается?


BEGIN TRY - через нее пытается ребилднуться индекс с ONLINE=ON, если неудачно, то он ребилдается с ONLINE=OFF.

так что открывается... и что, получается, что это транзакция на BEGIN TRY лочит индекс?
4 фев 13, 12:04    [13871375]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить