Get-DbaDbccStatistic

Synopsis

Retrieves statistics information from tables and indexed views for query performance analysis

Description

Executes DBCC SHOW_STATISTICS to extract detailed information about statistics objects, including distribution histograms, density vectors, and header information. This helps DBAs diagnose query performance issues when the optimizer makes poor execution plan choices due to outdated or skewed statistics. You can analyze specific statistics objects or scan all statistics across databases to identify when UPDATE STATISTICS should be run. Returns different data sets based on the selected option: StatHeader shows when statistics were last updated and row counts, DensityVector reveals data uniqueness patterns, Histogram displays value distribution across column ranges, and StatsStream provides the raw binary statistics data.

Syntax

Get-DbaDbccStatistic
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-Object] <String>]
    [[-Target] <String>]
    [[-Option] <String>]
    [-NoInformationalMessages]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaDbccStatistic -SqlInstance SQLServer2017

Will run the statement SHOW_STATISTICS WITH STAT_HEADER against all Statistics on all User Tables or views for every accessible database on instance SQLServer2017. Connects using Windows
Authentication.

Example: 2

PS C:\> Get-DbaDbccStatistic -SqlInstance SQLServer2017 -Database MyDb -Option DensityVector

Will run the statement SHOW_STATISTICS WITH DENSITY_VECTOR against all Statistics on all User Tables or views for database MyDb on instance SQLServer2017. Connects using Windows Authentication.

Example: 3

PS C:\> $cred = Get-Credential sqladmin
PS C:\> Get-DbaDbccStatistic -SqlInstance SQLServer2017 -SqlCredential $cred -Database MyDb -Object UserTable -Option Histogram

Will run the statement SHOW_STATISTICS WITH HISTOGRAM against all Statistics on table UserTable for database MyDb on instance SQLServer2017. Connects using sqladmin credential.

Example: 4

PS C:\> 'Sql1','Sql2/sqlexpress' | Get-DbaDbccStatistic -SqlInstance SQLServer2017 -Database MyDb -Object 'dbo.UserTable' -Target MyStatistic -Option StatsStream

Runs the statement SHOW_STATISTICS(‘dbo.UserTable’, ‘MyStatistic’) WITH STATS_STREAM against database MyDb on instances Sql1 and Sql2/sqlexpress. Connects using Windows Authentication.

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to analyze for statistics information. Accepts multiple database names as an array.
When omitted, the function processes all accessible databases on the instance, which is useful for instance-wide statistics analysis.

-Object

Specifies the table or indexed view to analyze for statistics information. Use this to focus on a specific object rather than all tables in the database.
Format two-part names as ‘Schema.ObjectName’ (e.g., ‘dbo.Orders’). When specified without Target, all statistics on the object are analyzed.

-Target

Specifies the exact statistics object, index, or column name to analyze. Use this when you need to examine a specific statistic rather than all statistics on an object.
Accepts statistics names (like ‘_WA_Sys_CustomerID’), index names (like ‘IX_Orders_CustomerID’), or column names. Can be enclosed in brackets, quotes, or left unquoted.

-Option

Controls which type of statistics data to return from DBCC SHOW_STATISTICS. Defaults to ‘StatHeader’ which shows when statistics were last updated and row counts.
Use ‘Histogram’ to analyze data distribution patterns, ‘DensityVector’ to examine column uniqueness, or ‘StatsStream’ to get raw binary statistics data for advanced analysis.

-NoInformationalMessages

Suppresses informational messages from DBCC SHOW_STATISTICS output, providing cleaner results focused only on the statistics data.
Use this when running automated scripts or when you only need the statistics data without additional DBCC messaging.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

Outputs

PSCustomObject

Returns one object per row returned from the DBCC SHOW_STATISTICS command. The properties included vary based on the Option parameter.

Common properties (all outputs):

• ComputerName: The name of the SQL Server instance’s computer
• InstanceName: The name of the SQL Server instance
• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: The name of the database containing the statistics
• Object: The schema-qualified name of the table or indexed view (e.g., ‘dbo.Orders’)
• Target: The name of the statistics object, index, or column being analyzed
• Cmd: The full DBCC SHOW_STATISTICS command that was executed

When Option is ‘StatHeader’ (default):

• Name: The name of the statistics object
• Updated: DateTime when statistics were last updated
• Rows: Total number of rows in the table or indexed view
• RowsSampled: Number of rows sampled when statistics were created
• Steps: Number of steps in the histogram
• Density: Overall density value for the statistics
• AverageKeyLength: Average length of the index key in bytes
• StringIndex: Indicates if the statistics are on a string column (Yes/No)
• FilterExpression: Filter expression if statistics are filtered
• UnfilteredRows: Number of rows if different from Rows due to filtering
• PersistedSamplePercent: Sample percent used when creating statistics

When Option is ‘DensityVector’:

• AllDensity: String representation of density value for all leading columns
• AverageLength: Average length of the column in bytes
• Columns: Names of the columns included in the density vector

When Option is ‘Histogram’:

• RangeHiKey: Upper boundary value of the histogram step
• RangeRows: Number of rows with values within the histogram step range
• EqualRows: Number of rows with values equal to RangeHiKey
• DistinctRangeRows: Number of distinct values within the histogram step
• AverageRangeRows: Average number of rows per distinct value

When Option is ‘StatsStream’:

• StatsStream: Raw binary statistics data as a binary object (for advanced analysis)
• Rows: Total number of rows in the table
• DataPages: Number of data pages used by the table