메모리 최적화 테이블에서 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-SQL1을 사용하여 복사본을 만들 수 있습니다.
select * into dbo.T_copy from dbo.T사용 가능한 메모리
T_copy가 충분한 경우 메모리 최적화 테이블일 수 있으므로 데이터 복사 속도가 빨라집니다.2개원래 테이블을 참조하는 스키마 바인딩된 개체를 삭제합니다.
원래 테이블을 삭제합니다.
새 인덱스가 포함된 스크립트를 사용하여 새 테이블(
T)을 만듭니다.T_copy에서T로 데이터를 복사합니다.참조하는 스키마 바인딩된 개체를 다시 만들고 사용 권한을 적용합니다.
에서 워크로드를 시작합니다
T.
1T_copy 이 예제에서는 디스크에 유지됩니다. 백업 T 을 사용할 수 있는 경우 임시 또는 비지속성 테이블일 수 있습니다 T_copy .
2 에 충분한 T_copy메모리가 있어야 합니다. 메모리가 DROP TABLE에서 즉시 해제되지 않습니다.
T_copy가 메모리 최적화된 경우, T의 추가 복사본 두 개에 충분한 메모리가 필요합니다. 디스크 기반 테이블인 경우, 이전 버전 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_nameschema_nametable_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 ""