SQL Server – Windows 32 bit – SQL Server v2014

Background

Reviewing Memory Utilization on one of our boxes.

 

Troubleshooting

Windows Version

Control Panel

System

Image

Explanation
  1. Windows Server 2008 Enterprise

Performance Monitor

Metrics

Metrics – [ SQL Server Instance ] – Memory Manager

Image

Textual

  1. Stolen Server Manager ( KB ) :- 448,400.000
  2. Target Server Memory ( KB ) :- 1,531,904.00
  3. Total Server Memory ( KB ) :- 1,516,648.00

 

SQL Server

Metadata

@@version

Image
Textual

Microsoft SQL Server 2014 (SP2-CU6) (KB4019094) – 12.0.5553.0 (Intel X86)
Jun 28 2017 14:05:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM)

 

Explanation
  1. We are running MS SQL Server 2014 / SP2 ( CU-6 )
  2. Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86>
    • Windows NT 6.0
      • Windows Server 2008
        Link
    • X86
      • 32 bit OS

Engineering

In terms of Engineering, can we do more.

Microsoft

AWE

The “awe enabled” SQL Server feature is deprecated.
Link

Summary

In Microsoft SQL Server 2008 R2, the configuration option, awe enabled, is deprecated. In the next version, Microsoft SQL Server 2012, this configuration option and the feature that uses this configuration option are removed from the product.

Therefore, when you upgrade from SQL Server 2008 R2 to the next version of SQL Server, you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

Operating System & SQL Server v2012+ Max Memory

SQL Server and operating system settings Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS 2 GB
32-bit SQL Server on 32-bit OS with /3G boot option 3 GB
32-bit SQL Server on 64-bit OS 4 GB

Closing

As always keep in mind what your end goal is and what engineering constraints are holding you back.

SQL Server & PowerShell – Get Memory Utilization

Background

Earlier today I found myself trying to better understand a system.

As part of my quick read-up, I found a one line code that shows how to read memory utilization via Powershell.

Code

Overview

Here is that one line code in far too many lines.

Powershell


Set-StrictMode -Version Latest;
Import-Module SQLPS -DisableNameChecking

function displayPath($path)
{
	[object] $objGI = $null;
	[int] $physicalMemoryUsageInKB = 0;
	[int] $physicalMemoryUsageInMB = 0;

	$objGI = (gi $path) 
	
	#Get Data
	$displayName = $objGI.DisplayName;
	$physicalMemoryUsageInKB = $objGI.PhysicalMemoryUsageInKB;
	$physicalMemoryUsageInMB = $objGI.PhysicalMemoryUsageInKB / 1024;
	
	#Display Data       
	$strLog = "`tPath {0}" -f $path;        
	Write-Host $strLog
	
	$strLog = "`tInstance {0}" -f $displayName;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (KB) {0}" -f $physicalMemoryUsageInKB;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (MB) {0}" -f $physicalMemoryUsageInMB;     
	Write-Host $strLog      
	
	Write-Host
        
}

[string] $typeSQLEngine = "SqlServer";
[string] $defaultInstance = "MSSQLServer";
[string] $machineName = $null;
[int]    $iNamedInstanceStartingPos = 6;

# Get machine name
$machineName = $env:computername


#Get a managed computer instance  
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer  

#List out all sql server instances running on this machine  
foreach ($item in $mc.Services)
{

	if ($item.Type -eq $typeSQLEngine)
	{
		
		if ($item.Name -eq $defaultInstance)
		{
			$instanceName = "DEFAULT";
		}
		else
		{
		
		    $instanceNameLen = ($item.Name).length - $iNamedInstanceStartingPos;
			
			$instanceName = ($item.Name).Substring( `
													  $iNamedInstanceStartingPos `
													, $instanceNameLen `
												  );
			
		}
		
		$path = "SQLSERVER:\SQL\" + $machineName + "\" + $instanceName;
		
		displayPath $path
					
	} # if ($item.Type -eq $typeSQLEngine)
	
} 

Output

 

Source Code Control

GitHub

DanielAdeniji/SQLServerPS

Link

DBCC MemoryStatus

Background

Wanted to place another stone in our pond of water where we review Memory Usage in MS SQL Server Instance.

DBCC MemoryStatus

Sample Output

Text File

Sample content when we run “dbcc memusage” and capture the output into a text file.

Notepad++

Here is what things look like in Notepad++.

 

Others Work

Wanted to see how others have consumed “dbcc memusage“.

Slava Murygin

Took to the Internet and found a nice post by Slava Murygin.

It is here.

 

Revision

Here is a revised version…



SET NOCOUNT ON
GO


declare @tblMemoryStatusDump TABLE
(
      [id] INT IDENTITY(1,1) PRIMARY KEY
    , [dump] VARCHAR(600)

);


declare @tblMemoryStatus TABLE
(
     id			INT
   , [measure]  VARCHAR(200) 
   , [counter]  VARCHAR(300) 
   , [value]	VARCHAR(200)
   , [rowType]  char(1)
   , [dump]		varchar(600) 

);

declare @serverName  sysname
declare @queryFormat nvarchar(600)
declare @query       nvarchar(600)

declare @tblMemoryStatusHeader TABLE
(
    [seqNumber] smallint not null
        identity(1,1)

    , [id]   int not null
    
    , [dump] nvarchar(600) not null

    , [rangeStart] int null
    , [rangeEnd]   int null

)

set @queryFormat = 'xp_cmdshell ''sqlcmd -E -S %s -Q "DBCC MEMORYSTATUS" ''';

/*

    sys.xp_cmdshell
    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 19]
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
    because this component is turned off as part of the security configuration for this server. 
    A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
    For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

*/

/*

    exec sp_configure 'show advanced options',1
    reconfigure with override;

    exec sp_configure 'xp_cmdshell',1
    go

    reconfigure with override;

*/


set @serverName = cast (serverproperty('servername') as sysname)
exec master..xp_sprintf
          @query output
        , @queryFormat
        , @serverName

print @query

INSERT INTO @tblMemoryStatusDump
(
    [dump]
)
exec(@query)


insert into @tblMemoryStatus
(
       [id]
     , [dump]
     , [counter] 
     , [value] 

)
select 
          tblMSD.[id]

        , tblMSD.[dump]
        
        , [counter]
            = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 42
                )

        , [value]
            = ltrim(rtrim
              (
                substring
                    (
                        tblMSD.[dump]
                        , 43
                        , 20
                    )
              ))

from   @tblMemoryStatusDump tblMSD

update tblMSD

set	
            
       [rowType] 
            = case
                when tblMSDNext.[dump] like '---%' then 't' 
                when tblMSD.[dump] like '---%' then 'd' 
                when tblMSD.[dump] like '(%rows affected)' then 'c' 
                when tblMSD.[dump] like 'DBCC execution completed%' then 'i' 
                when tblMSD.[dump] is null then '0'
                else 'v'
              end
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatus tblMSDNext

        on tblMSD.[id] + 1  = tblMSDNext.[id]


insert into @tblMemoryStatusHeader
(
      [id] 
    , [dump]
)
select 
      tblMSD.[id]
    , [dump]
        = substring
                (
                    tblMSD.[dump]
                    , 1
                    , 41
                )		

from   @tblMemoryStatus tblMSD

where  tblMSD.[rowType] = 't'

/*
    Get Range
*/
update tblMSH

set	
          [rangeStart] = tblMSH.[id]

        , [rangeEnd] = tblMSHNext.[id]

from   @tblMemoryStatusHeader tblMSH

inner join @tblMemoryStatusHeader tblMSHNext

        on tblMSHNext.[seqNumber] = tblMSH.seqNumber + 1

/*
    Get Range for last record
*/
update tblMSH

set		[rangeEnd] = ( select max([id]) from @tblMemoryStatus tblMSD )

from   @tblMemoryStatusHeader tblMSH

where   rangeEnd is null

/*
    Based on Range, set measure column
*/
update tblMSD

set			
       [measure] = tblMSH.[dump]
                 
from   @tblMemoryStatus tblMSD

inner join @tblMemoryStatusHeader tblMSH

        on tblMSD.[id] between tblMSH.rangeStart and tblMSH.rangeEnd

where tblMSD.[rowType] = 'v'


/*
    remove extra record
*/
delete tblMS
 
from  @tblMemoryStatus tblMS

where tblMS.[rowType] in 
        ( 
              'd'
            , 'c'
            , '0'
            , 'i'
        )

select  
        tblMS.*

from   @tblMemoryStatus tblMS

where  tblMS.rowType in ( 'v')


 

Dedicated

Dedicated to Slava Murygin.

SQL Server – HyperThread Ratio

Background

Trained Aireforge Studio against a set of SQL Server Instances that serve one of our one Applications.

 

AireForge Studio

Compare

Image

 

Explanation

The Numbers looked OK

  1. Max Server Memory
    • In Dev, we have 8 GB of RAM
      • We set our Max Memory to 6.2 ( max  server memory )
      • BTW, the number of actual memory is available via total_physical_memory
    • In QA/Test/Prod, we have 64 GB of RAM
      • We have set Max Memory to 58 GB
      • Again, the number of memory installed in box is displayed in the total_physical_memory
      • We have an average of 48 GB available in all three environments
  2. Total Page File in GB & Available Page File in GB
    • Will skip in depth discussion for now
    • Except to note that it is much smaller in Dev, where we have only 8 GB
  3. CPU Count
    • We have 4 CPUs in Dev
    • And, 8 in QA, Test, and Production
  4. HyperThread Ratio
    • In Dev, QA, and Production the Hyper Thread Ratio is 1
    • And, Test is 2

 

HyperThread Ratio

HyperThread Ratio?

Where is HyperThread coming from?

 

SysInternals

Process Explorer

Again, we go back on the SysInternals line, and get Process Explorer.

To get System Information, we click on the menu Item “View” \ “System Information”

TEST

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 4
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /4 = 2

 

 

Prod

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 8
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /8 = 1

SQL Server – Identify Expensive Queries using Michael K. Campbell Script

Prelude

This is hopefully the first one of many queries.

It uses Michael K. Campbell’s query to get a list of expensive queries besetting our SQL Instance.

Code

dbo.sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript

use master
go

/*
	drop procedure [dbo].[usp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]
*/
if object_id('[dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]') is null
begin

	exec('create procedure [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript] as ')  

end
go

alter procedure [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]
(
	  @filterDatabase   sysname      = null
	, @filterObjectType sysname		 = null
	, @orderBy	        varchar(60)  = null
	, @maxNumberofRows  int			= 300
)
as

begin

	/*
		Performance Tip: Find Your Most Expensive Queries
		Sep 13, 2012 by Michael K. Campbell in Practical SQL Server
		http://sqlmag.com/blog/performance-tip-find-your-most-expensive-queries
	*/

	/*
		Parameters:

			@filterDatabase
				a) Specific database name if you will like to filter on specific database

			@orderBy
				a) SubTreeCost
				b) GrossCost
				c) UseCount
				d) queryPlanHash

			@maxNumberofRows
				a) Maximum Number of Rows

	*/
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

	set nocount on;

	declare @filterDatabaseID int

	declare @tblCache TABLE
	(
	      [id]						int not null identity(1,1)
		, [QueryPlan]				xml not null
		, [PlanHandle]				varbinary(64) not null

		, [query_hash]				Binary(8)
		, [query_plan_hash]			Binary(8)

		, [Statement]				nvarchar(max)

		, OptimizationLevel			sysname null

		, [SubTreeCost]				float
		, [UseCount]				int
		, [SizeInBytes]				int

		, [database]				sysname	null
		, [objtype]					sysname not null
		, [object]					sysname null

		, attributeDBIDValue		int null

		, epaUserIDValue			int null
		, [epaUserIDUserContext]    sysname null

	)

	declare @queryPlanHashUnique TABLE
	(

		  [id] int not null
			identity(1,1)

		, [query_plan_hash]			Binary(8)

		, [numberofEntries]			int

	)

	if (@filterDatabase is not null)
	begin

		set @filterDatabaseID = db_id(@filterDatabase)

	end

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

		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, epaUserIDValue
		, [epaUserIDUserContext]

	)
	AS 
	(
		SELECT
				  eqp.query_plan AS [QueryPlan]

				, ecp.plan_handle [PlanHandle]

				, qs.query_hash

				, qs.query_plan_hash

				, q.[Text] AS [Statement]

				, OptimizationLevel
					= n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') --AS OptimizationLevel 

				, [SubTreeCost] 
					= ISNULL(CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') as float),0) --AS SubTreeCost 

				, [UseCount]
					= ecp.usecounts 

				, [SizeInBytes]
					= ecp.[size_in_bytes] 

				, [database]
					= case 
							when epaDBID.[value] = '32767' then 'Resource DB'
							else db_name
									(
										cast(epaDBID.[value] as int)
									)
					  end

				, ecp.objtype

				, [Object]
					= object_schema_name
						(
							  eqp.objectid
							, eqp.[dbid]
						)
						+ '.'
						+ object_name
						(
							  eqp.objectid
							, eqp.[dbid]
						)

				, cast
					(
						epaDBID.[value] as int
					)

				, cast
					(
						epaUserID.[value]
							as int
					)

				, [epaUserNameContext]
					= case

							when (cast(epaUserID.[value] as int) = -2) 
									then 'Generic'

							else user_name
									(
										  cast(epaUserID.[value] as int)
									)

					  end

		FROM sys.dm_exec_cached_plans AS ecp

		LEFT OUTER JOIN sys.dm_exec_query_stats as qs

				on ecp.[plan_handle] = qs.[plan_handle]

		CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp

		CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) AS q

		CROSS APPLY query_plan.nodes ('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn ( n )

		OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) AS epaDBID

		OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) AS epaUserID

		where epaDBID.[attribute] = 'dbid'

		and   (
					   ( @filterDatabaseID is null )

					or ( epaDBID.[value] = @filterDatabaseID )  

			  )

		and   epaUserID.[attribute] = 'user_id'

		and   q.[text] not like '%sys%'

		and   (
					   ( ecp.objtype = @filterObjectType )
					or ( @filterObjectType is null )
			  )	

		
	)
	insert into @tblCache
	(
		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, epaUserIDValue
		, [epaUserIDUserContext]

	)

	select 
		  [QueryPlan]
		, [PlanHandle]

		, [query_hash]
		, [query_plan_hash]

		, [Statement]

		, OptimizationLevel

		, [SubTreeCost] 
		, [UseCount]
		, [SizeInBytes]

		, [database]
		, [objtype]
		, [object]

		, attributeDBIDValue

		, [epaUserIDValue]
		, [epaUserIDUserContext]
	from   core



	insert into @queryPlanHashUnique
	(
		  [query_plan_hash]
		, [numberofEntries]	
	)

	select 
			  tblC.query_plan_hash
			, count(*)
	from	@tblCache tblC
	group by
			tblC.query_plan_hash
	order by
			count(*) asc

	SELECT TOP 
				(
					 isNull
					 ( 
						  @maxNumberofRows
						, 200
					)
				)

			  core.QueryPlan

			, core.[database]

			, core.objtype

			, core.[object]

			, PlanHandle

			, [Statement]

			, OptimizationLevel

			, SubTreeCost

			, UseCount

			, [GrossCost]	
				= [SubTreeCost] * [UseCount] 

			, [SizeInBytes]
				= SizeInBytes

			--, core.attribute

			--, core.[value]
			, [epaUserIDValue]

			, [epaUserIDUserContext]

            , [existSort]
                = core.QueryPlan.exist('//Sort')
 
            , [existWarning]
                = core.QueryPlan.exist('//Warning')
 
            , [existMissingIndexes]
                = core.QueryPlan.exist('//MissingIndexes')

			, core.[query_hash]

			, core.[query_plan_hash]

			, tblQPHU.numberofEntries


	FROM @tblCache core

	left outer join @queryPlanHashUnique tblQPHU

			on core.query_plan_hash = tblQPHU.query_plan_hash

	where (

				( 

					( 
						core.[database] 
							= isNull(@filterDatabase, core.[database]  )
					)

				)
		  )

	ORDER BY
			case

				when (@orderBy = 'SubTreeCost')	
					then cast(SubTreeCost as varchar(60))

				when (@orderBy = 'GrossCost')	
					then cast(
								([SubTreeCost] * [UseCount] ) 
								as varchar(60)
							)

				when (@orderBy = 'UseCount')	
					then cast(
								[UseCount]  
									as varchar(60)
							)

				when (@orderBy = 'queryPlanHash')	
					then cast(
								tblQPHU.numberofEntries
									as varchar(60)
							)
						
				else (
						[SubTreeCost] * [UseCount]
					 )
		
			end DESC 

	

end
go

exec sys.sp_MS_marksystemobject '[dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]'
go



Lab

Sample Invocation – 001




declare @filterDatabase    sysname      = null
declare @orderBy	       varchar(60)  = null
declare @maxNumberofRows   int			= 300

set @filterDatabase  = null
set @orderBy	     = null
set @maxNumberofRows = 300

set @filterDatabase = 'OdysseyExpress'
set @orderBy = 'SubTreeCost'


exec  [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]

		  @filterDatabase  = @filterDatabase 
		, @orderBy = @orderBy
		, @maxNumberofRows = @maxNumberofRows


Sample Invocation – HPALM



declare @filterDatabase    sysname      = null
declare @orderBy	       varchar(60)  = null
declare @maxNumberofRows   int			= 300

set @filterDatabase  = null
set @orderBy	     = null
set @maxNumberofRows = 300

set @orderBy = 'SubTreeCost'
set @orderBy = 'queryPlanHash'


exec  [dbo].[sp_IdentifyExpensiveQueriesUsingMichaelKCampbellScript]

		  @filterDatabase  = @filterDatabase 
		, @orderBy = @orderBy

Output

hpalm-20161217-1133am

Explanation

  1. List above are entries for an HP-ALM database
    • The user id is 6 and the user_name returned is ##MS_AgentSigningCertificate##
    • And, the database is again an HP User DB
    • Unfortunately, because we can not pass a db_id to user_name and so we are not getting the actual user name

 

Connect Items

  1. Connect ID :- 3116749
    Title :- Please extend the user_name function to accept dbid
    Type :- Suggestion
    Status :- Active
    Opened :- 2016-Dec-17th
    Detail :-
    Both object_name and object_schema_name accept dbid and are more useful as they can be queried from the master db.
    This is often the case when accessing dmv and query plans.
    I have been using the sys.dm_exec_plan_attributes dmv for a while, mostly to get dbid (database name).
    Yesterday discovered that it exposes the user_id attribute, as well.

    It will be nice to be able to combine the user_id with the db_id and get back the actual username
    Link

SQL Server – Aggregate Current Sessions Uptake

Background

We had an outage this morning.

Not sure what really triggered it.

But, as part of our investigative track wanted to get an aggregate view of the users that were connected, the commands they were running, and summation of resource usage.

Query



use [master]
go

if object_id('[dbo].[sp_sessions_aggregatedView]') is null
begin

	exec('create procedure [dbo].[sp_sessions_aggregatedView] as ')

end
go

alter procedure [dbo].[sp_sessions_aggregatedView]
(
	  @filterConnectionTypeSystemOrUser bit = null
	, @filterProgramName varchar(60) = null
	, @orderBy varchar(60) = null
)
as
begin

	declare @tblLogin TABLE
	(
		  [id]			int not null identity(1,1)
		, [loginame]	sysname
	)

	;with cte
	(
			  [systemOrUser]
			, [isUserProcess]
			, [sessionIDMin]
			, [sessionIDMax]
			, [status]
			, [hostname]
			, [loginame]
			, [program_name]
			, [dbid]
			, [database]
			, [command]
			, [numberofConnections]
			, [physicalIO]
			, [numberofLogicalReads]
			, [numberofReads]
			, [numberofWrites]
			, [totalElapsedTime]
			, [cpuTime]
			, [memoryUsedCurrent]
			, [numberofRows]
			, [clientInterfaceName]
			, [lastRequestStartTime]
			, [minutesSinceLastRequest]
			, [openTransactionCount]

	)
	as
	(

		select 

				[systemOrUser]
					 = case
							when tblSP.[spid] <= 50  then 'Background'
							else 'User'
					   end

			   , [isUserProcess]
				   =  case tblSES.is_user_process
	   					when 1 then 'Yes'
						else 'No'
					  end

			   ,  [sessionIDMin]
					= min(tblSP.[spid])


				,  [sessionIDMax]
					= max(tblSP.[spid])

				, [status]
					= tblSP.[status]
				
				,  tblSP.[hostname]

				, tblSP.[loginame]

				, tblSP.[program_name]

				, [dbid]
					= tblSP.[dbid]

				, [database]
					= db_name
						(
							tblSP.[dbid]
						)

				, [command]
					= tblSP.[cmd]

				, [numberofConnections]
					= count(*)

				, [physicalIO]
				   = sum(tblSP.physical_io)

				, [numberofLogicalReads]
					= sum(
							tblSES.[logical_reads]
						 )

				, [numberofReads]
					= sum(
							tblSES.[reads]
						 )

				, [numberofWrites]
					= sum(
							tblSES.[writes]
						 )

				, [totalElapsedTime]
					= sum
					   (
						   isNull
						   (
								  tblSES.[total_elapsed_time]
								, 0
							)
						)

				, [cpuTime]
					= sum
					   (
						   isNull
						   (
								  tblSES.[cpu_time]
								, 0
							)
						)

				, [memoryUsedCurrent]
					= sum
					   (
						   isNull
						   (
								  tblSES.[memory_usage]
								, 0
							)
						)


				, [numberofRows]
					= sum
					   (
						   isNull
						   (
								  cast( tblSES.[row_count] as bigint)
								, 0
							)
						)


				, [clientInterfaceName]
					= isNull(tblSES.client_interface_name, '')

				, [lastRequestStartTime]
					= max(tblSES.last_request_start_time)

				, [minutesSinceLastRequest]
					= datediff
						(
							  minute
							, max(tblSES.last_request_start_time)
							, getdate()
						)

				, [openTransactionCount]
					= sum(
							coalesce
								(
									  tblSP.[open_tran]
									, tblSES.[open_transaction_count]
								)
						 )

		from master.sys.sysprocesses tblSP

		left outer join sys.dm_exec_connections tblSEC

			on tblSP.spid = tblSEC.[session_id]

		left outer join [master].[sys].[dm_exec_sessions] tblSES

				on tblSEC.[session_id] = tblSES.[session_id]

	
		left outer join [master].[sys].[dm_exec_requests] tblSER

				on tblSEC.[session_id] = tblSER.[session_id]

		where tblSP.loginame not in
						(
							select [loginame]
							from   @tblLogin
						)

							
		and
			(
					( @filterConnectionTypeSystemOrUser is null )

				or ( isNull(tblSES.is_user_process, 0)
						= @filterConnectionTypeSystemOrUser 
					)
					
			)

		and
			(
					( @filterProgramName is null )

				or ( tblSP.[program_name]
						like @filterProgramName + '%'
					)
					
			)
	
	group by

				 case
					when tblSP.[spid] <= 50  then 'Background'
					else 'User'
				 end

			   , case tblSES.is_user_process
	   					when 1 then 'Yes'
						else 'No'
					  end

				, tblSP.[status]

				, tblSP.[hostname]
				, tblSP.[loginame]
				, tblSP.[program_name]
				, tblSP.[dbid]
				, tblSP.[cmd]
				, isNull
				(
					tblSES.client_interface_name
					, ''
				)

	)

	select 
			  [cte].[systemOrUser]
			, [cte].[isUserProcess]
			, [cte].[sessionIDMin]
			, [cte].[sessionIDMax]
			, [cte].[status]
			, [cte].[hostname]
			, [cte].[loginame]
			, [cte].[program_name]
			--, [cte].[dbid]
			, [cte].[database]
			, [cte].[command]
			, [cte].[numberofConnections]
			, [cte].[physicalIO]
			, [cte].[numberofLogicalReads]
			, [cte].[numberofReads]
			, [cte].[numberofWrites]
			, [cte].[totalElapsedTime]
			, [cte].[cpuTime]
			, [cte].[memoryUsedCurrent]
			, [cte].[numberofRows]
			, [cte].[clientInterfaceName]
			, [cte].[lastRequestStartTime]
			, [cte].[minutesSinceLastRequest]
			, [cte].[openTransactionCount]
				
	from   [cte]
	
	order by

			case
			 
				when @orderBy = 'numberofConnections' then [numberofConnections]
				when @orderBy = 'physicalIO' then [physicalIO]
				when @orderBy = 'numberofLogicalReads' then [numberofLogicalReads]
				when @orderBy = 'totalElapsedTime' then [totalElapsedTime]
				when @orderBy = 'cpuTime' then [cpuTime]
				when @orderBy = 'memoryUsedCurrent' then [memoryUsedCurrent]

				when @orderBy = 'database' then -[dbid]

				else [numberofLogicalReads]
			end 
					desc
	

end
go

Output

aggregatedusersview

 

Dedicated

Dedicated to my manager who said “even if it is or not a problem with the database, we can not rub it in“.

Listening

Martina McBride – For These Times
Link

In these times in which we live
Where the worst of what we live
Is laid out for all the world on the front page
And the sound of someone’s heartbreak
Is a soundbite at the news break
With a close shot of the tears rollin’ down their face
Blessed be the child who turns a loving eye
And stops to pray
For these times in which we live

 

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