Update SQL Server Statistics with Powershell

  • Article

It can be usefull to have a script to update statistics with powershell.

this script is very simple, you just have to connect to you database and call the updatestatistics() method for all your tables :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

 $s = new-object ('Microsoft.sqlserver.Management.smo.server') myServer\myInstance

 $db = $s.databases["myDB"]

 foreach ($t in $db.tables)
{
write-host "Update stat" $t.name
$t.updatestatistics("all", "fullscan")
}

the first parameter is a statistics Target, possible value are described here : https://msdn.microsoft.com/fr-fr/library/microsoft.sqlserver.management.smo.statisticstarget.aspx

the second parameter is a Statistics scan type, possible value are describe here : https://msdn.microsoft.com/fr-fr/library/microsoft.sqlserver.management.smo.statisticsscantype.aspx

If you want to update your statistics with giving a sample and not a full scan, the script become :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = new-object ('Microsoft.sqlserver.Management.smo.server') myServer\myInstance

$db = $s.databases["myDB"]

$sample = 50

foreach ($t in $db.tables)
{
write-host "Update stat" $t.name
$t.updatestatistics("all", "percent", $sample)
}

Enjoy...