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

Откуда: Гомель-Минск
Сообщений: 474
Добрый день, коллеги!

На продакш сервере нарисовалась следующая проблема с tempdb:
При выполнении простого запроса (общее количество данных в таблицах с которыми он оперирует не более 3 Gb)

получаю:
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'.

В момент выполнения запроса мониторорю постоянно тем db, картина стаблильная и не меняется:
exec sp_spaceused

database_name database_size unallocated space
tempdb 35927.06 MB 34007.08 MB

reserved data index_size unused
1584 KB 704 KB 696 KB 184 KB

С чем может быть связана проблема?
14 сен 12, 12:38    [13163866]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Дополнение:
select @@version

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1797.0 (X64) Jun 1 2011 15:43:18 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

sp_helpdb 'tempdb'

name db_size owner dbid created status compatibility_level
tempdb 35927.06 MB sa 2 Aug 24 2012 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 100

name fileid filename filegroup size maxsize growth usage
tempdev 1 E:\MSSQL$DCBIDEV\MSSQL10_50.DCBIDEV\MSSQL\Data\tempdb.mdf PRIMARY 34824832 KB Unlimited 10% data only
templog 2 E:\MSSQL$DCBIDEV\MSSQL10_50.DCBIDEV\MSSQL\Data\templog.ldf NULL 1964480 KB Unlimited 10% log only
14 сен 12, 12:46    [13163965]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Fire83
С чем может быть связана проблема?


Файл лога (templog.ldf) пытаеся вырасти, а места на диске нет
14 сен 12, 13:16    [13164308]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Глеб
Fire83
С чем может быть связана проблема?


Файл лога (templog.ldf) пытаеся вырасти, а места на диске нет
Сообщение об ошибке говорит, что это не файл лога.
14 сен 12, 13:18    [13164334]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
stavgreengo
Member

Откуда:
Сообщений: 710
DBCC CHECKDB для tempdb что возвращает ?
14 сен 12, 13:22    [13164394]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3019
и не мешает выполнить sp_spaceused с параметром @updateusage
а то информация может быть некорректной
14 сен 12, 13:24    [13164415]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Глеб,

неа, не оно) Запрос на котором проблема не обновляет ничего в tempdb.
Вот его структура:
UPDATE cost
  SET DimDataQualitySK = 18
FROM dbo.FactCost cost
  INNER JOIN dbo.Dim1 afe ON ( afe.Dim1SK = cost.Dim1SK )    
  INNER JOIN dbo.Dim2 j ON ( j.Dim2SK = cost.Dim2SK )
  INNER JOIN (
    SELECT BK, Number
    FROM (
        SELECT DISTINCT afe.Dim2SK, j.BK, afe.Number
        FROM dbo.Dim1 afe
           INNER JOIN dbo.Dim2 j ON ( afe.Dim2SK = j.Dim2SK )
       WHERE afe.Number <> '<undefined>'
    ) t
    GROUP BY BK, Number 
    HAVING COUNT(*) > 1  
   )  T on ( T.BK = j.BK AND t.Number = AFE.Number ) 


Понятно что наверняка это HASH JOIN хочет что-то записать в tempdb, но повторюсь - общий объем данных с которыми идет работа не превышает 3GB, что в более чем 10 раз меньше того что показывает доступным sp_spaceused
14 сен 12, 13:32    [13164507]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Дополнение:
Если заменить UPDATE на SELECT COUNT(*) то ошибка та же самая.

Попробовал провести тест, итак:
exec sp_spaceused

tempdb	35927.06 MB	34006.98 MB

select top 1000000 o1.* into #tmp
from sys.objects o1, sys.objects o2,sys.objects o3 

exec sp_spaceused

tempdb	35927.06 MB	33914.91 MB


Выходит таки пишет в tempdb .т.е проблема только при выделении места под внутренние объекты! (которые могут использоваться при Sort и Hasj Join)
14 сен 12, 14:17    [13164977]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Fire83
Запрос на котором проблема не обновляет ничего в tempdb.


Еще как обновляет. И группировка, и такого рода подзапрос - все выполнятся в worktables, т.е. во времянках в tempdb.
14 сен 12, 14:57    [13165321]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Glory
Member

Откуда:
Сообщений: 104751
Fire83
Понятно что наверняка это HASH JOIN хочет что-то записать в tempdb, но повторюсь - общий объем данных с которыми идет работа не превышает 3GB, что в более чем 10 раз меньше того что показывает доступным sp_spaceused

План выполнения то что показывает ?
14 сен 12, 14:58    [13165339]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Глеб,

Все выполняется в памяти а если её не хватает то возможно хранение в tempdb промежуточных результатов для сортировки и хеш джоина.
Но это и не важно, вы же видите что я вставил 10 млн в темп дб без проблем. А теперь посмотрите не объемы данных в таблицах которые участвуют в запросе (первая колонка колич строк, вторая - размер в МБ).

FactyCost 7478020 2059
Dim1 103056 34
Dim2 155516 128

Glory,
Перед выполнением прошелся с UPDATE STAISTICS WITH FULLSCAN по всем таблицам которые учас в запросе.
В estimated вроде ничего криминального нету (actual по поянтным причинам не могу посмотреть :))...
Вроде ничего криминального не видно, ну да есть фул скан по таблице фактов, но ведь и результат будет около 1 млн строк
|--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(int,[globalagg1017],0)))
       |--Stream Aggregate(DEFINE:([globalagg1017]=SUM([partialagg1016])))
            |--Parallelism(Gather Streams)
                 |--Stream Aggregate(DEFINE:([partialagg1016]=Count(*)))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([cost].[Dim1SK], [j].[BK], [Expr1023]) OPTIMIZED WITH UNORDERED PREFETCH)
                           |--Hash Match(Inner Join, HASH:([afe].[Dim1SK])=([cost].[Dim1SK]))
                           |    |--Bitmap(HASH:([afe].[Dim1SK]), DEFINE:([Bitmap1022]))
                           |    |    |--Parallelism(Distribute Streams, Broadcast Partitioning)
                           |    |         |--Hash Match(Inner Join, HASH:([afe].[AFENumber])=([afe].[AFENumber]), RESIDUAL:([WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]=[WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]))
                           |    |              |--Parallelism(Gather Streams)
                           |    |              |    |--Filter(WHERE:([Expr1011]>(1)))
                           |    |              |         |--Compute Scalar(DEFINE:([Expr1011]=CONVERT_IMPLICIT(int,[Expr1021],0)))
                           |    |              |              |--Stream Aggregate(GROUP BY:([afe].[AFENumber], [j].[BK]) DEFINE:([Expr1021]=Count(*)))
                           |    |              |                   |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[AFENumber], [j].[BK]), ORDER BY:([afe].[AFENumber] ASC, [j].[BK] ASC))
                           |    |              |                        |--Sort(DISTINCT ORDER BY:([afe].[AFENumber] ASC, [j].[BK] ASC, [afe].[Dim1SK] ASC))
                           |    |              |                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[AFENumber], [j].[BK], [afe].[Dim1SK]))
                           |    |              |                                  |--Hash Match(Inner Join, HASH:([afe].[Dim1SK])=([j].[Dim1SK]))
                           |    |              |                                       |--Bitmap(HASH:([afe].[Dim1SK]), DEFINE:([Bitmap1020]))
                           |    |              |                                       |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([afe].[Dim1SK]))
                           |    |              |                                       |         |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [afe]), WHERE:([WPOTD].[dbo].[Dim1].[AFENumber] as [afe].[AFENumber]<>N'<undefined>'))
                           |    |              |                                       |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([j].[Dim1SK]))
                           |    |              |                                            |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [j]), WHERE:(PROBE([Bitmap1020],[WPOTD].[dbo].[Dim1].[Dim1SK] as [j].[Dim1SK],N'[IN ROW]')))
                           |    |              |--Clustered Index Scan(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [afe]))
                           |    |--Table Scan(OBJECT:([WPOTD].[dbo].[FactCost] AS [cost]), WHERE:(PROBE([Bitmap1022],[WPOTD].[dbo].[FactCost].[Dim1SK] as [cost].[Dim1SK],N'[IN ROW]')))
                           |--Clustered Index Seek(OBJECT:([WPOTD].[dbo].[Dim1].[PK_Dim1] AS [j]), SEEK:([j].[Dim1SK]=[WPOTD].[dbo].[FactCost].[Dim1SK] as [cost].[Dim1SK]),  WHERE:([WPOTD].[dbo].[Dim1].[BK] as [j].[BK]=[WPOTD].[dbo].[Dim1].[BK] as [j].[BK]) ORDERED FORWARD)


Сообщение было отредактировано: 14 сен 12, 15:27
14 сен 12, 15:25    [13165698]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Fire83,

Да кстати, если заменить подзапрос на временную таблицу и затем джоинить то все работает (видимо необходимость писать в tempdb уходит)
14 сен 12, 15:29    [13165735]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
komrad
Member

Откуда:
Сообщений: 5516
Fire83
Добрый день, коллеги!



С чем может быть связана проблема?


покажи результат

exec xp_fixeddrives 
go
use tempdb
go
SELECT db_name() [DB],file_id,name,type_desc, physical_name,size/128. [Size (MB)],
	CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. [Used Space (MB)],
             size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128. AS [Available Space (MB)]
FROM sys.database_files
go
14 сен 12, 17:14    [13166520]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
komrad,

Вот
drive MB free
C 11353
D 47550
E 10
G 180611
H 91495
J 176855
L 75236
P 165
T 988

DB file_id name type_desc physical_name Size (MB) Used Space (MB) Available Space (MB)
tempdb 1 tempdev ROWS E:\MSSQL$DCBIDEV\MSSQL10_50.DCBIDEV\MSSQL\Data\tempdb.mdf 34008.625000 3.187500 34005.437500
tempdb 2 templog LOG E:\MSSQL$DCBIDEV\MSSQL10_50.DCBIDEV\MSSQL\Data\templog.ldf 1918.437500 629.546875 1288.890625

Видно диск забит там где диск с tempdb - это, вероятно, потому что файл сразу проинициализирован на весь диск DBA.
14 сен 12, 17:33    [13166646]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Народ, хочу проверить проблема ли только этого запроса или любого который по каким то причинам пишет в tempdb (spool,hash join, sort).
Помогите написать неоптимальный запрос с хинтами(например на sys.objects) который будет спулить наверняка чтобы проверить предположение.
14 сен 12, 19:10    [13167184]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Fire83
Народ, хочу проверить проблема ли только этого запроса или любого который по каким то причинам пишет в tempdb (spool,hash join, sort).
Помогите написать неоптимальный запрос с хинтами(например на sys.objects) который будет спулить наверняка чтобы проверить предположение.
Сортировку добавьте в ваш sys.objects запрос и будет спул.
14 сен 12, 19:32    [13167282]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
Mind,

да ладно, где?

set showplan_text  on
go

select * 
from sys.objects 
order by create_date desc, type_desc desc
go

set showplan_text  off
go


автор
|--Sort(ORDER BY:([o].[created] DESC, [n].[name] DESC))
|--Hash Match(Right Outer Join, HASH:([n].[value])=([o].[type]), RESIDUAL:([mssqlsystemresource].[sys].[syspalnames].[value] as [n].[value]=[WPOTD].[sys].[sysschobjs].[type] as [o].[type]))
|--Clustered Index Seek(OBJECT:([mssqlsystemresource].[sys].[syspalnames].[cl] AS [n]), SEEK:([n].[class]='OBTY') ORDERED FORWARD)
|--Hash Match(Right Outer Join, HASH:([r].[depid])=([o].[id]))
|--Index Scan(OBJECT:([WPOTD].[sys].[syssingleobjrefs].[nc1] AS [r]), WHERE:([WPOTD].[sys].[syssingleobjrefs].[class] as [r].[class]=(97) AND [WPOTD].[sys].[syssingleobjrefs].[depsubid] as [r].[depsubid]=(0)))
|--Filter(WHERE:(has_access('CO',[WPOTD].[sys].[sysschobjs].[id] as [o].[id])=(1)))
|--Compute Scalar(DEFINE:([Expr1006]=CONVERT(bit,[WPOTD].[sys].[sysschobjs].[status] as [o].[status]&(1),0), [Expr1009]=CONVERT(bit,[WPOTD].[sys].[sysschobjs].[status] as [o].[status]&(16),0), [Expr1010]=CONVERT(bit,[WPOTD].[sys].[sysschobjs].[status] as [o].[status]&(64),0)))
|--Clustered Index Scan(OBJECT:([WPOTD].[sys].[sysschobjs].[clst] AS [o]), WHERE:([WPOTD].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [WPOTD].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)))
14 сен 12, 19:39    [13167300]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Mind
Member

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

да ладно, где?

[src]
set showplan_text on
go
ну и сколько у вас строк в sys.objects? Слабо добавить пару тройку cross joins?
И проблемы с hash join/sort вы в плане не увидите, тем более в таком урезанном текстовом плане, где даже количество строк не показано.
15 сен 12, 02:52    [13168369]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Fire83
Member

Откуда: Гомель-Минск
Сообщений: 474
В общем после выходных проблема сама исчезла. Отличие в том что пришло немного новых данных в таблицу фактов. Думал статистика, но тогда почему после ее обновление с FULL SCAN ничего не менялось в пятницу? В общем жалко, причин уже видимо не узнаю(
17 сен 12, 12:51    [13175250]     Ответить | Цитировать Сообщить модератору
 Re: Странная проблема с temp db  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Если вдруг кому все таки понадобится (для тестов или еще чего) запрос жрущий всю выделенную под него память, а потом еще и "сливающий" в tempdb (до тех пор пока место на диске не закончится), то вот самый простой пример:

select * 
from master.dbo.spt_values v1,master.dbo.spt_values v2, master.dbo.spt_values v3
order by v1.high, v2.low, v3.name

Кстати, если запустить 4 таких запроса, то можно наблюдать нехватку памяти. 4-ый запрос будет ждать пока память не освободится. Да и впринципе любые другие тяжелые запросы, которые не смогут втиснуться в Small Resource Pool по своим параметрам, тоже "попадут" на ожидания.

Картинка с другого сайта. Ну и естественно, не запускайте на продакшн Картинка с другого сайта.
23 сен 12, 00:03    [13208222]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить