How to find out stale statistics of databases in SQL Server (PowerShell)

Introduction

This PowerShell script can find out stale statistics of databases in SQL Server that may cause performance issues.

Scenarios

Some people ask how to find stale statistics for these statistics may cause performance issues. This PowerShell script will demo how to check stale statistics which have been updated for more than 1000 times in SQL Server 2008 R2 starting with Service Package 2 or SQL Server 2012 starting with Service Package 1. 

Script

You can use this script in this way:
1. Run Microsoft PowerShell as Administrator

2. Run the script in the form: &Path
For example:E:\ Upload\ StaleStatistics.ps1

3. Press “Enter”, enter the server name and the ReportServer database
Server name: Server Name\ Instance Name.
Note: If it’s a default server, please enter the server name.

4. Press “Enter” and choose the authentication type.
If you choose SQL Server authentication, please enter the correct user name and password.

5. After the script finishes running, we’ll get the following figure:

Here are some code snippet for your reference:

PowerShell
Edit|Remove
if($choice -eq 0) 
{ 
    #Create SqlConnection object and define connection string 
    $Connection = New-Object System.Data.SqlClient.SqlConnection 
    $ConnectionB = New-Object System.Data.SqlClient.SqlConnectionStringBuilder 
    $ConnectionB["Data Source"] = $ServerName 
    $ConnectionB["Database"] = $DatabaseName 
    $ConnectionB["Trusted_Connection"] = "SSPI" 
    $Connection.ConnectionString = $ConnectionB.ConnectionString 
} 
 

Prerequisites

SQL Server 2008 R2 starting with Service Package 2 or
SQL Server 2012 starting with Service Package 1