Изменение таблиц Memory-Optimized

2017-06-13

Выполнение операций ALTER в таблицах, оптимизированных для памяти, не поддерживается. К ним относятся такие операции, как изменение bucket_count, добавление или удаление индекса, а также добавление или удаление столбца. В этом разделе содержатся рекомендации по обновлению оптимизированных для памяти таблиц.

Обновление определения таблицы Memory-Optimized

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

В следующей процедуре описаны шаги, необходимые для обновления таблицы. В этом примере обновление добавляет индекс. Эта процедура сохраняет имя таблицы и требует двух операций копирования данных: один раз в временную таблицу и один раз в новую таблицу. Изменение bucket_count индекса или добавления или удаления столбца выполняется так же.

Остановите рабочую нагрузку в таблице.
Создайте скрипт для таблицы и добавьте новый индекс в скрипт.

Создайте скрипт для объектов, привязанных к схеме (в основном скомпилированных хранимых процедур), ссылающихся на T и их разрешения.

Объекты, связанные с схемой, ссылающиеся на таблицу, можно найти с помощью следующего запроса:

declare @t nvarchar(255) = N'<table name>'  

select r.referencing_schema_name, r.referencing_entity_name  
from sys.dm_sql_referencing_entities (@t, 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id  
where r.is_caller_dependent = 0 and m.is_schema_bound=1;

Разрешения хранимой процедуры можно создать с помощью следующего кода Transact-SQL:

declare @sp nvarchar(255) = N'<procedure name>'  
declare @permissions nvarchar(max) = N''  

select @permissions += dp.state_desc + N' ' + dp.permission_name + N' ON ' +   
   quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) + N' TO ' +  
   quotename(u.name) + N'; ' + char(13)  
from sys.database_permissions as dp  

join sys.database_principals as u  
   on u.principal_id = dp.grantee_principal_id  

join sys.objects as o  
   on o.object_id = dp.major_id  
where dp.class = 1 /* object */  
   and dp.minor_id = 0 and o.object_id=object_id(@sp);  

select @permissions

Создайте копию таблицы и скопируйте данные из исходной таблицы в копию таблицы. Копию можно создать с помощью следующей инструкции Transact-SQL¹.
```
select * into dbo.T_copy from dbo.T  
```
Если достаточно доступной памяти, T_copy может быть оптимизированная для памяти таблица, что ускоряет копирование данных.²
Удалите объекты, связанные с схемой, ссылающиеся на исходную таблицу.
Удалите исходную таблицу.
Создайте новую таблицу (T) с скриптом, содержащим новый индекс.
Скопируйте данные из T_copy в T.
Повторно создайте объекты, связанные с схемой, и примените разрешения.
Запустите рабочую нагрузку T.

¹ Обратите внимание, что T_copy в этом примере сохраняется на диске. Если резервная копия T доступна, T_copy может быть временной или не устойчивой таблицей.

² Должно быть достаточно памяти для T_copy. Память не освобождается немедленно DROP TABLE. Если T_copy оптимизирована по памяти, требуется достаточно памяти для двух дополнительных копий T. Если T_copy является таблицей, основанной на диске, необходимо иметь достаточно памяти только для одной дополнительной копии T, так как сборщику мусора нужно наверстать упущенное после удаления старой версии T.

Изменение схемы (PowerShell)

Следующие скрипты PowerShell подготавливают и создают изменения схемы путем создания скриптов таблицы и связанных разрешений.

prepare_schema_change.ps1 <serverName> <databaseName> <schemaName> <tableName>

Этот скрипт принимает в качестве аргументов таблицу, создает скрипт для объекта и его разрешений, а также для объектов, связанных со схемой, и их разрешений в текущей папке. В общей сложности создаются 7 скриптов для обновления схемы входной таблицы:

Копирование данных во временную таблицу (куча).
Удаление привязанных к схеме объектов, ссылающихся на таблицу.
Удалите таблицу.
Создайте таблицу с новой схемой и повторно примените разрешения.
Скопируйте данные из временной таблицы в повторно созданную таблицу.
Повторно создайте объекты, привязанные к схеме, которые ссылаются на таблицу, а также воспроизведите их разрешения.
Удалите временную таблицу.

Скрипт для шага 4 должен быть обновлен, чтобы отразить требуемые изменения схемы. Если в столбцах таблицы есть какие-либо изменения, скрипты для шагов 5 (копирование данных из временной таблицы) и 6 (повторное создание хранимых процедур) должны обновляться по мере необходимости.

# Prepare for schema changes by scripting out the table, as well as associated permissions
# Usage: prepare_schema_change.ps1 server_name db_name schema_name table_name  
# stop execution once an error occurs  
$ErrorActionPreference="Stop"  
  
if($args.Count -le 3)  
{  
   throw "Usage prepare_schema_change.ps1 server_name db_name schema_name table_name"  
}  
  
$servername = $args[0]  
$database = $args[1]  
$schema = $args[2]  
$object = $args[3]  
  
$object_heap = "$object$(Get-Random)"  
  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null  
  
$server =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)  
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)  
  
## initialize table variable  
$tableUrn = $server.Databases[$database].Tables[$object, $schema]  
if($tableUrn.Count -eq 0)  
{  
   throw "Table or database not found"  
}  
  
## initialize scripting object  
$scriptingOptions = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
$scriptingOptions.Permissions = $True  
$scriptingOptions.ScriptDrops = $True  
  
$scripter.Options = $scriptingOptions;  
  
Write-Host "(1) Scripting SELECT INTO $object_heap for table [$object] to 1_copy_to_heap_for_$schema`_$object.sql"  
Echo "SELECT * INTO $schema.$object_heap FROM $schema.$object WITH (SNAPSHOT)" | Out-File "1_copy_to_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(2) Scripting DROP for procs schema-bound to [$object] 2_drop_procs_$schema`_$object.sql"  
## query referencing schema-bound objects  
$dt = $server.Databases[$database].ExecuteWithResults("select r.referencing_schema_name, r.referencing_entity_name  
from sys.dm_sql_referencing_entities ('$schema.$object', 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id  
where r.is_caller_dependent = 0 and m.is_schema_bound=1;")  
  
## initialize out file  
Echo "" | Out-File "2_drop_procs_$schema`_$object.sql"  
## loop through schema-bound objects  
ForEach ($t In $dt.Tables)  
{    
   ForEach ($r In $t.Rows)  
   {    
      ## script object   
      $so =  $server.Databases[$database].StoredProcedures[$r[1], $r[0]]  
      $scripter.Script($so) | Out-File -Append "2_drop_procs_$schema`_$object.sql"  
   }  
}  
Write-Host "--done--"  
Write-Host ""  
Write-Host "(3) Scripting DROP table for [$object] to 3_drop_table_$schema`_$object.sql"
$scripter.Script($tableUrn) | Out-File "3_drop_table_$schema`_$object.sql";
Write-Host "--done--"  
Write-Host ""  
  
## now script creates  
$scriptingOptions.ScriptDrops = $False  
  
Write-Host "(4) Scripting CREATE table and permissions for [$object] to !please_edit_4_create_table_$schema`_$object.sql"  
Write-Host "***** rename this script to 4_create_table.sql after completing the updates to the schema"
$scripter.Script($tableUrn) | Out-File "!please_edit_4_create_table_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(5) Scripting INSERT INTO table from heap and UPDATE STATISTICS for [$object] to 5_copy_from_heap_$schema`_$object.sql"  
Write-Host "[update this script if columns are added to or removed from the table]"  
Echo "INSERT INTO [$schema].[$object] SELECT * FROM [$schema].[$object_heap]; UPDATE STATISTICS [$schema].[$object] WITH FULLSCAN, NORECOMPUTE" | Out-File "5_copy_from_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(6) Scripting CREATE PROC and permissions for procedures schema-bound to [$object] to 6_create_procs_$schema`_$object.sql"  
Write-Host "[update the procedure definitions if columns are renamed or removed]"  
## initialize out file  
Echo "" | Out-File "6_create_procs_$schema`_$object.sql"  
## loop through schema-bound objects  
ForEach ($t In $dt.Tables)  
{    
   ForEach ($r In $t.Rows)  
   {    
      ## script the schema-bound object  
      $so =  $server.Databases[$database].StoredProcedures[$r[1], $r[0]]  
      ForEach($s In $scripter.Script($so))  
        {  
            Echo $s | Out-File -Append "6_create_procs_$schema`_$object.sql"  
            Echo "GO" | Out-File -Append "6_create_procs_$schema`_$object.sql"  
        }  
   }  
}  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(7) Scripting DROP $object_heap to 7_drop_heap_$schema`_$object.sql"  
Echo "DROP TABLE $schema.$object_heap" | Out-File "7_drop_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""

Следующий скрипт PowerShell выполняет изменения схемы, которые были написаны в предыдущем примере. Этот скрипт принимает в качестве аргумента таблицу и выполняет скрипты изменений схемы, созданные для этой таблицы и связанных хранимых процедур.

Использование: execute_schema_change.ps1 server_name**db_name table_nameschema_name

# stop execution once an error occurs  
$ErrorActionPreference="Stop"  
  
if($args.Count -le 3)  
{  
   throw "Usage execute_schema_change.ps1 server_name db_name schema_name table_name"  
}  
  
$servername = $args[0]  
$database = $args[1]  
$schema = $args[2]  
$object = $args[3]  
  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null  
  
$server =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)  
$database = $server.Databases[$database]  
$table = $database.Tables[$object, $schema]  
if($table.Count -eq 0)  
{  
   throw "Table or database not found"  
}  
  
$1 = Get-Item "1_copy_to_heap_$schema`_$object.sql"  
$2 = Get-Item "2_drop_procs_$schema`_$object.sql"  
$3 = Get-Item "3_drop_table_$schema`_$object.sql"  
$4 = Get-Item "4_create_table_$schema`_$object.sql"  
$5 = Get-Item "5_copy_from_heap_$schema`_$object.sql"  
$6 = Get-Item "6_create_procs_$schema`_$object.sql"  
$7 = Get-Item "7_drop_heap_$schema`_$object.sql"  
  
Write-Host "(1) Running SELECT INTO heap for table [$object] from 1_copy_to_heap_for_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $1.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(2) Running DROP for procs schema-bound from [$object] 2_drop_procs_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $2.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(3) Running DROP table for [$object] to 4_drop_table_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $3.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(4) Running CREATE table and permissions for [$object] from 4_create_table_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $4.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(5) Running INSERT INTO table from heap for [$object] and UPDATE STATISTICS from 5_copy_from_heap_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $5.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(6) Running CREATE PROC and permissions for procedures schema-bound to [$object] from 6_create_procs_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $6.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(7) Running DROP heap from 7_drop_heap_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $7.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""

См. также

Таблицы, оптимизированные для памяти

Поделиться через

Изменение таблиц Memory-Optimized

Обновление определения таблицы Memory-Optimized

Изменение схемы (PowerShell)

См. также

Дополнительные ресурсы