SQL Server – Database Size Aggregated By File Type

Background

For a performance exercise that I will be undertaken I need to figure out how much my user databases and tempdb are growing.

 

Lineage

Here are some past posts along those same lines.

  1. SQL Server – List Database Size
    Use sys.master-files
    Published On :- 2017-March-29th
    Link

 

SQL

Pasted below are some sample SQL for getting file sizes and in use portion aggregated by file types.

Database Size for current database

Code



; with cte
as
(

  select 
		    [dbName]
			= DB_NAME() 

		, [type]				

		, [fileSizeMB]
			= cast
				(
					sum(size)/128.0
					as decimal(10, 2)
				)

		, [spaceUsedMB]
			= cast
				(
					sum(
							CAST
							(
								FILEPROPERTY(name, 'SpaceUsed') AS INT
							)
					)/128.0
					as decimal(10, 2)
				)

		, [freeSpaceMB]
			= sum
				(
					tblSDF.[size]
					- CAST
					(
						FILEPROPERTY
						(
							  tblSDF.[name]
							, 'SpaceUsed'
						) AS INT
					)
				)/128.0

	from sys.database_files tblSDF

	group 
		by tblSDF.[type] 

)

select 
		  [dbName]

		, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

		, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

		, [dataInUse%]
			= cast
				( 
					(
						( sum(isNull([inuse0], 1)) * 100.00 )
							/ sum(isNull([alloc0], 1))
					)
					as decimal(10, 2)
				)

		, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
		, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	
		, [logInUse%]
			= cast
				(
					( 
						sum
						(
							isNull([inuse1], 1)
						) * 100.00 
					)
					/ sum
					  (
						isNull([alloc1], 1)
					  )

					as decimal(10, 2)
				)

		, [fileStreamAllocatedSizeInMB] 
			= sum
				(
					isNull([alloc2], 0)
				)


from   
		(
		
			select 
					  [dbName]
					, [type] = 'alloc' + cast([type] as char(1))
					, [fileSizeMB]
					, [spaceUsedMB]
					, [typeInUse] = 'inuse' + cast([type] as char(1))
			from   cte

		) cteFS

		PIVOT
		(

			  sum 
			  ( 
				cteFS.[fileSizeMB] 
			  )

			  for [type]
			  in 
			  (
				  [alloc0]
				, [alloc1]
				, [alloc2]
			  )

		) as cteDataFileSizeInMB

		PIVOT
		(

			  sum 
			  ( 
				[spaceUsedMB]
			  )

			  for [typeInUse]
			  in 
			  (
				  [inuse0]
				, [inuse1]
				, [inuse2]
			  )

		) as cteInUseFileSizeInMB


group by
		  [dbName]



Output

 

 

Database Size for all databases

Code




set nocount on;
go

use [master]
go

declare @tblDatabase  TABLE
(
	  [dbName]					sysname not null
	, [DataAllocatedSizeInMB]   int
	, [DataInUseSizeInMB]	    bigint

	, [dataInUse%] 
		as 
			convert
			(
				  decimal(10, 2)
				, (
					[DataInUseSizeInMB] * 100.00
					/ NULLIF
						(
							[DataAllocatedSizeInMB]
							, 0
						)	
					)
			)

	, [LogAllocatedSizeInMB] bigint
	, [LogInuseSizeInMB]	 bigint
	, [logInUse%] as 
		convert
		(
			decimal(10, 2)
			, ( [LogInuseSizeInMB] * 100.00 )
				/ NULLIF([LogAllocatedSizeInMB], 0)
		)

	, [fileStreamAllocatedSizeInMB]  bigint


)

insert into @tblDatabase

EXEC sp_MSforeachdb 
	'
	IF DATABASEPROPERTYEX(''?'', ''Collation'') IS NOT NULL
	begin

		USE [?];
		; with cte
		as
		(

		  select 
					[dbName]
					= DB_NAME() 

				, [type]				

				, [fileSizeMB]
					= sum(size)/128.0

				, [spaceUsedMB]
					= sum(
							CAST
								(
									FILEPROPERTY(name, ''SpaceUsed'') AS INT
								)
						)/128.0

				, [freeSpaceMB]
					= sum
						(
							tblSDF.[size]
							- CAST
							(
								FILEPROPERTY
								(
									  tblSDF.[name]
									, ''SpaceUsed''
								) AS INT
							)
						)/128.0

			from sys.database_files tblSDF

			group 
				by tblSDF.[type] 

		)

		select 
				  [dbName]

				, [DataAllocatedSizeInMB] = sum(isNull([alloc0], 1))

				, [DataInUseSizeInMB] = sum(isNull([inuse0], 1))

				, [LogAllocatedSizeInMB] = sum(isNull([alloc1], 0))
				, [LogInuseSizeInMB] = sum(isNull([inuse1], 0))	

				, [fileStreamAllocatedSizeInMB] 
					= sum
						(
							isNull([alloc2], 0)
						)

		from   
				(
		
					select 
							  [dbName]
							, [type] = ''alloc'' + cast([type] as char(1))
							, [fileSizeMB]
							, [spaceUsedMB]
							, [typeInUse] = ''inuse'' + cast([type] as char(1))
					from   cte

				) cteFS

				PIVOT
				(

					  sum 
					  ( 
						cteFS.[fileSizeMB] 
					  )

					  for [type]
					  in 
					  (
						  [alloc0]
						, [alloc1]
						, [alloc2]
					  )

				) as cteDataFileSizeInMB

				PIVOT
				(

					  sum 
					  ( 
						[spaceUsedMB]
					  )

					  for [typeInUse]
					  in 
					  (
						  [inuse0]
						, [inuse1]
					  )

				) as cteInUseFileSizeInMB


			group by
				  [dbName]

		end

	  '

select *
from   @tblDatabase
order by [dbName]

SQL Server Management Studio ( SSMS ) – Non-Printable Characters

 

Background

Through the use of a Microsoft LightSwitch application that we are working on, I have been eating my dog food.

 

Data Entry

As one who hates the sheer drudgery of data entry, I end up doing quite a bit of copy and paste.

I copy and paste data from the original Excel file into the LightSwitch application.

And, things have been mostly OK; as the data that needs to be copied over is minimal.

But, recently I started noticing little errors cropping up.

I launched SQL Server Profiler and noticed the errors are due to referential integrity.

The error was traced to trying to place data into secondary tables whereas those same data are not in our primary data.

 

TroubleShooting

 

Query Data

Launched Sql Server Management Studio ( SSMS ), and queried for the data and they appear to be in the Primary table.

 

Issue

After a bit of head scratching traced the problem back to white spaces at the end of the primary key fields.

 

Remediation

As always took to the Net to determine fixes and workarounds.

Outline

Here are the workarounds that we found thus far:

  1. Using Transact SQL
    • Identify data that have non-printable characters ( CRLF )
    • Strip out the Non-Printable characters

 

Code

Using Transact SQL

Here the functions that I found on the Net

Sourced

  1. dbo.fn_ShowWhiteSpace
  2. dbo.fn_nonPrintableStringClean

 

Code Snippet

 
use master
go
 

 
if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_ShowWhiteSpace]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_ShowWhiteSpace]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER FUNCTION [npc].[fn_ShowWhiteSpace]
(
    @str varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    /*
        Michael Riley - AKA Gunny
        <a href="http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq">http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq</a>
 
        Michael Riley - AKA Gunny - Profile
        <a href="http://stackoverflow.com/users/195983/michael-riley-aka-gunny">http://stackoverflow.com/users/195983/michael-riley-aka-gunny</a>
 
    */
 
     DECLARE @ShowWhiteSpace varchar(8000);
     DECLARE @expanded bit
 
     set @expanded = 1
 
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
 
     if (@expanded = 1)
     begin
 
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
 
    end --if (@expanded = 1)
 
    RETURN(@ShowWhiteSpace)
 
END
go
 
grant execute on [npc].[fn_ShowWhiteSpace] to public
go

 

 

 

 
use [master]
go
 
set ansi_nulls on
go
set quoted_identifier on
go
 

if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_StringClean]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_StringClean]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER function [npc].[fn_StringClean] 
(
 @strIn as varchar(8000)
)
returns varchar(8000)
as
begin
 
    /*
        Blog
        How to remove unprintable characters from various fields in table
        <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql</a>
 
        Profile:
        NaveenCR
        <a href="https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini">https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini</a>
    */
 
    declare @iPtr as int
 
    set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
    while @iPtr > 0 
    begin
    
      set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
 
      set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
     end
 
     return ( @strIn )
 
end
go
 
grant execute on [npc].[fn_StringClean] to public
go

 

 

Lab

Code
 

 

    select 
 
              tblLS.[serverName]
 
            , [serverName_Len] 
                = len(tblLS.[serverName])
 
            , [serverNameTrim]
                 = ltrim(rtrim(tblLS.[serverName]))
 
            , [serverName_Trim(Len]
                 = len(ltrim(rtrim(tblLS.[serverName])))
 
            , [ShowWhiteSpace]
                = [master].dbo.[fn_ShowWhiteSpace](tblLS.servername)
 
            , [serverName_NPClean]
                 = [master].[npc].[fn_StringClean]  (tblLS.[serverName])
 
            , [serverName_NPClean_Len] 
                = datalength([master].[npc].[fn_StringClean]  (tblLS.[serverName]))
 
    from   [DBLAB].dbo.[listofServers.2017022] tblLS
 
    where  (
                (
                    (
                         tblLS.servername 
                            != [master].[npc].[fn_StringClean] (tblLS.servername) 
                    )
                )
          )    

 

 

 

Output

dbo_listofservers_20170222_1036pm

 

Explanation
  1. Here are the columns that are being shown
    1. serverName
      • The actual Server Name
      • The length of the Server Name
    2. serverName Trimmed
      • Using ltrim and rtrim we trim the Server Name
      • We get the length of the trimmed column
    3. Show whitespace
      • Show the serverName and display Non-Printable characters
    4. Show “Cleaned” serverName
      • Show cleaned Server Name
      • Show length of cleaned Server Name

 

Microsoft Connect Items

  1. SQl Server Management Studio should show new lines in records. – by Michael Freidgeim
    • Submitted By :- Michael Freidgeim
    • Item ID :-381955
    • Date Submitted :- 2008-Nov-14th 4 AM
    • Link
    • Description:-
      • 1. If there are newLines characters in the record data, SQl Server Management Studio in Grid view shows the record as one string, without indication that newlines are present.
        It causes confusion to the user, invalid interpretation of the data and even data loss.See scenario in my post
        http://geekswithblogs.net/mnf/archive/2008/11/13/sql-server-management-studio-doesnt-show-new-lines-in-records.aspx
        It will be good if newLines will be shown as actual new lines(consistent with SQL Server 2000 Enterprize Manager) or as some special character(e.g \n or |) to inform user.
        Ideally method on new line presentation could be configured in Tools/Options/Query Results/SQL Server/Results to Grid.
      • 2. By the way, separate suggestion: make Results to Text Maximum Length of the output default  to 8192  (rather than 256) to avoid truncations, that are not obvious.
    • Microsoft Feedback
      • Posted by Seshagiri ( Microsoft ) on 2011-May-31st 7:33 AM
        • Hi Michael,
          This is related to the connect item 381955. We evaluated the feedback carefully and are able to reproduce the cases you listed. However because of the following reasons we are unfortunately not able to attend to this request:

          • 1. new lines in grid
            When using the results to grid option, we use the standard Windows grid control to display the results. This grid control treats each cell value as a plain text and hence the new line characters are ignored. The Save as function when executed from the results section just takes the content from the grid as it is and hence the newline is not found in the new file created from the grid. However if you use the results to text option or the results to file the new line characters are retained. SSMS is not really intended to be a reporting tool, so we are unable to spend much time on the result formatting, especially if some workarounds exist like mentioned above.
          • 2. Max characters when using results to text
            The number specified here is used to preallocate memory for the text column data. A larger value for default could mean lot of unnecessary memory being allocated but not used. Hence it is left to the user to increase the value based on their needs.
            I hope the above sounds reasonable. I am currently closing the issue as a wont-fix. However in case there is anything not mentioned previously please let us know and we will surely take a relook.
          • Thank you
            Seshagiri
            PM, SSMS

SQL Server – Number of Error Log Files

 

Background

Trying to review error logs on one of our SQL Server Instances and discovered that it is likely that the error logs we might have to mine is so far back and we might no longer have it.
And, so took to see if we have properly calibrated the Number of Error Logs Files to keep before rollover.

 

Review

GUI

SQL Server Management Studio ( SSMS )

Screen Shot

configuresqlservererrorlogs

 

Explanation

  1. In the screenshot above we can see that the Number of Errors has not been explicit set and so the system will use the default value of 6
  2. To set it we can check the “Limit the number of error log files before they are recycled” and provide a number to set it to

 

Script

For automation, script is a bit better and here is one …

Code

Stored Procedure

Stored Procedure – errorLog.sp_ConfigureNumberofErrorLogFiles


set nocount on;
go

USE [master]
GO

if schema_id('errorLog') is null
begin
	
	exec('create schema [errorLog] authorization [dbo] ')

end
go

if object_id('[errorLog].[sp_ConfigureNumberofErrorLogFiles]') is null
begin

	exec('create procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] as ')

end
go

alter procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
(
	  @numberofErrorLogsDesired int = 99
	, @scriptOnly bit = 0
)
as 
begin

	set nocount on;
	set XACT_ABORT on;

	declare @strNumberofErrorLogsCurrent	nvarchar(60)
	declare @iNumberofErrorLogsDefault		int
	declare @iNumberofErrorLogsCurrent		int
	declare @numberofErrorLogsMax			int

	declare @strLog							varchar(120)
	
	/*
		Set default values
	*/
	set @numberofErrorLogsMax = 99
	set @strNumberofErrorLogsCurrent = null
	set @iNumberofErrorLogsDefault = -1
	set @iNumberofErrorLogsCurrent = @iNumberofErrorLogsDefault

	if (@numberofErrorLogsDesired > 99)
	begin

		set @numberofErrorLogsDesired = @numberofErrorLogsMax

		set @strLog = 'Maximum Number of errors specified is : ' 
						+ cast(@numberofErrorLogsDesired as varchar(10))
						+ ' but max allowed is '		
						+ cast(@numberofErrorLogsMax as varchar(10))
						+ '!'
						+ ' will use ' 
						+ cast(@numberofErrorLogsMax as varchar(10))

		print @strLog

	end	

	/*
		Read Registry Value
			HKEY_LOCAL_MACHINE
			Branch : Software\Microsoft\MSSQLServer\MSSQLServer
			Item   :- NumErrorLogs
	*/
	exec master.dbo.xp_instance_regread
			  N'HKEY_LOCAL_MACHINE'
			, N'Software\Microsoft\MSSQLServer\MSSQLServer'
			, N'NumErrorLogs'
			, @iNumberofErrorLogsCurrent output

	/*
		If Number of Error Logs is set, then  convert to string
	*/
	if (@iNumberofErrorLogsCurrent is not null )
	begin


		/*
			If Number of Current Logs has been set
		*/
		if (
				(@iNumberofErrorLogsCurrent != @iNumberofErrorLogsDefault )
			)
		begin

			print '@iNumberofErrorLogsCurrent : ' 
					+ cast(
							isNull(@iNumberofErrorLogsCurrent, '')
								as varchar(10)
						  )


		end

		set @strNumberofErrorLogsCurrent
				= cast
					(
						isNull(@iNumberofErrorLogsCurrent, -1)
						as varchar(10)
					)


		/*
			If String is not null
		*/
		if (
				( @strNumberofErrorLogsCurrent is not null )
		   )
		begin

			set @strLog = '@strNumberofErrorLogsCurrent : ' 
								+ @strNumberofErrorLogsCurrent

			print @strLog

		end


	end -- if (@iNumberofErrorLogsCurrent is not null )


	/*
		If Number of Errors Logs is not set to what we desire, then review it
	*/
	if (
			( @iNumberofErrorLogsCurrent != @numberofErrorLogsDesired )
		)
	begin

		if (@scriptOnly = 0)
		begin

			set @strLog = 'Adjusting Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )
						+ ' .. '

			print @strLog


			EXEC master.dbo.xp_instance_regwrite 
						  N'HKEY_LOCAL_MACHINE'
						, N'Software\Microsoft\MSSQLServer\MSSQLServer'
						, N'NumErrorLogs'
						, REG_DWORD
						, @numberofErrorLogsDesired

			set @strLog = 'Adjusted Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end --script only
		else
		begin

			set @strLog = 'Skipping Adjustment of Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end
	end

end

GO


Invoke


use [master]
go

declare @numberofErrorLogsDesired int
declare @scriptOnly				  bit

set @numberofErrorLogsDesired = 99
set @scriptOnly =0

exec [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
		  @numberofErrorLogsDesired = @numberofErrorLogsDesired
		, @scriptOnly = @scriptOnly

Output

RegQueryValueEx() returned error 2 – The System cannot find the file specified

Image

cannotfindthefilespecified

Textual
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
@strNumberofErrorLogsCurrent : -1
Adjusting Number of Error Logs from -1 to 99 .. 
Adjusted Number of Error Logs from -1 to 99

Explanation

By default, the value is missing and so when we invoke master.dbo.xp_instance_regread to read it, we receive a warning message that reads “RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’

 

Registry

The actual registry branch where the information is kept is dependent on the version of the SQL Server Engine and the Instance Name

v2012

Here us what things look list for a SQL Server 2012 Instance, before setting the value

By Default

Image

v2012-default

Explanation

  1. This registry branch above is for a SQL Server v2012 instance
  2. The branch is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

 

Post Setting

Once setting the value for Number of Error Logs to keep our registry will look like what we have set below.

v2012-set

 

Source Control

GitHub

Uploaded to github here

Warning – "The query had to wait 5 seconds for MemoryGrant during execution"

Background

Most of today I spent reviewing  one of our vendors Scheduled Jobs and their corresponding Stored Procedures.

BTW, the jobs are SQL Server based and scheduled through SQL Server Agent.

 

Query Plan Warnings

As I reviewed each SQL Statement noticed a big warning that reads “The query had to wait 5 seconds for MemoryGrant during execution”.

Image

TheQueryHadToWait5Seconds (cropped)

 

Query Statement

Query Text

Here is the query text


UPDATE	a
SET	a.HasMajor = CASE 
			WHEN am.ArticulationId is NOT NULL THEN 1
			ELSE 0
		    END 

FROM		tArticulation a

LEFT JOIN tArticulationMajor am

	ON a.CourseArticulationID_Oltp = am.ArticulationId

;

Explanation

What does the query do

  1. It performs a left outer join on tArticulation and tArticulationMajor
  2. Each time an ArticulationMajor exists for an Articulation we stamp the HasMajor column with 1
  3. On the other hand, for Articulations missing tArticulationMajor we stamp them with 0

 

Rewrite – Using Alternate Joins

The query statement appears ripe for revision

 

Rewrite as Inner Join


UPDATE	a

SET	a.HasMajor = 1
					
FROM	tArticulation a

INNER JOIN tArticulationMajor am

	on a.CourseArticulationID_Oltp = am.ArticulationId

	;

 

Rewrite as Correlation Join / Where Exists


UPDATE	a

SET		a.HasMajor = 1
						
FROM		tArticulation a

where exists
	(
	   select 1

	   from   tArticulationMajor am

	   where a.CourseArticulationID_Oltp = am.ArticulationId

       )

;

 

Compare Original Against Alternate Joins

Compare Query Plans

Image

compareQueryPlans (cropped)

 

Explanation

  1. While the original left outer join comparatively comes in at 100%, our two revisions come in at 0%

 

Compare Statistics IO

Image

compareStatisticsIO

 

 

Number of Records Affected

Image

NumberofReordsAffected

 

 

 

Rewrite – Using Merge Statement

Using Merge Statement, we can compare and merge data from two different sources, as well

 

Rewrite Using Merged / When Matched

Explanation

In the sample code we set HasMajor to 1 when we have records in the source table

Code



merge tArticulation tblA

USING tArticulationMajor tblAM

	on tblA.[CourseArticulationID_Oltp] = tblAM.[ArticulationId]

WHEN MATCHED THEN
	UPDATE
	   SET	tblA.HasMajor = 1
	;

 

Rewrite Using Merged / When Matched and Not Matched

Explanation

In the sample code we set HasMajor to 1 when we have records in the source table.

And, when not matched we set to 0.


merge tArticulation tblA

USING tArticulationMajor tblAM

	on tblA.[CourseArticulationID_Oltp] = tblAM.[ArticulationId]

WHEN MATCHED THEN
	UPDATE
		SET	tblA.HasMajor = 1
	
	WHEN NOT MATCHED BY SOURCE THEN
	UPDATE
		SET	tblA.HasMajor = 0
	
	;

 

Compare Original Against Alternate Merge Statements

Compare Query Plans

Image

queryPlans-mergeStatements

 

Explanation

In comparison

  1. Weigh
    • The original query is at 49%
    • The Merge Statement with Matched is at 0%
    • On the other hand the Merge Statement with Matched and Not Matched is at 51%
  2. Parallelism
    • Both the original query with left outer join and the Merge\Not Matched query relies on Parallism
    • While the Merge and Matched does not use Parallelism

 

Compare Statistics IO

Image

StatisticsIO-mergeStatement

 

Explanation

  1. The Original query and the Merge Statement with matched and not matched experience similar IO
  2. The Merge with Update and Match alone experience limited IO

 

Merge Rewrite – Number of Records Affected

Image

NumberofRecordsAffected - MergeStatement

 

Metrics

Query Cost

Image

Left Outer Join

Summary-LeftOuterJoin (Cropped)

 

Inner Join

Summary-InnerJoin (Cropped)

Merged When Matched / Alone

Summary-MergedWhenMatchedAlone (Cropped)

 

Correlation Join / Using Exists

Summary-Exists ( Cropped )

Merged When Matched / Alone

Summary-MergedWhenMatchedAndNotMatched (cropped)

 

Detailed

    • Left Outer Join
      • Cached plan size = 40KB
      • Degree of Parallelism = 4
      • Estimated Subtree Cost = 33.4372
      • Memory Grant = 21856
      • Estimated Number of Rows = 328914
    • Inner Join
      • Cached plan size = 24KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.0631069
      • Memory Grant = 1024
      • Estimated Number of Rows = 32.5183
    • Exists
      • Cached plan size = 24KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.0515091
      • Memory Grant = None
      • Estimated Number of Rows = 32.5183
    • Merged when matched alone
      • Cached plan size = 32KB
      • Degree of Parallelism = 1
      • Estimated Subtree Cost = 0.063129
      • Memory Grant = 1024
      • Estimated Number of Rows = 32.5183
    • Merged when matched and not matched
        • Cached plan size = 48KB
        • Degree of Parallelism = 4
        • Estimated Subtree Cost = 34.7932
        • Memory Grant = 22152
        • Estimated Number of Rows = 328914

Tabulated

 

Property Left Outer Join Inner Join Correlated Not Exists Merge Join – When Matched Merge Join – When Matched & Not Matched
Cached Plan Size 40KB 24KB  24 KB  32KB  48KB
Degree of Parallelism  4  1  1  1  4
Memory Grant  21856  1024  1024  22152
Estimated Subtree Cost  33.4372  0.0631069  0.0515091  0.063129  34.7932
Estimated Number of Rows  328914  32.5183  32.5183  32.5183  328914

 

 

Statistics I/O

Image

Left Outer Join

summary-statisticsio-leftouterjoin

Inner Join

summary-statisticsio-innerjoin

Correlated Join / Not Exists

summary-statisticsio-correlatedjoin-exists

Merge – When Matched / Update

summary-statisticsio-merge-whenmatchedupdate_20170203_0816am

Merge – When Matched – Update  & When Not Matched – Update

summary-statisticsio-merge-whennotmatchedupdate_20170203_0816am

 

Tabulated

 

Property Left Outer Join Inner Join Correlated Not Exists Merge Join – When Matched Merge Join – When Matched & Not Matched
tArticulationMajor Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0 Scan count 1, logical reads 2, physical reads 0
workfile  Scan count 0, logical reads 0, physical reads 0  Scan count 0, logical reads 0, physical reads 0
worktable  Scan count 0, logical reads 0, physical reads 0 Scan count 0, logical reads 0, physical reads 0 Scan count 0, logical reads 0, physical reads 0  Scan count 0, logical reads 0, physical reads 0
tArticulation  Scan count 5, logical reads 1007936 Scan count 15, logical reads 145, physical reads 0 Scan count 15, logical reads 145, physical reads 0 Scan count 15, logical reads 145, physical reads 0 Scan count 5, logical reads 1007936, physical reads 0
worktable scan count 0, logical reads, physical reads scan count 0, logical reads 0, physical reads 0

 

 

Explanation

  1. Workfiles
    • Left Outer Join and Merge Join
      • Queries that include and have a marker for records that do not match
  2. WorkTable
    • Inclusive
      • Left Outer Join, Inner Join, Merge Join when matched, Merge Join when matched and not matched
      • Where?
        • Left Outer Join
          • Hash Match
          • Sort
            • Estimated Rows = 328914
        • Inner Join
          • Nested Loop
          • And, then Sort
            • Estimated Rows = 32
        • Merge When Matched Update / Alone
          • Nested Loop
          • Sort
            • Estimated Rows = 32
        • Merge When Matched – Update / When not matched – Update
          • Sort
            • Estimated Rows = 328914
    • Excluded
      • Correlated Not Exists

 

Terminology

 

Term Meaning Link
Worktables The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed. Link
workfile Work files are similar to work tables but are created strictly by hashing operations. Work files are used to store temporary results for hash joins and hash aggregates.  Link

 

 

Operators

WorkFile

Let us quickly and look at the Queries that references Workfile in their Query Plan

Occurrences

Left Outer Join

summary-hashmatch-leftouterjoin

Merge – When Not matched

summary-hashmatch-mergenotmatched

 

Findings

  • We find that each time Hash Matches are found in the Query Plan, we see corresponding workfile entries

Optimizations

Work Table

  1. Worktable caching is improved.
    Link

    • Version 2005 and Higher
      • When a query execution plan is cached, the work tables needed by the plan are not dropped across multiple executions of the plan but merely truncated. In addition, the first nine pages for the work table are kept.
    • version 2000
      • In SQL Server 2000, the work tables used during query plan execution are dropped.
    • Because the work table is cached, the next execution of the query is faster.
    • When the system is low on memory, the execution plan may be removed from the cache and the associated work tables dropped as well.
    • Both SQL Server 2000 and SQL Server 2005 use a small global pool of pre-allocated pages and extents that make the initial creation of work tables faster.

 

Summary

Honestly, have to come back and add in more metrics, and generally tie things up.

There are some things that will later help our understanding of Workfiles and Worktables.

WorkFiles occur when we need records that are not included within our Join Clause.

Whereas, worktables are hallmarks of the Sort Operator.

But, again how true this is, will come later.

Transact SQL – File Management – File Names

Background

Unfortunately Transact SQL does not come with rich File Management Tools.

It seemingly lacks native APIs for parsing full file names into folder names, filenames, and extensions.

And, so I always take to the Net when I need to do the above

 

Functions

Here are some scalar functions that I have been able to gather in one place from here and there on the Net.

 

File List

Posted the files to Github and here is the repository.

 

File Name Use
fileManagement.schema.sql Create Schema fileManagement, if it does not exist
fileManagement.udfn_GetDirectoryPath.sql From full file name, get Directory Path
fileManagement.udfn_getFileName.sql From full file name, get File name
fileManagement.udfn_getFileNameExt.sql From full file name, get File Extension
fileManagement.udfn_getFileNameWithoutExt.sql From full file name, get File name without extension

 

 

Modules

Code

fileManagement.udfn_GetDirectoryPath


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_GetDirectoryPath]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_GetDirectoryPath]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_GetDirectoryPath]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @PathLength INT

    SET @ReversedPath = REVERSE(@Path)
    SELECT @PathLength = CHARINDEX('\', @ReversedPath)
    SELECT @FileName = LEFT(@Path, LEN(@Path) - @PathLength)
    RETURN @FileName
END

go

grant execute on [fileManagement].[udfn_GetDirectoryPath] to public
go

 

fileManagement.udfn_getFileName


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_getFileName]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileName]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_getFileName]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)

    SET @ReversedPath = REVERSE(@Path)
    SELECT @FileName = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)

    RETURN @FileName
END

go

grant execute on [fileManagement].[udfn_getFileName] to [public]

go


fileManagement.udfn_getFileNameExt


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if object_id('[fileManagement].[udfn_getFileNameExt]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileNameExt]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go


ALTER FUNCTION [fileManagement].[udfn_getFileNameExt]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @ExtLength INT
	DECLARE @ext       varchar(30)

    SET @ReversedPath = REVERSE(@Path)
    SET @FileName = ''
    SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
    IF (@ExtLength > 0) 
    BEGIN 
       SELECT @ext = RIGHT(@Path, @ExtLength - 1)
    END
    RETURN @ext
END
go


grant execute on [fileManagement].[udfn_getFileNameExt] to public
go

fileManagement.udfn_getFileNameWithoutExt


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_getFileNameWithoutExt]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileNameWithoutExt]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_getFileNameWithoutExt]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @ExtLength INT

    SET @ReversedPath = REVERSE(@Path)
    SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
    SELECT @FileName = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)
    SELECT @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)
    RETURN @FileName
END
go


grant execute on [fileManagement].[udfn_getFileNameWithoutExt] to [public]
go


Sample Invocation

Code



declare @filename varchar(400)

set @filename = 'Z:\SQLBackups\ppsivr\FULL\ppsivr_FULL_20170127_115058.bak' 

--display full file name
print '@filename ( full) : ' + [master].fileManagement.udfn_GetDirectoryPath(@filename)

--display directory path
print '@folder : ' + fileManagement.udfn_GetDirectoryPath(@filename)

--display file name
print '@filename : ' + [master].fileManagement.udfn_getFileName(@filename)

--display file name without extenion
print '@filename (without ext): ' + [master].fileManagement.udfn_getFileNameWithoutExt(@filename)

--display file name
print '@ext : ' + fileManagement.udfn_getFileNameExt(@filename)

Output

displayfileinfo

Like You

Like you, I wish back to when things were simpler.

Like HERE ( The Deele – Two Occasions featuring Babyface)

SQL Server – Reading Instance Configuration saved in Registry

Background

For a script that I am working on, I need to access System Properties such as the base folder of the errorlog.

 

SQL Server Management Studio ( SSMS )

Of course the information is available via SSMS, just click on the Server’s Property and you will see something like this…

sqlserver-mssqlserver-20170119-1107am

 

Explanation

  1. We can that “Root Directory” is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL

 

Registry

Registry – CPE

Knowing the value that we are searching for took to the registry and searched for “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL“.

Ended up here…

sqlserver-cpe

Registry – Setup

Most of the most important information is saved in the Setup branch.

sqlserver-setup-20170119-1126am

 

Registry – MSSQLServer

Btw, the MSSQLServer path is very important, as well.

sqlserver-mssqlserver-20170119-1116am

Code

xp_instance_regread

Let us read ErrorDumpDir from the CPE branch

 

Code Snippet


DECLARE @rootKey	 NVARCHAR(500)
DECLARE @path		 NVARCHAR(500)
DECLARE @name		 NVARCHAR(500)
DECLARE @value		 NVARCHAR(1000)

set @rootKey = N'HKEY_LOCAL_MACHINE'
set @path    = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
set @name    = N'ErrorDumpDir'

EXEC master..xp_instance_regread
         @rootkey = @rootKey
       , @path    = @path
       , @name	  = @name
       , @value   = @value output

PRINT 'Error Dump Dir :' + isNull(@value, '')


 

Output

errordump

 

Knowledge Base

Please keep in mind that xp_instance_regread is an extended Stored Procedure.

As there could be a few SQL Server Instances on a box, Microsoft abstracted things a bit and xp_instance_regread is able to read information for the specific Instance it is ran on.

The more generic SP that does not abstract for the specific instance is xp_RegRead.

SQL Server – Recreate System Database – msdb

Background

One of my brothers called me and said that that he is unable to use his beloved SQL Server Management Studio  ( SSMS ) to edit records in a table.

And, that SQL Server says his msdb database his corrupted.

 

Remediate

Restore from Backup

To remediate I asked if he takes backup of the msdb database and he says No.

And, so I know that is not quite a good look.

 

Recreate MSDB from Scratch

Overview

In the sample script below, we will recreate the MSDB Database.

SQL Server comes with a script called instmsdb.sql.  The file creates the msdb database.

We need to make sure that the database and its files do not exist and so we will do some housekeeping and rid self of the existing files.

It is a long winded code and so let us talk about it a bit.

 

Steps

  1. Preparation
    • Base Values
      • Set Instance Name
      • Set Folder Location of the instmsdb.sql file
      • Set Default Folder Location of system database files
      • Set File names for msdb data and log files
  2. Computed Values
    • Set full file name for MSDB Data and Log files
  3. Get Current Timestamp
  4. Compute Backup folder where we will keep the current data and log files for the msdb database
  5. If Backup folder does not exist, create it
  6. Stop MS SQL Server Engine and other SQL Server Services
  7. Start MS SQL Server Engine in minimal mode
    • Trace Flags
      • 3608
  8. Using sqlcmd.exe, detach msdb database
  9. Using sqlcmd.exe, recreate msdb database using instmsdb.sql
  10. Stop MS SQL Server
  11. Restart MS SQL Server in regular mode

 

Code


@echo on
setlocal

Rem set instance Name to Default
set "_instname=."

Rem Set Folder for location of instmsdb.sql
set _installFLD=E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install

Rem Set Folder for Location of System Database files such as ( master, model, tempdb, msdb)
set _dbSystemFLD=E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

Rem Set filename for MSDB Database & Log file
set _dbMSDBData=MSDBData.mdf
set _dbMSDBLog=MSDBLog.ldf

Rem Set Full File Name for Data and Log files
set _datafile=%_dbSystemFLD%\%_dbMSDBData%
set _logfile=%_dbSystemFLD%\%_dbMSDBLog%


REM serverfault
REM Need leading zero for batch script using %time% variable
REM Answered by David
REM http://serverfault.com/questions/147515/need-leading-zero-for-batch-script-using-time-variable/529445
:prepare time stamp 
set year=%date:~10,4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
:replace leading space with 0 for hours < 10
if "%hour:~0,1%" == " "  set hour=0%hour:~1,1%
set minute=%time:~3,2%
set second=%time:~6,2%
set _timeStamp=%year%%month%%day%_%hour%%minute%%second%

set _BACKUPFLD=%_dbSystemFLD%\BACKUP\%_timeStamp%

REM If Backup Folder does not exist, mkdir Backup Folder 
if not exist "%_BACKUPFLD%" mkdir "%_BACKUPFLD%"

REM Stop MSSQLServer
net stop mssqlserver /y

REM Start MSSQLServer
NET START MSSQLSERVER /T3608


REM Using SQLCMD Detach msdb database
sqlcmd  -E -S %_instname% -d master -Q "set quoted_identifier off; if db_id('msdb') is not null begin exec sp_detach_db 'msdb'; end "


REM If MSDB Data & Log file already exist at target folder, pleaes move to backup folder 
if exist "%_datafile%" move "%_datafile%" "%_BACKUPFLD%"
if exist "%_logfile%"  move "%_logfile%"  "%_BACKUPFLD%"


REM Using SQLCMD run instmsdb.sql
SQLCMD -E -S %_instname% -i "%_installFLD%\instmsdb.sql" -o instmsdb_%_timeStamp%.log


REM Stop MSSQLServer
net stop mssqlserver /y


REM Start MSSQLServer
NET START MSSQLSERVER


endlocal


Source Code Control

GitHub

Here is the URL for our repository

 

References

  1. Database Features > Databases (Database Engine) > System Databases > Rebuild System Databases
    Link