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

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

Есть таблица в БД MS SQL SERVER 2012 EE, в которой есть поле с типом данных XML (название поля этой таблицы - options).
Индекса по этому полю нет.

CREATE TABLE W.tt
(
[action_id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED ,
[options] [xml] NOT NULL,
[isdeleted] [bit] NOT NULL,
[dt] [datetime] NOT NULL,
[employee_id] [int] NOT NULL,
[isActive] [bit] NOT NULL,
[begin_dt] [datetime] NULL,
[end_dt] [datetime] NULL,
[action_name] [varchar](200) NULL,
[image_url] [varchar](100) NULL,
[filter_map] [smallint] NULL,
[country_map] [tinyint] NULL,
[group_id] [int] NOT NULL
)




Запрос к этой таблице:
SELECT
MIN(DATALENGTH(CAST( options AS VARCHAR(MAX)))) AS minn
,MAX(DATALENGTH(CAST( options AS VARCHAR(MAX)))) AS maxx
FROM W.tt
/*
minn maxx
===============
105938 1768864
*/


Вопрос:

При обращении к этой таблице, например

SELECT
isdeleted,
dt,
employee_id,
isActive,
begin_dt,
end_dt
FROM W.tt

содержимое поля options тоже читается с диска или из кэша ?

СПАСИБО.
9 июн 14, 10:04    [16140505]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
dpi
содержимое поля options тоже читается с диска или из кэша ?
Из кэша, конечно (разумеется, если данные там уже есть).
9 июн 14, 10:09    [16140531]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
Glory
Member

Откуда:
Сообщений: 104751
dpi
содержимое поля options тоже читается с диска или из кэша ?

Если вы про то, читаются ли страницы LOB полей при отсутсивии полей в тексте запроса, то ИМХО - нет.
9 июн 14, 10:27    [16140633]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
msLex
Member

Откуда:
Сообщений: 8856
alexeyvg
dpi
содержимое поля options тоже читается с диска или из кэша ?
Из кэша, конечно (разумеется, если данные там уже есть).


как я понял, вопрос был в другом. Будут ли вообще обращение к XML данным если поля нет в запросе.

Ответ следующий.

Если данные умещаются на основной странице (XML < 8КБ) то да, иначе нет.
9 июн 14, 10:29    [16140645]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
Glory
Member

Откуда:
Сообщений: 104751
msLex
Если данные умещаются на основной странице (XML < 8КБ) то да, иначе нет.

Это еще зависит от опции text in row
9 июн 14, 10:31    [16140653]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
Glory
msLex
Если данные умещаются на основной странице (XML < 8КБ) то да, иначе нет.

Это еще зависит от опции text in row
Да, правильно, я невнимательно прочитал вопрос...
9 июн 14, 10:35    [16140699]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
msLex
Member

Откуда:
Сообщений: 8856
Glory
msLex
Если данные умещаются на основной странице (XML < 8КБ) то да, иначе нет.

Это еще зависит от опции text in row

а разве это работает для XML и прочих "современных" LOB-ах?
9 июн 14, 10:37    [16140710]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
Glory
Member

Откуда:
Сообщений: 104751
msLex
Glory
пропущено...

Это еще зависит от опции text in row

а разве это работает для XML и прочих "современных" LOB-ах?

Нет. Для них есть

large value types out of row
1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.
9 июн 14, 10:40    [16140724]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
dpi
Member

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

Спасибо за ответы.


SELECT [name] AS tablename, large_value_types_out_of_row, text_in_row_limit
FROM sys.tables
WHERE NAME = 'tt'
/*
tablename large_value_types_out_of_row text_in_row_limit
==================================================
tt 0 0
*/

Значит, в моём случае, в этом поле options хранится "указатель" на сами значения поля options, которые в свою очередь, хранятся отдельно от страниц данных таблицы tt ? Так ?


И верно ли, что т.к. параметр large_value_types_out_of_row = 0, то при уменьшении объёма значений этого поля и при наличие места на странице данных таблицы - значения этого поля будут храниться на странице данных таблицы(т.е. в строке страницы данных) ?

Ещё раз спасибо.
9 июн 14, 11:46    [16141247]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
msLex
Member

Откуда:
Сообщений: 8856
dpi
Здравствуйте.

Спасибо за ответы.


SELECT [name] AS tablename, large_value_types_out_of_row, text_in_row_limit
FROM sys.tables
WHERE NAME = 'tt'
/*
tablename large_value_types_out_of_row text_in_row_limit
==================================================
tt 0 0
*/

Значит, в моём случае, в этом поле options хранится "указатель" на сами значения поля options, которые в свою очередь, хранятся отдельно от страниц данных таблицы tt ? Так ?

Нет, large_value_types_out_of_row = 0 значит, что xml, по-возможности, будет храниться на странице с данными.
для изменения нужно вызвать
exec sp_tableoption N'table_name', 'large value types out of row', '1'


dpi
И верно ли, что т.к. параметр large_value_types_out_of_row = 0, то при уменьшении объёма значений этого поля и при наличие места на странице данных таблицы - значения этого поля будут храниться на странице данных таблицы(т.е. в строке страницы данных) ?

Ещё раз спасибо.

Небольшой тест показывает, что при update в меньшую сторону данные не переносятся в IN_ROW_DATA страницы, более того, занятые LOB_DATA страницы не освобождаются.


-- собирает большой varchar(max)
declare 
	@c varchar(max) = ''
	, @i int = 0	
while @i < 1000
begin
	set @c = @c + cast(newid() as varchar(max))
	set @i += 1 
end

	


create table dbo.a(id int identity primary key, c XML) 

	
insert dbo.a(c) 
select @c
select *
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.a'), null, null, 'detailed')

update a set 
c = '1'


select *
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.a'), null, null, 'detailed')


drop table a
9 июн 14, 11:58    [16141348]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
msLex
Member

Откуда:
Сообщений: 8856
msLex
dpi
И верно ли, что т.к. параметр large_value_types_out_of_row = 0, то при уменьшении объёма значений этого поля и при наличие места на странице данных таблицы - значения этого поля будут храниться на странице данных таблицы(т.е. в строке страницы данных) ?

Ещё раз спасибо.

Небольшой тест показывает, что при update в меньшую сторону данные не переносятся в IN_ROW_DATA страницы, более того, занятые LOB_DATA страницы не освобождаются.


-- собирает большой varchar(max)
declare 
	@c varchar(max) = ''
	, @i int = 0	
while @i < 1000
begin
	set @c = @c + cast(newid() as varchar(max))
	set @i += 1 
end

	


create table dbo.a(id int identity primary key, c XML) 

	
insert dbo.a(c) 
select @c
select *
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.a'), null, null, 'detailed')

update a set 
c = '1'


select *
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.a'), null, null, 'detailed')


drop table a

я вас обманул, данные действительно переносятся на IN_ROW_DATA страницы
9 июн 14, 12:12    [16141448]     Ответить | Цитировать Сообщить модератору
 Re: XML хранение на странице данных.  [new]
dpi
Member

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

Благодарю за помощь.

Выполню:
1) Вариант
exec sp_tableoption N'tt', 'large value types out of row', '1'
и
проверю производительность.


2) Вариант
Т.к. это поле я не использую для чтения, то попробую вариант с переносом его в другую таблицу tt2 и связью с данной таблицей tt как 1 к 1.
10 июн 14, 14:21    [16148979]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить