Error processing execution plan results. The error message is: There is an error in XML document

Background

Tried to access a Query’s execution plan and ran into a wall.

The error is pasted below.

Error Message

Image

IndexKindType.NonClusteredHash.20190405.0457AM.

Text


An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:
There is an error in XML document (1, 5093).

 

Resolution

Looked more into it.

And, determined that I was using SQL Server Management Studio v2005.

In this case I should be using a newer version, v2014 to be exact.

SSMS – v2008 – Error – “Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)”

Background

Drew a quick error running Sql Server Management Studio (SSMS).

Error

Text

The error reads :-

Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

Image

SSSM.v2008.IndexWasOutsideTheBoundsOfTheArray.20190203.0652AM.PNG

Explanation

  1. Error reads
    • Microsoft.SqlServer.Smo
    • Index was outside the bounds of the array

Recreate

Tried connecting to Microsoft SQL Server v2017 instance with Sql Server Management Studio v2008.

 

TroubleShooting

Microsoft.SqlServer.Smo

SysInternals / Process Explorer

Version

Outline
  1. Download SysInternals \ Process Explorer
  2. Launched SysInternal’s Process Explorer
  3. Selected the ssms.exe
  4. View process modules
    • Click on menu “View”\”Show Lower Pane”
  5. Find
    • Click on menu and choose Find
    • Enter smo
    • Reviewed found modules
  6. Launched Windows Explorer
  7. Accessed the found DLL
  8. Noted version number as 10.0.1600.22
Images
Image – Process Explorer

processExplore.20190203.0957AM.PNG

Image – Handle or DLL substring

findModule.smo.PNG

Windows Explorer

ssmo.sysInternals.processExplorer.20190203.0720AM.PNG

Remediation

Microsoft SQL Server 2008 Service Pack 4 Feature Pack

SQL Server SMO is bundled as part of SQL Server Feature Pack.

The latest version is Service Pack 4 and it is available here.

Packages

  1. Shared Management Objects
    • ENU\X86\SharedManagementObjects.msi
  2. Clr Types
    • ENU\X86\SQLSysClrTypes.msi

Outline

  1. Download
    • Download ENU\X86\SharedManagementObjects.msi
    • Download ENU\X86\SQLSysClrTypes.msi
  2. Install
    • Install SQLSysClrTypes.msi
    • Install SharedManagementObjects.msi

Screenshot

Download

SQL System CLR Types

clrtypes.20190203.0732AM.PNG

Management Objects

microsoft.smo.20190203.0728AM.PNG

Installation

Management Objects
Prerequisite – Missing Microsoft SQL Server 2008 System CLR Types

clrtypes.20190203.0730AM.PNG

Welcome

microsoft.smo.welcome.20190203.0733AM.PNG

System CLR Types
Installation – SQL Server 2008 System CLR Types

clrtypes.welcome.20190203.0732AM.PNG

Post Installation Review

Outline

  1. Post Installation
    • Microsoft.SqlServer.Smo.dll
    • Version
      • Moved from 10.0.1600.22 to 10.0.6000.29

Images

WindowsExplorer.Microsoft.SqlServer.smo.dll.20190203.1028AM.PNG

Acknowledged

Acknowledged Giles D Middleton.

GilesDMiddleton.20190203.1041AM

 

References

  1. stack overflow
    • Index was outside the bounds of the Array. (Microsoft.SqlServer.smo)
      Link
  2.  Microsoft
    • Microsoft SQL Server 2008 Service Pack 4 Feature Pack
      Link

 

SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

SQL Server On Linux – Post Installation – Configuration – Set Folders

Background

Upon installation, SQL Server on Linux utilizes the same storage folders for data, log, and backups.

Outline

  1. Review Current Settings
  2. Provision Folders
  3. Register Folder Settings on SQL Server
  4. Restart SQL Server
  5. Review new folder settings

Processing

Review Current Settings

SQL Server Management Studio ( SSMS )

Let us use our GUI Tool, SSMS, to determine current default folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.20181127.1135AM

Explanation
  1. Data, Log, Backup
    • /var/opt/mssql/data/

Provision Folders

Outline

  1. Create An Array
  2. Fill Array with list of folders
  3. Iterate Array List
    • Get Folder
    • Determine if folder exists
    • If Folder does not exist, please create it
    • Set Folder Attributes
      • Set Folder Owner to mssql
      • Set Folder Group to mssql

Script


#!/bash/bash

echo "Preparing folders"

declare -a _folderList=(
                          "/app/Microsoft/sqlServer/data"
                          "/app/Microsoft/sqlServer/log"
                          "/app/Microsoft/sqlServer/backup"
                          "/app/Microsoft/sqlServer/dumpCrash"
)

#Get Number of elements
_iNumberofElements=${#_folderList[*]}

echo "Number of elements $_iNumberofElements "

_i=0

while [ $_i -lt $_iNumberofElements ]
do

  #Current Folder
  _folder=${_folderList[_i]}

  _folderExistence=false

   #Does Folder Exist
   if [ -d $_folder ]
   then

     _folderExistence=true

   fi

   #Speak Folder
   echo "$_i ) $_folder "

   #if Folder does not exist, create it
   if [ $_folderExistence = false ]
   then

       echo -e "\tCreating Folder $_folder"

       mkdir --parents $_folder

  fi

  #Change Folder owner
  echo -e "\tChange Folder Owner"
  chown mssql $_folder

  #Change Folder Group
  echo -e "\tChange Folder Group"
  chgrp mssql $_folder

  #Move to next array element
  _i=$((_i+1))

done

Configure SQL Server Default Folders

Outline

  1. Issue folderlocation.default[x]
    • Data
    • Log
    • Backup
    • Dump

Script


/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /app/Microsoft/sqlServer/data

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /app/Microsoft/sqlServer/log

/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /app/Microsoft/sqlServer/backup

/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /app/Microsoft/sqlServer/dumpCrash

Restart SQL Server Service

Outline

Restart SQL Server Service to effect changes

Script


sudo systemctl restart mssql-server.service

Review new settings

SQL Server Management Studio ( SSMS )

Let use again use SSMS to review folder settings.

Server Properties

Server Properties – Database Settings
Images

configuration.defaultLocations.ssms.post.20181127.0245PM

Explanation
  1. We were able to change our targeted folders
    • Data, Log, Backup

 

SQL Server Agent – Configuration on Linux

Background

Post installation of SQL Server on our Linux Box, it is time to review our SQL Server Agent component.

Lineage

  1. SQL Server – Installation on Linux/CentOS
    Link

Check List

Let us go through our checklist.

Outline

Is SQL Server Configured?

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services 

Output

sys.dm_server_services.pre.20181127.0602AM.PNG

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Stopped
    • Service Account
      • LocalSystem
    • Clustered
      • No

 

Configuration File

/opt/mssql/bin/mssql-conf

List

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to identify configurable elements.

SQL

sudo /opt/mssql/bin/mssql-conf list

Output

mssql-conf.pre.20181127.0607AM.PNG

Explanation
  1. sqlagent.databasemailprofile
    • Database Mail Profile
  2. sqlagent.enabled
    • Is SQL Server Agent Enabled
  3. sqlagent.errorlogfile
    • Error Log File
  4. sqlagent.errorlogginglevel
    • Error Logging Level

 

Configure

/opt/mssql/bin/mssql-conf

Process

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to make configuration changes.

Make Changes

Command

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Output

mssql-conf.set.20181127.0610AM.PNG

Explanation
  1. sqlagent.enabled
    • Enabled SQL Server Agent

 

Effect Changes

To effect the changes, please use systemctl

Command

sudo systemctl restart mssql-server.service

Output

mssql-conf.set.systemctl.20181127.0732AM

Explanation
  1. Restarted SQL Server Service to effect the changes

 

Configuration

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services

Output

sys.dm_server_services.post.20181127.0735AM

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Running
    • Process ID
      • 4116 ( pid )
        • Obviously yours will be different
    • Last Startup Time
      • 1600-12-31 16:00:00.0000000 -08:00
        • Not Accurate
    • Service Account
      • LocalSystem
    • Clustered
      • No

Usage

SQL Server Management Studio ( SSMS )

Object Explorer

Images
Images – Before

sqlServerExplorer.pre.20181127.0739AM.PNG

Images – After

sqlServerExplorer.post.20181127.0740AM.PNG

 

SQL Server – Database Files – Minimum File Size

Background

There is a setting that is unfortunately difficult to determine within Transact SQL.

That setting is the smallest size a data or log file can be shrunk down to.

GUI

SQL Server Management Studio ( SSMS )

Database Property

Image

bigfix_Enterprise_initialSizes_20180711_1119AM

Code

Syntax


dbcc page
(
      {'dbname' | dbid}
    , filenum
    , pagenum
    [, printopt={0|1|2|3} ]
)

Explanation

  1. DBCC PAGE
    • Database
    • FileID
    • Pagenum
      • 0
    • printopt
      • 3 – page header plus detailed per-row interpretation

SQL

Data files

Code


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblDBCCPage Table
(

	  [parentObject] sysname not null
	, [object]		 sysname not null
	, [field]		 sysname not null
	, [value]		 varchar(255) null	

	, [computedInKB]
		as case
				when ([field] like '%size%') then
						[value] * 8

				else null
		  end

	, [computedInMB]
		as case
				when ([field] like '%size%') then
						(
							[value] * 8
						)
						/ ( 1024)

				else null
		  end
)

set @FORMAT_SQL = 'DBCC PAGE(''%s'',%s,0,3) with tableresults, no_infomsgs';

set @database = 'BFEnterprise';

set @fileID = 1
set @fileID = 1
set @dbid = db_id(@database);

select
		  @fileSymbolicName = tblSMF.[name]
		, @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

if (@fileID is not null)
begin

	DBCC TRACEON(3604) with no_infomsgs;

	exec master..xp_sprintf
			  @sql output
			, @FORMAT_SQL
			, @database
			, @fileIDAsString

	print @sql

	insert into @tblDBCCPage
	(
		  [parentObject]
		, [object]
		, [field]
		, [value]
	)
	exec(@sql)

	-- exec sp_helpdb [model]
	select
		      [database] = @database
			, [fileSymbolicName] = @fileSymbolicName
			, [filePhysicalName] = @filePhysicalName
			, [parentObject]
			, [object]
			, [field]
			, [value]
			, [computedInKB]
			, [computedInMB]

	from   @tblDBCCPage tbl

	where  (
			( field in
				(
					'MinSize'
					, 'Size'
				)
			)

	DBCC TRACEOFF(3604) with no_infomsgs

end -- if (@fileID is not null)

Output

bigfix_Enterprise_Code_20180711_1130AM.png

Log File

Explanation

Unfortunately, same does not work for log files.

You will get the error pasted below.

Output


DBCC PAGE('BFEnterprise',2,0,3) with tableresults, no_infomsgs
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1585190943824412672 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -1.
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 13792724832485376 (type Unknown), page ID (2:3) contains an incorrect page ID in its page header. The PageId in the page header = (102:7274528).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 12947986374328320 (type Unknown), page ID (2:6) contains an incorrect page ID in its page header. The PageId in the page header = (117:7077985).
Msg 8909, Level 16, State 1, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9007714657435648 (type Unknown), page ID (2:7) contains an incorrect page ID in its page header. The PageId in the page header = (32:7536756).
Msg 8939, Level 16, State 98, Line 6
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7036874954637312 (type Unknown), page (0:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -14.

<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Crediting

Edward Dortland
File Header Properties
Link

Summary

Unfortunately, the word size is imprecise and thus need to be surrounded to facilitate context.

When Database properties are viewed through SSMS, for each data and log file, there is a number identified as “Initial Size“.

I think one has to be careful and read more into what it means and how it is sourced.

References

  1. Edward Dortland
    • Stack Overflow
      • File Header Properties
        Link
  2. Paul Randal
    • Server & Tools Blogs > Data Platform Blogs > SQL Database Engine Blog
      • How to use DBCC PAGE
        Link

SSMS – Error Message – “Property Owner is not available for Database”

Background

Trying to access the database property on one of our databases.

Error

Error Message

Syntax

Property Owner is not available for Database ‘[database]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Sample

Property Owner is not available for Database ‘[AdminDB]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

Troubleshooting

Metadata

sp_helpdb

Syntax

use master
go

exec sp_helpdb
go

Output

Explanation

  1. Database
    • AdminDB
      • ~~UNKNOWN~~

sys.databases

Syntax

select 
		  tblSD.[database_id]
		, tblSD.[name]
		, tblSD.[owner_sid]
		, [owner]
			= SUSER_SNAME(tblSD.owner_sid)

from [master].[sys].[databases] tblSD
go

Output

Explanation

  1. Database
    • AdminDB
      • SUSER_SNAME(sys.databases.owner_sid) returns null

 

Remediation

Alter Authorization

Syntax


use [master]
go

ALTER AUTHORIZATION
DATABASE::[database]
TO [sa]
;

Sample


use master
go

BEGIN TRAN

	exec sp_helpdb

	ALTER AUTHORIZATION 
		ON DATABASE::[AdminDB] 
		TO [sa]
		;    

	exec sp_helpdb

ROLLBACK

Output

 

Reproducible

SSMS – v2017

The error does not occur in SSMS v2017.

SSMS – v2014

Noticed it in v2014 when trying to access the database property for an orphaned database.

 

References

  1. Microsoft
    • Docs > SQL > T-SQL > Statements
      • ALTER AUTHORIZATION (Transact-SQL)
        Link
    • Docs > SQL > T-SQL > Functions