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

Откуда:
Сообщений: 1161
Пухнет таблица на SQL2005 и 2008.
На 2014 - нет.

--1
CREATE TABLE TestTab
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Txt] [varchar](max) NULL,
	[Int] [int] NULL,
	[Dat] [datetime] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] )
) 

--2
insert  TestTab(txt,int,Dat)
select 'X', day(getdate()), getdate()


--3 повторяем много раз нажав и удерживая F5:
begin tran T1
	insert  TestTab
	select REPLICATE(cast('w' as varchar(max)),50000) + cast(cast(RAND()*1000000 as int) as varchar(10)), day(getdate()), getdate()
rollback

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


Но почему?
15 фев 16, 15:17    [18818492]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
хе-хе )
Guest
EXEC sp_SpaceUsed 'TestTab'
15 фев 16, 16:04    [18818901]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Подготовил небольшое репро:

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
	DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (
	id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	txt VARCHAR(MAX) NULL
) 
GO
INSERT dbo.tbl (txt) SELECT NULL
GO

BEGIN TRAN
	INSERT dbo.tbl
	SELECT REPLICATE('X', 50000) -- REPLICATE(CAST('X' AS VARCHAR(MAX)), 50000)
ROLLBACK TRAN
GO 10

SELECT p.[rows], a.type_desc, a.total_pages, a.used_pages, a.data_pages
FROM sys.partitions p
LEFT JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE p.[object_id] = OBJECT_ID('dbo.tbl')

Если константа X не кастуется к VARCHAR(MAX), то проблемы нет:

rows      type_desc         total_pages   used_pages   data_pages
--------- ----------------- ------------- ------------ ------------
1         IN_ROW_DATA       2             2            1
1         LOB_DATA          0             0            0

Если кастуется, то получаем ситуацию о которой говорил Alexander Us :

rows      type_desc         total_pages   used_pages   data_pages
--------- ----------------- ------------- ------------ ------------
1         IN_ROW_DATA       2             2            1
1         LOB_DATA          65            62           0

В общем присоединяюсь к обсуждению. Интересно узнать: баг или фича?
15 фев 16, 16:16    [18819038]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
хе-хе ),

после повторов п.3:

namerowsreserveddataindex_sizeunused
TestTab14696 KB4680 KB8 KB8 KB


после ещё повторов п.3:

namerowsreserveddataindex_sizeunused
TestTab18856 KB8808 KB8 KB40 KB
15 фев 16, 16:22    [18819108]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
А вот ещё случай, гогда TRUNCATE TABLE не освобождает место на диске:
(работает на SQL 2005, на остальных не проверял)


--1
CREATE TABLE TestTab2
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Txt] [varchar](max) NULL,
 CONSTRAINT [PK_Test2] PRIMARY KEY CLUSTERED ([ID] )
) 

--2
insert  TestTab2(txt)
select 'X'


--3 повторяем много раз нажав и удерживая F5:
begin tran T1
	insert  TestTab2
	select REPLICATE(cast('w' as varchar(max)),50000) + cast(cast(RAND()*1000000 as int) as varchar(10)) 
rollback


--4 измеряем
EXEC sp_SpaceUsed 'TestTab2' 

--5 пытается очистить таблицу
truncate table TestTab2

--6 измеряем
EXEC sp_SpaceUsed 'TestTab2'   -- у меня тут почти то же что в пункте 4, но с 0 строк
15 фев 16, 16:33    [18819224]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
хе-хе )
Guest
2012 - не воспроизводятся случаи ...
15 фев 16, 16:49    [18819333]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
хе-хе ),

на 2014 тоже не воспроизводятся.

Но на 2005 и 2008 воспроизводятся
15 фев 16, 16:52    [18819369]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Alexander Us
Но на 2005 и 2008 воспроизводятся

Полные версии будут публиковаться или ну нафиг ?
15 фев 16, 16:56    [18819396]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Glory
Member

Откуда:
Сообщений: 104751
select * from sys.dm_db_index_physical_stats(db_id(), object_id('TestTab2'), null, null, N'DETAILED')
15 фев 16, 16:58    [18819416]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
У меня воспроизвелось на:

Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
15 фев 16, 17:01    [18819434]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
o-o
Guest
У меня 2008 R2 SP3 и не воспроизвелось.
Т. е. после хоть скольки роллбэков размер таблицы устаканивается на черырех страницах: iam, data, iam, lob.
rows 0
reserved 32 Kb
data 24 Kb
index 8Kb.
Интернет отвалился, пишу с телефона, картинок не будет, разве что из дома
15 фев 16, 17:04    [18819459]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

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


--2005:
select @@version
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)

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
9.00.5000.00	SP4	Enterprise Edition (64-bit)



--2008:
select @@version
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 
	Apr  2 2010 15:48:46 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
10.50.1600.1	RTM	Developer Edition (64-bit)
15 фев 16, 17:05    [18819465]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Вы реально хотите решать проблему на RTM-ах ?

https://support.microsoft.com/en-us/kb/2295878
15 фев 16, 17:05    [18819466]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
o-o
Guest
интернет вернулся
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (Intel X86) Aug 19 2014 12:21:07 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

К сообщению приложен файл. Размер - 85Kb
15 фев 16, 17:12    [18819504]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

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


после множества вставок с откатом:
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count
19189777381811CLUSTERED INDEXIN_ROW_DATA10011142.24116629602171830161616NULL
19189777381811CLUSTERED INDEXLOB_DATA100NULLNULL4890000000NULL


после truncate table:
database_idobject_idindex_idpartition_numberindex_type_descalloc_unit_type_descindex_depthindex_levelavg_fragmentation_in_percentfragment_countavg_fragment_size_in_pagespage_countavg_page_space_used_in_percentrecord_countghost_record_countversion_ghost_record_countmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytesforwarded_record_count
19189777381811CLUSTERED INDEXIN_ROW_DATA0000000000000NULL


и
EXEC sp_SpaceUsed 'TestTab2'

namerowsreserveddataindex_sizeunused
TestTab204040 KB3920 KB0 KB120 KB
15 фев 16, 17:13    [18819506]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Glory
Вы реально хотите решать проблему на RTM-ах ?

https://support.microsoft.com/en-us/kb/2295878


Glory,

Спасибо!
15 фев 16, 17:22    [18819559]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Glory
Вы реально хотите решать проблему на RTM-ах ?

Однозначно нет :) и это самое разумное решение - накатить SP.

У меня другого рода вопрос. Кто может подсказать почему баг воспроизводится, когда:

REPLICATE(CAST('X' AS VARCHAR(MAX)), 50000)

при этом вот так уже все будет норм:

REPLICATE('X', 50000)
?

С точки зрения плана:

<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(max),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.....',0)">
    <Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="true">
    <ScalarOperator>
        <Const ConstValue="'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.....'" />
    </ScalarOperator>
    </Convert>
</ScalarOperator>

разница только в этом между планами:

<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="replicate(CONVERT(varchar(max),'X',0),(10000))">
  <Intrinsic FunctionName="replicate">
    <ScalarOperator>
      <Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="false">
        <ScalarOperator>
          <Const ConstValue="'X'" />
        </ScalarOperator>
      </Convert>
    </ScalarOperator>
    <ScalarOperator>
      <Const ConstValue="(10000)" />
    </ScalarOperator>
  </Intrinsic>
</ScalarOperator>
15 фев 16, 17:29    [18819603]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
К слову, только что накатил SP1:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Проблема не воспроизводится. Но вопрос выше остается. Заранее спасибо, если кто поможет разобраться.
15 фев 16, 17:32    [18819632]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
хе-хе )
Guest
Потому что Varchar(max) это lob_data и хранится не как in_row_data/owerflow_data
15 фев 16, 17:49    [18819737]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
o-o
Guest
хе-хе )
Потому что Varchar(max) это lob_data и хранится не как in_row_data/owerflow_data

а вот и нет.
просто кастить правильно надо.
когда делаете REPLICATE('X', 50000), не откастив к varchar(max), результат обрезается до 8000 символов
15 фев 16, 18:03    [18819857]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Различие между LOB и IN_ROW я понимаю. Вопрос был в другом. Возможно я не совсем корректно выразился. Почему проблема воспроизводится, когда используется явный каст к VARCHAR(MAX)?
15 фев 16, 18:05    [18819869]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Вопрос снимается...

SELECT LEN(REPLICATE('X', 10000)), LEN(REPLICATE(CAST('X' AS VARCHAR(MAX)), 10000))

----------- ------------
8000        10000


о-о, спасибо :)
15 фев 16, 18:07    [18819885]     Ответить | Цитировать Сообщить модератору
 Re: Почему пухнет таблица?  [new]
komrad
Member

Откуда:
Сообщений: 5763
Glory
select * from sys.dm_db_index_physical_stats(db_id(), object_id('TestTab2'), null, null, N'DETAILED')

поддержу
ghost records
15 фев 16, 19:03    [18820154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить