В чем разница между временной таблицей и табличной переменной в SQL Server?


404

Это, кажется, область с довольно многими мифами и противоречивыми взглядами.

Так в чем же разница между табличной переменной и локальной временной таблицей в SQL Server?

+4

Связанный вопрос по переполнению стека: [Когда я должен использовать переменную таблицы против временной таблицы в SQL Server?] (Http://stackoverflow.com/q/11857789/73226) 07 июл. 162016-07-07 04:30:51

618

содержание

Contents

Предостережение

В этом ответе рассматриваются «классические» табличные переменные, представленные в SQL Server 2000. SQL Server 2014 в памяти OLTP представляет оптимизированные для памяти типы таблиц.Экземпляры табличных переменных во многом отличаются от тех, которые обсуждались ниже!(more details).

Место хранения

Нет разницы.Оба хранятся вtempdb,

Я видел, что это предположило, что для табличных переменных это не всегда так, но это можно проверить из приведенного ниже

DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Пример результатов (показывает местоположение вtempdb2 строки сохранены)

File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Логическое Расположение

@table_variablesвести себя более как если бы они были частью текущей базы данных, чем#tempтаблицы делают.Для табличных переменных (с 2005 года) сопоставления столбцов, если не указано явно, будут сопоставлениями текущей базы данных, тогда как для#tempтаблицы будут использовать сопоставление по умолчаниюtempdb(More details).Кроме того, пользовательские типы данных и коллекции XML должны быть в базе данных tempdb для использования#tempтаблицы, но переменные таблицы могут использовать их из текущей базы данных (Source).

SQL Server 2012 представляет автономные базы данных.the behavior of temporary tables in these differs(ч/т Аарон)

В отдельной базе данных временные таблицы сопоставляются в сопоставлении содержащейся базы данных.

  • Все метаданные, связанные с временными таблицами (например, имена таблиц и столбцов, индексы и т. Д.), Будут находиться в каталоге сортировки.
  • Именованные ограничения не могут использоваться во временных таблицах.
  • Временные таблицы могут не ссылаться на пользовательские типы, коллекции схем XML или пользовательские функции.

Видимость в разных сферах

@table_variablesдоступен только в пределах пакета и области, в которой они объявлены.#temp_tablesдоступны в дочерних пакетах (вложенные триггеры, процедуры,execзвонки).#temp_tablesсоздано во внешней области видимости (@@NESTLEVEL=0) может также охватывать пакеты, поскольку они сохраняются до окончания сеанса.Ни один тип объекта не может быть создан в дочернем пакете и доступен в области вызова, как обсуждено далее (глобальный##tempтаблицыМожнобудь хоть).

Продолжительность жизни

@table_variablesсоздаются неявно, когда партия, содержащаяDECLARE @.. TABLEоператор выполняется (перед выполнением любого пользовательского кода в этом пакете) и неявно отбрасывается в конце.

Хотя синтаксический анализатор не позволит вам попробовать и использовать переменную таблицы доDECLAREЗаявление о неявном творении можно увидеть ниже.

IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tablesсоздаются явно, когда TSQLCREATE TABLEоператор встречается и может быть отброшен явно сDROP TABLEили будет неявно отброшен при завершении пакета (если он создан в дочернем пакете с@@NESTLEVEL > 0) или когда сессия заканчивается иначе.

NB: в хранимых процедурах оба типа объектовcan be cachedвместо многократного создания и удаления новых таблиц.Существуют ограничения на то, когда может происходить это кэширование, которые можно нарушать для#temp_tablesно какие ограничения на@table_variablesпредотвратить в любом случае.Затраты на обслуживание кэшированных#tempтаблицынемногобольше, чем для табличных переменныхas illustrated here,

Метаданные объекта

По сути, это одинаково для обоих типов объектов.Он хранится в системных базовых таблицах вtempdb,Это более просто увидеть#tempтаблица однако какOBJECT_ID('tempdb..#T')может использоваться для ввода в системные таблицы, и внутренне сгенерированное имя более тесно связано с именем, определенным вCREATE TABLEзаявление.Для табличных переменныхobject_idфункция не работает, и внутреннее имя полностью сгенерировано системой без связи с именем переменной.Ниже показано, что метаданные все еще там, однако вводя (надеюсь уникальное) имя столбца.Для таблиц без уникальных имен столбцов идентификатор_объекта можно определить с помощьюDBCC PAGEдо тех пор, пока они не пусты.

/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B) 
     WITH (FILLFACTOR = 80, 
        IGNORE_DUP_KEY = ON, 
        DATA_COMPRESSION = PAGE, 
        ALLOW_ROW_LOCKS=ON, 
        ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM  tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
        ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
        ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
        ON c.object_id = p.object_id
WHERE c.name = 'dba.se'

Выход

Duplicate key was ignored.

    +-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| object_id |  name  | rows |   type_desc   | total_pages | used_pages | data_pages | data_compression_desc |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | PAGE         |
| 574625090 | #22401542 |  13 | LOB_DATA     |     24 |     19 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | ROW_OVERFLOW_DATA |     16 |     14 |     0 | PAGE         |
| 574625090 | #22401542 |  13 | IN_ROW_DATA    |      2 |     2 |     1 | NONE         |
+-----------+-----------+------+-------------------+-------------+------------+------------+-----------------------+

операции

Операции на@table_variablesвыполняются как системные транзакции, не зависящие от какой-либо внешней пользовательской транзакции, тогда как эквивалент#tempТабличные операции будут выполняться как часть самой пользовательской транзакции.По этой причинеROLLBACKКоманда повлияет на#tempстол, но оставь@table_variableнетронутым.

DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

логирование

Оба генерируют записи журнала вtempdbЖурнал транзакций.Распространенным заблуждением является то, что это не относится к переменным таблицы, поэтому сценарий, демонстрирующий это ниже, объявляет переменную таблицы, добавляет пару строк, затем обновляет их и удаляет их.

Поскольку переменная таблицы создается и неявно отбрасывается в начале и конце пакета, необходимо использовать несколько пакетов, чтобы увидеть полное ведение журнала.

USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141), 
       (2, 0x41414141414141414141)

UPDATE @T
SET  B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id 
FROM  sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
        ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
        ON c.object_id = p.object_id
WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM  fn_dblog(NULL, NULL)
WHERE AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
      AS (SELECT Operation,
           Context,
           CASE
            WHEN AllocUnitId = @allocId THEN 'Table Variable'
            WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
            ELSE AllocUnitName
           END AS AllocUnitName,
           [Log Record Length]
        FROM  fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
              WHEN GROUPING(Operation) = 1 THEN 'Total'
              ELSE Operation
             END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM  T
GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER BY GROUPING(Operation),
        AllocUnitName 

Возвращает

Детальный просмотр

Screenshot of results

Сводное представление (включает ведение журнала для неявного удаления и системных базовых таблиц)

Screenshot of results

As far as I've been able to discernоперации на обоих генерируют примерно одинаковое количество записей.

Покаколичествоведения журнала очень похожи одно важное отличие состоит в том, что записи журнала, связанные с#tempтаблицы не могут быть очищены до тех пор, пока любая содержащая пользовательская транзакция не завершит настолько длительную транзакцию, которая в какой-то момент записывает в#tempтаблицы будут препятствовать усечению журнала вtempdbв то время как автономные транзакции, порожденные для табличных переменных, - нет.

Табличные переменные не поддерживаютTRUNCATEэто может быть связано с недостатком ведения журнала, когда требуется удалить все строки из таблицы (хотя для очень маленьких таблицDELETEcan work out better anyway)

мощность

Многие планы выполнения, включающие в себя переменные таблицы, будут показывать одну строку, оцениваемую как выходные данные из них.Проверка свойств табличной переменной показывает, что SQL Server считает, что табличная переменная имеетнульряды (почему он оценивает, что 1 строка будет выведена из таблицы с нулевой строкой, объясняется @Paul Whitehere).

Однако результаты, показанные в предыдущем разделе, показывают точнуюrowsсчитать вsys.partitions,Проблема в том, что в большинстве случаев операторы, ссылающиеся на переменные таблицы, компилируются, пока таблица пуста.Если заявление (пере) составлено после@table_variableвместо этого это будет использоваться для кардинальности таблицы (это может произойти из-за явногоrecompileили, возможно, потому что оператор также ссылается на другой объект, который вызывает отложенную компиляцию или перекомпиляцию.)

DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

План показывает точное количество строк после отложенной компиляции.

Shows accurate row count

В SQL Server 2012 SP2 введен флаг трассировки 2453.Более подробная информация в разделе «Реляционный двигатель»here,

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

Нет столбцов статистики

Наличие более точного количества элементов в таблице не означает, что расчетное количество строк будет более точным (если только не выполнить операцию со всеми строками в таблице).SQL Server вообще не поддерживает статистику столбцов для табличных переменных, поэтому будет использовать догадки, основанные на предикате сравнения (например, 10% таблицы будет возвращено для=против неуникального столбца или 30% для>сравнение).В отличие от столбца статистикиявляютсяподдерживается для#tempстолы.

SQL Server ведет подсчет количества изменений, внесенных в каждый столбец.Если количество изменений с момента составления плана превышает порог перекомпиляции (RT), то план будет перекомпилирован и статистика обновлена.RT зависит от типа и размера стола.

ОтPlan Caching in SQL Server 2008

RT рассчитывается следующим образом.(n относится к количеству элементов таблицы при составлении плана запроса.)

Постоянный стол
- Если n <= 500, RT = 500.
- Если n> 500, RT = 500 + 0,20 * n.

Временный стол
- Если n <6, RT = 6.
- Если 6 <= n <= 500, RT = 500.
- Если n> 500, RT = 500 + 0,20 * n.
Табличная переменная
- RT не существует.Поэтому перекомпиляции не происходят из-за изменений в кардинальности табличных переменных.(Но см. Примечание о TF 2453 ниже)

KEEP PLANподсказка может быть использована для установки RT для#tempтаблицы такие же, как для постоянных таблиц.

Общий эффект всего этого заключается в том, что часто планы выполнения, сгенерированные для#tempтаблицы на порядки лучше, чем для@table_variablesкогда задействовано много строк, SQL Server имеет лучшую информацию для работы.

NB1. Переменные таблицы не имеют статистики, но могут по-прежнему вызывать событие перекомпиляции «Статистика изменена» под флагом трассировки 2453 (не применяется для «тривиальных» планов). дополнительный, еслиN=0 -> RT = 1,т.е. все операторы, скомпилированные, когда переменная таблицы пуста, в конечном итоге получат перекомпиляцию и исправятTableCardinalityв первый раз они выполняются, когда не пусто.Количество элементов таблицы времени компиляции сохраняется в плане, и если оператор выполняется снова с той же мощностью (либо из-за потока управляющих операторов, либо из-за повторного использования кэшированного плана), перекомпиляция не происходит.

NB2. Для кэшированных временных таблиц в хранимых процедурах история перекомпиляции гораздо сложнее, чем описано выше.УвидетьTemporary Tables in Stored Proceduresдля всех кровавых деталей.

перекомпилирует

А также модификации, основанные на перекомпиляции, описанные выше#tempтаблицы также могут быть связаны сadditional compilesпросто потому, что они разрешают операции, которые запрещены для табличных переменных, запускающих компиляцию (например, изменения DDLCREATE INDEX,ALTER TABLE)

Блокировка

Этоhas been statedчто табличные переменные не участвуют в блокировке.Это не вариант.Выполнение приведенных ниже выводов на вкладку сообщений SSMS с подробностями блокировок, снятых и снятых для оператора вставки.

DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

Для запросов, которыеSELECTиз табличных переменных Пол Уайт указывает в комментариях, что они автоматически приходят с неявнымNOLOCKнамек.Это показано ниже

DECLARE @T TABLE(X INT); 

SELECT X
FROM @T 
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

Выход

*** Output Tree: (trivial plan) ***

       PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )

Однако влияние этого на блокировку может быть незначительным.

SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))


DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
            [Filler] [char](8000) NULL,
            PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Ни один из этих возвращаемых результатов не приводит к порядку ключа индекса, указывающему, что SQL Server использовалallocation ordered scanдля обоих.

Я запустил вышеупомянутый скрипт дважды, и результаты для второго запуска ниже

Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0 

Вывод блокировки для табличной переменной действительно крайне минимален, поскольку SQL Server просто получает блокировку стабильности схемы для объекта.Но для#tempтаблица почти так же легка, что вынимает уровень объектаSзамок.NOLOCKподсказка илиREAD UNCOMMITTEDКонечно, уровень изоляции может быть указан явно при работе с#tempстолы также.

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

  --BEGIN TRAN;  

     CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

     INSERT INTO #T (X) VALUES(1) 

     SELECT CASE resource_type
          WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
          WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                         FROM tempdb.sys.allocation_units a 
                         JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                         WHERE a.allocation_unit_id = resource_associated_entity_id)
          WHEN 'DATABASE' THEN DB_NAME(resource_database_id)                   
          ELSE (SELECT OBJECT_NAME(object_id, 2)
             FROM  tempdb.sys.partitions
             WHERE partition_id = resource_associated_entity_id)
         END AS object_name,
         *
     FROM  sys.dm_tran_locks
     WHERE request_session_id = @@SPID

     DROP TABLE #T

     -- ROLLBACK 

при запуске вне явной пользовательской транзакции для обоих случаев при проверке возвращается только блокировкаsys.dm_tran_locksэто общая блокировка наDATABASE,

На комментарийBEGIN TRAN ... ROLLBACKВозвращается 26 строк, показывающих, что блокировки удерживаются как на самом объекте, так и на строках системной таблицы, чтобы обеспечить откат и предотвратить чтение незафиксированных данных другими транзакциями.Операция с эквивалентной табличной переменной не подлежит откату с пользовательской транзакцией и не нуждается в удержании этих блокировок, чтобы мы могли проверить следующую инструкцию, но отслеживание блокировок, полученных и выпущенных в Profiler, или использование флага трассировки 1200 показывает, что множество событий блокировки все еще работают происходят.

Индексы

Для версий, предшествующих SQL Server 2014, индексы могут создаваться неявно только для табличных переменных как побочный эффект добавления уникального ограничения или первичного ключа.Это, конечно, означает, что поддерживаются только уникальные индексы.Однако неуникальный некластеризованный индекс в таблице с уникальным кластеризованным индексом можно смоделировать, просто объявив егоUNIQUE NONCLUSTEREDи добавление ключа CI в конец требуемого ключа NCI (SQL Server будетdo this behind the scenes anywayдаже если можно указать неуникальный NCI)

Как показали ранее различныеindex_options могут быть указаны в объявлении ограничения, включаяDATA_COMPRESSION,IGNORE_DUP_KEY, а такжеFILLFACTOR(хотя нет смысла устанавливать его, так как это будет иметь значение только для перестроения индекса, и вы не сможете перестроить индексы для табличных переменных!)

Кроме того, переменные таблицы не поддерживаютINCLUDEd столбцы, отфильтрованные индексы (до 2016 года) или секционирование,#tempтаблицы делают (схема разбиения должна быть создана вtempdb).

Индексы в SQL Server 2014

Неуникальные индексы могут быть объявлены встроенными в определении табличной переменной в SQL Server 2014. Пример синтаксиса для этого приведен ниже.

DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Индексы в SQL Server 2016

Из CTP 3.1 теперь можно объявлять отфильтрованные индексы для табличных переменных.По RTM этоможетбыть в том случае, если включены столбцы также допускаются, хотя ониwill likely not make it into SQL16 due to resource constraints

DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

параллелизм

Запросы, которые вставляются в (или иным образом изменяются)@table_variablesне может иметь параллельный план,#temp_tablesне ограничены таким образом.

Существует очевидный обходной путь в том, что переписывание позволяетSELECTчасть, чтобы иметь место параллельно, но это в конечном итоге с использованием скрытой временной таблицы(behind the scenes)

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

В запросах такого ограничения нетВыбратьиз табличных переменныхas illustrated in my answer here

Другие функциональные различия

  • #temp_tablesне может использоваться внутри функции.@table_variablesможет использоваться внутри UDFs скалярной таблицы или таблицы с несколькими утверждениями.
  • @table_variablesне может иметь именованных ограничений.
  • @table_variablesне может бытьSELECT-edINTO,ALTER-ed,TRUNCATEг или быть цельюDBCCтакие команды, какDBCC CHECKIDENTили изSET IDENTITY INSERTи не поддерживают табличные подсказки, такие какWITH (FORCESCAN)
  • CHECKограничения на табличные переменные не рассматриваются оптимизатором для упрощения, подразумеваемых предикатов или обнаружения противоречий.
  • Переменные таблицы не соответствуют критериямrowset sharing optimisationЭто означает, что удаление и обновление планов против них может столкнуться с дополнительнымиPAGELATCH_EXждет.(Example)

Только память?

Как указано в начале, оба хранятся на страницах вtempdb,Однако я не говорил, было ли какое-либо различие в поведении, когда дело доходит до записи этих страниц на диск.

Я провел небольшое тестирование по этому вопросу сейчас и до сих пор не видел такой разницы.В конкретном тесте, который я провел на своем экземпляре SQL Server, 250 страниц, по-видимому, являются точкой отсечения до того, как файл данных будет записан.

Примечание: приведенное ниже поведение больше не происходит в SQL Server 2014 илиSQL Server 2012 SP1/CU10 or SP2/CU1писатель больше не хочет записывать страницы на диск.Подробнее об этом изменении наSQL Server 2014: tempdb Hidden Performance Gem,

Запуск приведенного ниже скрипта

CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

И мониторинг пишет вtempdbфайла данных с Process Monitor я не видел ни одного (кроме тех, которые иногда попадали на страницу загрузки базы данных со смещением 73 728).После изменения250в251Я начал видеть записи, как показано ниже.

ProcMon

На приведенном выше снимке экрана показаны записи 5 * 32 страниц и одна запись одной страницы, указывающая, что 161 страница была записана на диск.Я получил ту же точку отсечения 250 страниц при тестировании с табличными переменными тоже.Сценарий ниже показывает это по-другому, глядя наsys.dm_os_buffer_descriptors

DECLARE @T TABLE (
    X    INT,
    [dba.se] CHAR(8000) NULL)

INSERT INTO @T
         (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM  master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                    FROM  tempdb.sys.partitions AS p
                   INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                        ON p.hobt_id = a.container_id
                       INNER JOIN tempdb.sys.columns AS c
                        ON c.object_id = p.object_id
                    WHERE c.name = 'dba.se')
GROUP BY is_modified 

Результаты

is_modified page_count
----------- -----------
0      192
1      61

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

На незанятом сервере сmax server memoryустановлен в2000 MBа такжеDBCC MEMORYSTATUSсоздание отчетов по страницам пула буферов, выделенных примерно в 1 843 000 КБ (около 23 000 страниц), которые я вставил в таблицы выше в пакетах по 1000 строк/страниц и для каждой записанной итерации.

SELECT Count(*)
FROM  sys.dm_os_buffer_descriptors
WHERE database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE' 

И переменная таблицы и#tempТаблица дала почти идентичные графики и сумела в значительной степени максимизировать буферный пул, прежде чем дошла до того, что они не были полностью сохранены в памяти, поэтому не существует каких-либо особых ограничений на то, сколько памяти может потреблять.

Pages in Buffer Pool


35

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

  1. Таблицы #temp по умолчанию используют параметры сортировки по умолчанию для экземпляра SQL Server.Поэтому, если не указано иное, вы можете столкнуться с проблемами при сравнении или обновлении значений между таблицами #temp и таблицами базы данных, если masterdb имеет параметры сортировки, отличные от базы данных.См .: http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
  2. Полностью основанный на личном опыте, доступная память, кажется, влияет на то, что работает лучше.MSDN рекомендует использовать табличные переменные для хранения меньших наборов результатов, но в большинстве случаев разница даже не заметна.Однако в больших наборах становится очевидным, что в некоторых случаях табличные переменные занимают гораздо больше памяти и могут замедлить запрос до сканирования.
+5

Также обратите внимание, что параметры сортировки в таблицах #temp * могут * наследовать параметры сортировки вызывающей базы данных, если вы используете SQL Server 2012 и база данных содержится. 29 янв. 132013-01-29 19:51:03

  0

Разъяснение №2 для маленьких и больших наборов http://stackoverflow.com/a/14465163/5224021 08 фев. 172017-02-08 14:56:07