Mengubah Tabel Memory-Optimized

Artikel
06/13/2017

Melakukan operasi UBAH pada tabel yang dioptimalkan memori tidak didukung. Ini termasuk operasi seperti mengubah bucket_count, menambahkan atau menghapus indeks, dan menambahkan atau menghapus kolom. Topik ini menyediakan panduan tentang cara memperbarui tabel yang dioptimalkan memori.

Memperbarui Definisi Tabel Memory-Optimized

Memperbarui definisi tabel yang dioptimalkan memori mengharuskan Anda membuat tabel baru dengan definisi tabel yang diperbarui, menyalin data ke tabel baru, dan mulai menggunakan tabel baru. Kecuali tabel bersifat baca-saja, ini memerlukan penghentian beban kerja pada tabel, untuk memastikan tidak ada perubahan yang dilakukan pada tabel saat salinan data dilakukan.

Prosedur berikut menguraikan langkah-langkah yang diperlukan untuk memperbarui tabel. Dalam contoh ini, pembaruan menambahkan indeks. Prosedur ini mempertahankan nama tabel dan memerlukan dua operasi penyalinan data: sekali ke tabel sementara, dan sekali ke tabel baru. Mengubah bucket_count indeks atau menambahkan atau menghapus kolom dilakukan dengan cara yang sama.

Hentikan beban kerja pada tabel.
Buat skrip untuk tabel dan tambahkan indeks baru ke skrip.

Hasilkan skrip untuk objek yang terikat skema (terutama prosedur tersimpan yang dikompilasi secara asli) yang merujuk T dan izinnya.

Objek terikat skema yang mereferensikan tabel dapat ditemukan menggunakan kueri berikut:

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;

Izin prosedur tersimpan dapat ditulis menggunakan Transact-SQL berikut:

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

Buat salinan tabel dan salin data dari tabel asli ke salinan tabel. Salinan dapat dibuat menggunakan Transact-SQL¹ berikut.
```
select * into dbo.T_copy from dbo.T  
```
Jika ada cukup memori yang tersedia, T_copy bisa menjadi tabel yang dioptimalkan memori, yang membuat salinan data lebih cepat.²
Hilangkan objek yang terikat skema yang mereferensikan tabel asli.
Jatuhkan tabel asli.
Buat tabel baru (T) dengan skrip yang berisi indeks baru.
Salin data dari T_copy ke T.
Buat ulang objek yang terikat skema referensi dan terapkan izin.
Mulai beban kerja pada T.

¹ Perhatikan bahwa T_copy dipertahankan ke disk dalam contoh ini. Jika cadangan T tersedia, T_copy bisa berupa tabel sementara atau tidak tahan lama.

² Harus ada cukup memori untuk T_copy. Memori tidak segera dikosongkan pada DROP TABLE. Jika T_copy memori dioptimalkan, perlu ada cukup memori untuk dua salinan Ttambahan . Jika T_copy adalah tabel berbasis disk, hanya perlu cukup memori untuk satu salinan Ttambahan , karena pengumpul sampah perlu mengejar ketinggalan setelah menghilangkan versi lama .T

Mengubah Skema (PowerShell)

Skrip PowerShell berikut menyiapkan dan menghasilkan perubahan skema dengan membuat skrip tabel dan izin terkait.

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

Skrip ini mengambil sebagai argumen tabel, dan membuat skrip objek dan izinnya serta mereferensikan objek yang terikat skema dan izinnya di folder saat ini. Total 7 skrip dihasilkan untuk memperbarui skema tabel input:

Menyalin data ke tabel sementara (timbunan).
Hilangkan objek yang terikat skema yang mereferensikan tabel.
Jatuhkan mejanya.
Buat ulang tabel dengan skema baru dan terapkan kembali izin.
Salin data dari tabel sementara ke tabel yang dibuat ulang.
Buat ulang objek yang terikat skema yang mereferensikan tabel dan izinnya.
Jatuhkan tabel sementara.

Skrip untuk langkah 4 harus diperbarui untuk mencerminkan perubahan skema yang diinginkan. Jika ada perubahan dalam kolom tabel, skrip untuk langkah 5 (salin data dari tabel sementara) dan 6 (buat ulang prosedur tersimpan) harus diperbarui seperlunya.

# 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 ""

Skrip PowerShell berikut menjalankan perubahan skema yang diskrip dalam sampel sebelumnya. Skrip ini mengambil sebagai tabel argumen, dan menjalankan skrip perubahan skema yang dihasilkan untuk tabel tersebut dan prosedur tersimpan terkait.

Penggunaan: table_name server_name**db_nameschema_nameexecute_schema_change.ps1

# 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 ""

Lihat juga

Tabel yang Dioptimalkan Memori

Bagikan melalui

Mengubah Tabel Memory-Optimized

Memperbarui Definisi Tabel Memory-Optimized

Mengubah Skema (PowerShell)

Lihat juga

Sumber Daya Tambahan: