Background of this implementation-As a part of Performance tuning, We noticed huge avg_disk_queue_length on F drive after analysis, we’ve recommended to move two tables(data and index)to different drive all together a different filegroup. This would give the best IO performance. Those two tables have ‘N’ indexes. Manually scripting ‘N’ index is going to be daunting task.
The below are the sequence of steps to create index script.
Manually load the SQL Server snap-ins into PowerShell, run it on the computer where SQL Server is installed.
**********************
Load SNAPINS
*****************
#SQL Provider
ps:\>add-pssnapin sqlserverprovidersnapin100
#Cmdlets
ps:\>add-pssnapin sqlservercmdletsnapin100
***********************************
Five Parameter Details -
***********************************
$Server = Local or remote server ( Localhost or Server01 or Server 02)
$Instance = Default/NamedInstanceName ( default or TMG or KAT)
$Database = DatabaseName ( dssp or webp)
$Schema = Schema Name( dbo or any schema name)
$Tables = Enter ‘*’ [All tables] or Name of the specific Table(Work_order)
Output:- F:\PowerSQL\index.txt ( Change the path)
***********************************************************
Actual PowerShell Script
****************************************************************
PARAM
(
$server = $(read-host “Server”),
$instance = $(read-host “Instance – Default”),
$database = $(read-host “Database”),
$schema = $(read-host “schema (default schema dbo)”),
$tables = $(read-host “Tables (*)”)
)
$path = “sqlserver:\sql\$server\$instance\databases\$database\tables”
IF ($tables -eq ‘*’)
{
$tableset= gci -path $path | select-object name
foreach($t in $tableset)
{
$path1 = $path+”\dbo.”+$t.name+”\indexes\”
gci -path $path1 | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n ” | out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}
ELSE
{ $tableset =get-childitem $path -ErrorAction stop | where-object {$_.name -like “$tables”}
foreach($t in $tableset)
{
$path = $path+”\dbo.”+$t.name+”\indexes\”
gci -path $path | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n “| out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}
**************************
