SQL Server – Query Plan – Warning – “Columns with no statistics”

Background

Wanted to quickly cover how to address a SQL Server Query Plan warning.

Warning

Textual

The Warning reads “Columns with no Statistics [dbo].[tblTimer].stepid”

Image

missingStatistics_01_20191205_0247AM.png

Query

SQL

Text


select @stepID =
(
       select isNull(max(stepID), 0) + 1
       from   dbo.tblTimer
       where  sectionID = @sectionID
       and    instanceID = @instanceID
)

TroubleShooting

SQL

Syntax


    exec sp_helpstats
          @objname =  @objname
        , @results = [ALL|STATS]

Sample


    exec sp_helpstats
          @objname =  '[dbo].[tblTimer]'
        , @results = 'ALL'

Output

Grid

statistics_01_20191205_0333AM

Textual

  1. _WA_Sys_00000003_019E3B86 ( instanceId )
  2. idx_sectionID_instanceID_stepID ( sectionId, instanceId, stepid )
  3. PK__tblTimer__02925FBF ( id )

 

Remediation

SQL

Text


declare @object    nvarchar(512)
declare @statistic sysname

declare @objectID  int

set @object = '[dbo].[tblTimer]'
set @statistic = 'stat_stepID'

set @objectID = object_id(@object)

if
    ( @objectID is not null)
    and not exists
    (
        select *

        from   sys.stats tblSS

        where  tblSS.[object_id]= @objectID

        and    tblSS.[name] = @statistic
    )
begin

    CREATE STATISTICS [stat_stepID]
    ON [dbo].[tblTimer]
    (
        [stepid]
    )
    ;

end
GO

Review

Once you create the missing statistics, please run the query again and see if the warning is gone.

missingStatistics_02_20191205_0631AM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s