W trakcie optymalizacji baz danych pod kątem indeksów należałoby się pozbyć nieużywanych, kosztowych bądź zduplikowanych indeksów (postaram się o tym napisać więcej w kolejnym wpisie) Przy tego typu zmianach niezbędne jest pozostawienie sobie możliwości wycofania zmian, ja to wykonuję poprzez zrzut skryptu T-SQL dla indeksu.
Przy większej ilości indeksów nie chciałbym robić tego pojedynczo, dlatego posługuję się skryptem których chciałbym się podzieli.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
<# .Synopsis Umożliwia zrzut skryptu T-SQL indeksów z bazy danych .DESCRIPTION Umożliwia zrzut skryptu T-SQL indeksów z bazy danych .EXAMPLE Zrzut skryptu dla dwoch indeksow Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') .EXAMPLE Zrzut skryptu dla dwoch indeksow i zapisanie do pliku .\Get-CreateIndexScript.ps1 -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') -Verbose | Out-File C:\file.sql .EXAMPLE Zrzut skryptów dla indeksów pobranych z pliku płaskiego Get-CreateIndexScript -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName (Get-Content C:\Temp\indexes.txt) -Verbose | Out-File D:\Temp\ .LINK Author: Mateusz Nadobnik Link: blog.mnadobnik.pl Date: 22.10.2016 Version: 0.0.1.1 Keywords: Notes: ChangeLog: 22.10.2016 - napisanie skryptu 30.10.2016 - dodanie obługi błędów #> [CmdletBinding()] param( #Address to SQL Server [Parameter(Mandatory=$true)] [string]$SqlServer, #Database Name [Parameter(Mandatory=$true)] [string]$DatabaseName, #Array with index names [Parameter(Mandatory=$true)] [array]$IndexName ) Begin { try { [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null } catch { Write-Warning $Error[0] return } $connectsqlserver = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer $connectsqlserver.ConnectionContext.ApplicationName = "Powershell - Get-CreateIndexScript" $connectsqlserver.ConnectionContext.ConnectTimeout = 1 Write-Verbose "Connect to server $SqlServer" if ($connectsqlserver.ConnectionContext.IsOpen -eq $false) { $connectsqlserver.ConnectionContext.Connect() } $a = "'" + ($IndexName -join "','") + "'" $query = "Select i.name as index_name, t.name as table_name, SCHEMA_NAME(schema_id) as schema_name from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s Join sys.indexes i On i.object_id = s.object_id and i.index_id = s.index_id Inner join sys.tables t on i.object_id = t.object_id and t.object_id = s.object_id Where i.name is not null and i.name in ($a)" Write-Verbose "Query `n $query" try { $db = $connectsqlserver.Databases.Item($DatabaseName) [array]$Indexes = ($db.ExecuteWithResults($query)).Tables[0] } catch { Write-Warning $Error[0] return } [string]$Script = "/****** Dump of index - $SqlServer Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`nUSE [$DatabaseName]`nGO`n`n" } Process { foreach ($row in $Indexes) { try { Write-Verbose "Get script for $($row.index_name)" $Script += "/****** Object: $($row.index_name) Script Date: $(Get-Date -Format "dd-MM-yyyy hh:mm:ss") ******/`n" $Script += $connectsqlserver.databases[$DatabaseName].Tables.Item($row.table_name,$row.schema_name).Indexes[$row.index_name].Script() $Script += ";`nGO `n`n" } catch { Write-Warning $Error[0] } } } End { $connectsqlserver.ConnectionContext.Disconnect() return $Script } |
Przykładowe użycie:
|
1 |
.\Get-CreateIndexScript.ps1 -SqlServer SERWER-SQL -DatabaseName AdventureWorks2012 -IndexName ('IX_TransactionHistoryArchive_ProductID', 'IX_WorkOrderRouting_ProductID') -Verbose | Out-File C:\file.sql |
Wynik powyższego polecenia:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/****** Dump of index - SERWER-SQL Script Date: 13-12-2016 11:20:26 ******/ USE [AdventureWorks2012] GO /****** Object: IX_TransactionHistoryArchive_ProductID Script Date: 13-12-2016 11:20:26 ******/ CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_ProductID] ON [Production].[TransactionHistoryArchive] ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]; GO /****** Object: IX_WorkOrderRouting_ProductID Script Date: 13-12-2016 11:20:26 ******/ CREATE NONCLUSTERED INDEX [IX_WorkOrderRouting_ProductID] ON [Production].[WorkOrderRouting] ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]; GO |
Tak „zrzucone indeksy” pozwalają mi wycofać zmiany w przypadku ewentualnych problemów z wydajnością zapytań. Szerzej o podejściu/metodzie/sposobie usuwania nadmiarowych indeksów napiszę w kolejnych wpisach.
Z pasją poświęcam czas na zdobywanie wiedzy w zakresie szeroko rozumianej Data Platform. Zachwycony językiem skryptowym Windows PowerShell. Swoją wiedzę, doświadczenia i spostrzeżenia opisuję na blogu.