Microsoft – SharePoint – Database – Top Queries

Introduction

Reviewing top N Queries for our SharePoint Databases and as we reviewed each Stored Procedure discovered that for some of the queries we were getting a warning that “column level statistics” are missing.

 

Query for Top Queries

Here is a quick query to identify the top N Queries.


declare @iNumberofQueries int

set @iNumberofQueries = 100

;with cteSummed
    (
        execution_count
      , total_worker_time
      , total_elapsed_time
    )
as
 
    (
       SELECT
          sum(qs.execution_count) as execution_count
        , sum(qs.total_worker_time) AS Total_CPU
        , sum(qs.total_elapsed_time) as total_elapsed_time
       FROM (
 
            select  
					--(@iNumberofQueries)
						qs.*
            from   sys.dm_exec_query_stats AS qs
            --order by qs.total_worker_time DESC
        ) qs    
 
    )
SELECT TOP (@iNumberofQueries)
 
      qs.execution_count
 
    , [executionCount%]
		= (
				(qs.execution_count * 100)
					 / (cteSummed.execution_count)
		  ) 

 
    , Total_CPU
		= qs.total_worker_time
 
    , [totalWorkerTime%]    
		= (
				  (qs.total_worker_time * 100) 
				/ (cteSummed.total_worker_time)
		  ) 

 
    , total_CPU_inSeconds
		 = qs.total_worker_time/1000000 
 
    , average_CPU_inSeconds =
         (qs.total_worker_time/1000000) / qs.execution_count
 
    , qs.total_elapsed_time
 
    , [totalElapsedTime%] 
		= (
				(qs.total_elapsed_time * 100) 
					/ (cteSummed.total_elapsed_time)
		  ) 

 
    , databaseName
		= db_name(st.[dbid])
 
    , [sqlText]
		= st.[text]
 
	, sql_statement
		= 
			(
				SELECT TOP 1 SUBSTRING
				(
					  st.[text]
					, qs.statement_start_offset / 2+1 
					, ( 
						(
							CASE WHEN qs.statement_end_offset = -1   
									THEN (
											LEN
											(
												CONVERT
												(
													  nvarchar(max)
													, st.[text]
												)
											) * 2
										)   
							 	 ELSE qs.statement_end_offset 
							END
						)  - qs.[statement_start_offset]
					) 
					/ 2+1
				)
			) 
    , qp.query_plan
 
FROM
    sys.dm_exec_query_stats 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
        cross join cteSummed
ORDER BY
    qs.total_worker_time DESC


Screen Shot

topQueries

Object Listing

Database Stored Procedure
Search_Service_Application_DB_xx proc_MSS_GetCurrentRegistryVersion
Bdc_Service_DB_xx proc_ar_ClearAllAccessControlEntriesForMetadataObject
Search_Service_Application_DB_xx proc_MSS_PropagationIndexerGetReadyQueryComponents
WSS_Facilities proc_GetChanges
 Bdc_Service_DB_xx proc_ar_GetMethodInstancesForDataClassWithCount
 WSS_Facilities proc_AppendSiteQuota
 Bdc_Service_DB_xx proc_ar_GetMethodInstancesForDataClassWithCount
 WSS_Facilities proc_SecGetDomainGroupMapData
 WSS_Facilities proc_LogChange
 WSS_Facilities proc_ProcessSiteQuotaForStorageMetricsChanges
 WSS_Facilities proc_ProcessSiteQuotaForStorageMetricsChanges
 WSS_Facilities proc_QMChangeSiteDiskUsedAndContentTimestamp
 WSS_Facilities proc_GetListMetaData
 WSS_Facilities proc_UpdateDiskUsed
 WSS_Facilities proc_CopyDir
 WSS_Facilities fn_IsOverQuotaOrWriteLocked
 WSS_Facilities proc_ListContentTypesInWebRecursive
 WSS_Facilities proc_MapFieldToContentType
 WSS_Facilities proc_MapFieldToContentType

Here is a quick rundown of the Identified Queries

 

Query – proc_MSS_GetCurrentRegistryVersion

  • Query is “SELECT @SqlVar=Value from MSSConfiguration where Name = @LikeKey”
  • The  MSSConfiguration is a small table; in our case 640 records; and so table scan is issued

Query – proc_ar_ClearAllAccessControlEntriesForMetadataObject

  • Index seek against AR_MetadataObject
  • Index seek against AR_MetadataObjectSecurity and Clustered Index Delete against same

Query – proc_GetChanges

  • Checks against Table Value Functions (TVFs) that are based on the EventCache table

Query – proc_LogChanges

  • Insert into the EventCache table

Query – dbo.proc_SecGetDomainGroupMapData

  • Columns With No Statistics: [WSS_Facilities].[dbo].[UserInfo].tp_ID

Query – dbo.proc_ProcessSiteQuotaForStorageMetricsChanges

  • Columns With No Statistics: [WSS_Facilities].[dbo].[StorageMetricsChanges].DocId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[StorageMetrics].DocId

Query – dbo.proc_GetListMetaData

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId

Query – dbo.proc_GetListWebPart

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllWebParts].tp_PageUrlID, [WSS_Facilities].[dbo].[AllWebParts].tp_UserID, [WSS_Facilities].[dbo].[AllWebParts].tp_Level, [WSS_Facilities].[dbo].[AllWebParts].tp_PageVersion, [WSS_Facilities].[dbo].[AllWebParts].tp_IsCurrentVersion
  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId

Query – dbo.proc_GetListWebPart

  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId, [WSS_Facilities].[dbo].[EventReceivers].SolutionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId, [WSS_Facilities].[dbo].[EventReceivers].HostType, [WSS_Facilities].[dbo].[EventReceivers].SolutionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId

Query – dbo.proc_ListContentTypesInWebRecursive

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[ContentTypes].Class, [WSS_Facilities].[dbo].[ContentTypes].Scope

Background

MS publication per Best Practice for SharePoint is to turn off “Auto-Create Statistics” on SharePoint databases.

And, so this is to be expected.

MSFT reasoning is that they already have the best indexes in place and so the system should not bother to create new statistics.

System Level Query to identify Queries and Database Tables missing column statistics

Let us quickly inspect our Cached Plan to see which queries and corresponding Database tables and columns are flagged as missing statistics.

Here is one I stole from the .Net but it took me a lot of time to understand and customize for my use:

Instance Level


-- Querying the plan cache for plans that have warnings
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), 
    WarningSearch AS (

			SELECT 
				  qp.query_plan
				, cp.usecounts
				, cp.objtype
				, wn.query('.') AS StmtSimple

			FROM   sys.dm_exec_cached_plans cp

			  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

			  CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(wn)

			WHERE wn.exist('//Warnings') = 1

			AND  wn.exist('//ColumnsWithNoStatistics') =1							
                        AND wn.exist('@QueryHash') = 1

                        )

SELECT
	  ws.query_plan
	, ws.query_plan.query('//Warnings') as Warning
	, ws.query_plan.query('//ColumnsWithNoStatistics') 
              as WarningColumnsWithNoStatistics
	, StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') 
             AS sqlText
	, StmtSimple.value('StmtSimple[1]/@StatementId', 'int') AS StatementId
	, c1.value('@NodeId','int') AS node_id
	, c1.value('@PhysicalOp','sysname') AS physical_op
	, c1.value('@LogicalOp','sysname') AS logical_op
        , ws.objtype
	, c3.value('@Database', 'sysname') as [DatabaseName]
	, c3.value('@Schema', 'sysname') as [Schema]
	, c3.value('@Table', 'sysname') as  [TableName]
	, c3.value('@Column', 'sysname') as [Column]
FROM WarningSearch ws
      CROSS APPLY StmtSimple.nodes('//RelOp') AS q1(c1)
      CROSS APPLY c1.nodes('./Warnings') AS q2(c2)
      CROSS APPLY c2.nodes('./ColumnsWithNoStatistics/ColumnReference') AS q3(c3)

Specific Object Name

It is a bit expensive to dig into the Query Plan and so you might want to be a bit selective regarding which objects you will like to inspect.


-- Querying the plan cache for plans that have warnings
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @DatabaseName sysname
declare @SchemaName sysname
declare @TableName sysname

set @DatabaseName = '[Sharepoint]'
set @SchemaName = '[dbo]'
set @TableName = '[UserInfo]'

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), 
    WarningSearch AS 
    (

	SELECT 
		  qp.query_plan
		, cp.usecounts
		, cp.objtype
		, wn.query('.') AS StmtSimple

	FROM   sys.dm_exec_cached_plans cp

		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

		CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(wn)

	WHERE wn.exist('//Warnings') = 1

	AND  wn.exist('//ColumnsWithNoStatistics') =1							

        AND wn.exist('@QueryHash') = 1

	and  qp.query_plan.exist
               ('//ColumnReference[@Database = sql:variable("@DatabaseName")]') = 1         

	and  qp.query_plan.exist
              ('//ColumnReference[@Schema = sql:variable("@SchemaName")]') = 1                                                        							
	and  qp.query_plan.exist
              ('//ColumnReference[@Table = sql:variable("@TableName")]') = 1                            

   )

SELECT top 1000
        ws.query_plan
      , ws.query_plan.query('//Warnings') as Warning
      , ws.query_plan.query('//ColumnsWithNoStatistics') 
              as WarningColumnsWithNoStatistics
      , StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') AS sqlTtext
      , StmtSimple.value('StmtSimple[1]/@StatementId', 'int') AS StatementId
      , c1.value('@NodeId','int') AS node_id
      , c1.value('@PhysicalOp','sysname') AS physical_op
      , c1.value('@LogicalOp','sysname') AS logical_op
      , ws.objtype
      , c3.value('@Database', 'sysname') as [DatabaseName]
      , c3.value('@Schema', 'sysname') as [Schema]
      , c3.value('@Table', 'sysname') as  [TableName]
      , c3.value('@Column', 'sysname') as [Column]
FROM WarningSearch ws
       CROSS APPLY StmtSimple.nodes('//RelOp') AS q1(c1)
       CROSS APPLY c1.nodes('./Warnings') AS q2(c2)
       CROSS APPLY c2.nodes('./ColumnsWithNoStatistics/ColumnReference') AS q3(c3)
where  c3.exist('//ColumnReference[@Database = sql:variable("@DatabaseName")]') = 1
and  c3.exist('//ColumnReference[@Schema = sql:variable("@SchemaName")]') = 1
and  c3.exist('//ColumnReference[@Table = sql:variable("@TableName")]') = 1

Summary

Does the warning “Column with no statistics” matter?  Probably not in this case, as SharePoint tables, indexes, and queries are canned solutions heavily Q/Aed by the Vendor, Microsoft in this case.

For other systems and databases I will definitely look deeper; especially now that I have stolen\”preped” a code for seeing how rampant it is.

Placeholder

Will come back and credit the source for SQL Codes I per-used.

References

References – Microsoft – SQL Server – Database – Auto/Create Statistics & Auto-Update Statistics

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