SQL Server – Execution Plan – Warnings – “Columns with no statistics”

Background

Trying to narrow down my thoughts into another cause of poor performance, but got sidetracked into looking for warnings in general.

Brent Ozar – Warnings in Execution Plan

The query that I am using is the one from Brent Ozar’s sp_BlitzCache.

The specific area is “Warnings in Execution Plan“.

The SQL code is:


SELECT  st.text,
        qp.query_plan
FROM    (
           SELECT  TOP 50 *
           FROM    sys.dm_exec_query_stats
           ORDER BY total_worker_time DESC
        ) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value
(
'declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";count(//p:Warnings)'
, 'int'
) > 0

Queries Identified

A couple of Sharepoint queries were identified, inclusive:

  1. [WSS_Content_####].dbo.proc_GetRunnableWorkItems

[WSS_Content_####].dbo.proc_GetRunnableWorkItems

Code

Let us invoke dbo.proc_GetRunnableWorkItems and see what we find out…


set nocount on
go

begin tran

declare
    @ProcessingId          uniqueidentifier,
    @SiteId                uniqueidentifier,
    @WorkItemType          uniqueidentifier,
    @BatchId               uniqueidentifier,
    @MaxFetchSize          int ,
    @ThrottleThreshold     int ,
    @RequestGuid           uniqueidentifier

    select 
        @MaxFetchSize      = 100 
      , @ThrottleThreshold = 0

   exec dbo.proc_GetRunnableWorkItems 
	  @ProcessingId      = @ProcessingId
	, @SiteId            = @SiteId
	, @WorkItemType      = @WorkItemType
	, @BatchId           = @BatchId
	, @MaxFetchSize      = @MaxFetchSize
	, @ThrottleThreshold = @ThrottleThreshold 
	, @RequestGuid       = @RequestGuid OUTPUT     
   print '@RequestGuid: '
           + isNull(cast(@RequestGuid as varchar(60)), '')

rollback tran

Review Execution Plan

SelectWarning

Quick Explanation:

  1. We see the warning right away
  2. It is on Query 2
    • Select All DeliveryDate, Type, ProcessMachineID … from [dbo].[ScheduledWorkItems

Review Warning

Clicked on the warning and dug a bit deeper into the specific Operator Property.

Properties

ClusteredIndexSeek

Operator – Predicate

QueryPredicate

Query Statement

The query that is offering the warning is:



declare @iRet		   int
declare @ReturnWorkItems   int
DECLARE @ProcessingId	   uniqueidentifier	
DECLARE @WorkItemType      uniqueidentifier
DECLARE @Now		   datetime

begin tran

    set @ReturnWorkItems = 1
    set @Now = getdate()
	
    IF @ReturnWorkItems = 1
    BEGIN
      SELECT ALL
          DeliveryDate, Type, ProcessMachineId as SubType
        , Id, SiteId, ParentId, ItemId, BatchId, ItemGuid
        , WebId, UserId, Created, BinaryPayload, TextPayload
            , InternalState
     FROM
            dbo.ScheduledWorkItems
     
     WHERE
            Type = @WorkItemType AND
            DeliveryDate <= @Now AND
            ProcessingId = @ProcessingId
     ORDER BY
            DeliveryDate
    
     IF @@ROWCOUNT <> 0
     BEGIN
       EXEC @iRet = proc_UpdateFailOver 
                       @ProcessingId, NULL, 20
     END -- if @@ROWCOUNT <> 0
    
    END -- IF @ReturnWorkItems = 1

rollback tran 

Object Metadata

Let us look into the table’s indexes and statistics.

Index

Let us quickly review our indexes.


  exec sp_helpindex 'dbo.ScheduledWorkItems'

Output:

listofIndexes

Explanation:
  1. We have 4 indexes
  2. None of them is tracking “Processing ID

Statistics

Let us quickly review our statistics.



select 
	[table]
		=object_name(tblSS.object_id)

	, [stat]
	   = tblSS.name
		
	, tblSS.stats_id				
		
	, tblSS.auto_created

	, tblSS.user_created

	, [index]
	   = tblSI.name			
		
from   sys.stats tblSS

	left outer join sys.indexes tblSI
	
	  on  tblSS.object_id = tblSI.object_id
	  and tblSS.stats_id = tblSI.index_id


where  tblSS.object_id 
	  = object_id('dbo.ScheduledWorkItems')

Output:

listStats

Explanation:
  1. All of our statistics are tied to indexes
  2. No auto-created statistics
    • Auto-Created statistics have auto_created=1
    • In our current state, all records have auto_created set to 0

Why?

Normally the SQL Engine auto creates statistic whenever it thinks they will be useful.

Let us check our db settings …

Code


  select 
     [database]
	= db_name()
			
   , IsAutoCreateStatistics
	= databasepropertyex
             (
                 db_name()
                , 'IsAutoCreateStatistics'
             )
			
	, IsAutoUpdateStatistics
		= databasepropertyex
             (
                  db_name()
                , 'IsAutoUpdateStatistics'
             )

Output:

DBSettings

Explanation:

  1. Our current setting is that we are not auto-creating nor auto-updating statistics
  2. SharePoint updates indexes within its own maintenance utilities
  3. SharePoint does not create indexes nor does it want SQL Server to auto-create them
    1. In-fact, turning on this options is strongly discouraged
    2. The rationale is that SharePoint Engineers have done such a thorough job designing the system and they do not want the puny SQL Engine creating statistics that it thinks might be helpful
    3. One might think the mere present of new Statistics will be OK, as they would still have to vetted along with existing indexes\statistics
    4. But, No, SharePoint thinks NO

Troubleshoot

Auto-create Statistics

Though, we might not want to leave auto-create statistics to the supposedly “whims” of SQL Server.

Let us narrow our help to only those tables where we are experiencing active warnings.

Create Statistics

Code


CREATE STATISTICS [statUser_ProcessingID_DeliveryDate_TYpe]
    ON dbo.ScheduledWorkItems
     (
		  ProcessingId
		, DeliveryDate
		, [Type]
	  )
    WITH FULLSCAN;

Re-run query

Code



declare @iRet		   int
declare @ReturnWorkItems   int
DECLARE @ProcessingId	   uniqueidentifier	
DECLARE @WorkItemType      uniqueidentifier
DECLARE @Now		   datetime

begin tran

    set @ReturnWorkItems = 1
    set @Now = getdate()
	
    IF @ReturnWorkItems = 1
    BEGIN
     SELECT ALL
          DeliveryDate, Type, ProcessMachineId as SubType, Id
        , SiteId, ParentId, ItemId, BatchId, ItemGuid, WebId
        , UserId, Created, BinaryPayload, TextPayload
        , InternalState
     
     FROM
            dbo.ScheduledWorkItems
     WHERE
            Type = @WorkItemType AND
            DeliveryDate <= @Now AND
            ProcessingId = @ProcessingId
    
     ORDER BY
            DeliveryDate
    
     IF @@ROWCOUNT <> 0
     BEGIN
        EXEC @iRet = proc_UpdateFailOver @ProcessingId, NULL, 20
     
      END
    
  END


rollback tran

Output:
QueryExecutionWarning

Review Statistics Distribution

Let us review our stats distribution


exec sp_helpstats [dbo.ScheduledWorkItems]
go

DBCC SHOW_STATISTICS
 (
       [dbo.ScheduledWorkItems]
     , [statUser_ProcessingID_DeliveryDate_TYpe]
 )
WITH HISTOGRAM;
GO

Output:
ShowStatistics

Explanation:

  1. We have no statistics because our target table is current empty

Version

Version – SQL Server

Code


set nocount on
go

select [@@version] = @@version

select
    [serverName] = serverproperty('servername') 
  , [productversion] = serverproperty('productversion') 
  , [edition] = serverproperty('edition') 	
  , [productversion] = serverproperty('productversion') 
  , [productlevel] = serverproperty('productlevel') 			

Output:

version

Explanation:

  1. We are running SQL Server v2014 SP1 – Service Pack 1
  2. Numeric Version Number 12.0.4213.0

Microsoft Connect

Unfortunately, had to open up a connect Item

  1. 181974 – Unresolvable Execution Plan Warning “Columns With No Statistics: [WSS_Content_NNNNN].[dbo].[ScheduledWorkItems].DeliveryDate”
    • The Sharepoint database backup file was uploaded, as well
      • It is SQL Server 2014 backup

Summary

There are a lot of other things we tried, but nothing worked.

I think our problem is the way SQL Server handles Statistics on empty tables.  There is a not a lot written on the tangent of Statistic and empty tables.

So nothing here is prescriptive, every thought and angle is experiential.  And, what a tiny window we have.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s