SQLQueryStress – Parameter Substitution

Background

A quick follow-up to our last post on SQLQueryStress.

BTW, that post is here.

Parameter Substitution

Query

Original Query

Code

insert into [dbo].[activity]
default values

Revised Query

Code

select
          tblP.BusinessEntityID
        , tblP.FirstName
        , tblP.LastName
        , tblP.MiddleName

from  [Person].[Person] tblP

where
        (
                ( tblP.LastName = @lname )
            and ( tblP.FirstName = @fname )
        )

Code – Explanation

In the query above, we have two arguments @lname and @fname.

Substitution

Outline
  1. Click the “Parameter Substitution” button
  2. In the “Parameter Substitution” window
    • Click the “Database” button to set the Database that the parameter arguments reside on
    • In the “Parameter Query” textbox, enter a query that will fetch the parameter arguments
    • Click the “Get Columns” button to have the Database return the list of columns that will be returned from running the Query
    • Match each parameter to its corresponding database column
Images
Image – Parameter Substitution

Click on the “Parameter Substitution” button.

parameterSubstitution__Initiate__20180821_1126AM
Image – Parameter Query

Enter the query that will fetch values for all the data-set that will feed our parameters.

parameterSubstitution__ParameterQuery_20180821_1102AM.PNG

Image – Get Columns – Get Columns

Click on the “Get Columns” button.

Clicking on the “Get Columns” button generates the column names from the Parameter Query.

parameterSubstitution__GetColumns_Before_20180821_1104AM.PNG

Image – Get Columns – Align

Map parameters with column names.

parameterSubstitution__GetColumns_After_20180821_1105AM.PNG

 

Load

SQL Profiler

Images

Image 01

payload_20180821_1210PM_01.PNG

Image 02

payload_20180821_1211PM_02.PNG

Explanation

We can observe variability in the RPC:Completed Event captured for each iteration.

SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

Sql Server – Integration Services – SSIS Catalog ( SSISDB ) – Metadata – List Parameters

Introduction

Microsoft continues to build out Sql Server Integration Services (SSIS).

There is support for various package repositories; inclusive are Flat files, MSDB, and SSIS DB.

SSIS Catalog / SSISDB

If you go with SSISDB, it is easy to review the package configuration data.

 

SQL


use [SSISDB]
go

SELECT 
		  [folder] 
			= folders.folder_id

		, [folderName]
			 = folders.[name]

		, [projectName] 
			= projects.[name]

		, [packageName]
			= [packages].[name]

		, [objectType]
			 = case

				when ([paras].[object_type] = 20) then 'Project'
				when ([paras].[object_type] = 30) then 'Package'
				else cast
						(
							[paras].[object_type] 
								as varchar(30)
						)

			   end	

		, [parameterName]
			= [paras].parameter_name

		, [parameterDataType]
			= [paras].data_type

		, [designeDefaultValue]
			= [paras].[design_default_value]

		, [defaultValue]
			= [paras].[default_value]

		, [valueSet]
			= case
				when ([paras].[value_set] =1) then 'Yes'
				else 'No'
		      end

		, [projectDeployTime]
			= convert(varchar(30), [projects].[last_deployed_time], 100)

FROM   [catalog].[folders] folders

INNER JOIN [catalog].[projects] projects 

	ON projects.[folder_id]=folders.[folder_id]

INNER JOIN [catalog].[packages] packages 
	ON packages.[project_id]=projects.[project_id]

INNER JOIN [catalog].[object_parameters] paras 
	ON  ( paras.[object_name]=packages.[name] )
	AND ( paras.[project_id]=packages.[project_id])

 

 

Output

Acknowledgment

Nothing here original.

Trained SQL Server Profiler and catch the SQL it is using.

SQL Server – “Lock Timeouts” – Trigger Timeouts – SSMS – Table Designer

Background

In our initial post we spoke about experiencing an Outage.

Our Clients in the Development side of the house explained they have been experiencing “Lock Timeouts“.

We wanted to see what tools we can use to detect similar occurrences on the DB.  And, we cited SQL Server Profiler as one of those tools.

In this post, we will attempt to simulate time outs due to expired Lock requests and see how well SQL Server Profiler performs.

 

Lab

Outline

  1. Start SQL Server Profiler and set to capture event
  2. Create a bare minimum table
  3. Add data
    • Start Transaction
    • Add a couple of records
    • Temporarily leave Transaction Uncommitted
  4. In another session, return to Table designer & initiate changes
    • Add one or two new columns, attempting to make design changes

Steps

SQL Server Profiler

Here are the events that we said we will capture:

Image

Image – Events Selection

Image – Edit Filter

 

Tabulation

Tabulation – Events Selection
Event Category Event
 Locks  Lock:Cancel
 Stored Procedures  RPC:Completed
 TSQL  SQL:Batch Completed

 

 

Tabulation – Filter
Column Value
 Duration  30000

 

 

Create a Bare Minimum Table

Image

Explanation

In the screen above, we have created a table and added a couple of columns ( personID and personName).

 

Attempt to add data

Script


set nocount on
go
set XACT_ABORT on
go

use [DBLab]
go

declare @transactionComplete bit

set @transactionComplete = 0

begin tran

	insert into [dbo].[person]
	default values;

 

Table Designer

Table Designer – Adding new column – dateAdded

Image

 

Table Designer – Execution Timeout Expired

Image

Textual

'person' table
- Unable to modify table.
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

 

SQL Server Profiler

Image

Lock:Cancel

Tabulated
Event Category Event Columns Values
 Lock  
 Lock:Cancel
 TextData
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:23:18.190
 EndTime  2017-03-25 16:23:18.190
 Mode  2-SCH-M
 ObjectID 110623437
 ObjectID2  110623437
 OwnerID  1-TRANSACTION
 DatabaseID  7
 DatabaseName  DBLAB
 Hostname  DADENIJI
 IntegerData2  0-LOCK

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

SQL: Batch Completed

Tabulated
Event Category Event Columns Values
 T-SQL  
SQL: Batch Completed
 TextData  ALTER TABLE dbo.person ADD dateAdded null
 Application  Microsoft SQL Server Management Studio
 NTUserName  dadeniji
 Duration  29999
 StartTime  2017-03-25 16:22:48.190
 EndTime  2017-03-25 16:23:18.190

 

 

Explanation
  • You want to pay attention to ObjectID and Database ID
    • Those two will lead you to Database, Schema, and Object name
  • Mode
    • Will lead you to desired Lock
      • In our case, 2-SCH-M or Schema Modification Lock
  • And, of course Application, Host, and Username, and TextData
    • Determine causation and forensics

 

 

Dedicated

Dedicated to MSFT’s SQL Server Team.

 

 

SQL Server – Server Side Traces

Introduction

One of our batch processes occasionally fails.

Annoying so, because there is no error messages.

But, we noticed it fails on a specific day.

We have a need to capture the SQL statements and corresponding errors, if any.

Here we try to use SQL Server Profiler and customize the generated code to Server Side Trace.

Code

From SQL Server Profiler Trace to Stored Procedure

Using SQL Server Profiler, we created a trace file.

Here are is quick rework to extend it a bit. The changes are very minimal.

And, there are …

  1. Flexibility
    • Targeted Folder
    • Targeted Login
  2. Terminate Running Traces?

Stored Procedure – dbo.sp_ServerSideTraceInitiate


/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 02/16/2016  05:47:49 PM         */
/****************************************************/
use master
go

if object_id('dbo.sp_ServerSideTraceInitiate') is null
begin
	
	exec('create procedure [dbo].[sp_ServerSideTraceInitiate] as select 1/0 as [shell] ')

end
go

alter procedure dbo.sp_ServerSideTraceInitiate
(
	  @tracefolder				sysname
	, @loginname				sysname = null
	, @terminateRunningTraces	bit = 0
	, @createTrace				bit = 1
	, @traceID					int	= null output	
	, @traceRC					int	= null output	
)
as
begin

	set XACT_ABORT ON;
	set nocount on;

	-- Create a Queue
	declare @rc int
	declare @traceIDNull int
	declare @maxfilesize bigint
	declare @on bit

	-- Set the Filters
	declare @intfilter int
	declare @bigintfilter bigint

	--declare @loginname sysname
	declare @tracefolderBackup sysname
	declare @tracefolderBackupTimestamp sysname
	declare @tracefile sysname
	declare @tracefileFullName sysname
	declare @folderAlreadyInUseByRunningTrace sysname
	declare @bCommit bit


	declare @OSCommandSyntaxCreateFolder varchar(600)
	declare @OSCommandSyntaxDeleteFile   varchar(600)
	declare @OSCommandSyntaxMoveFile     varchar(600)

	declare @logSyntaxTraceTerminate	 varchar(600)
	declare @logSyntaxTraceCreating		 varchar(600)
	declare @logSyntaxTraceFilterOnLogin varchar(600)
	declare @logSyntaxTraceStart		 varchar(600)	

	declare @OSCommandCreateFolder					varchar(600)
	declare @OSCommandDeleteFile					varchar(600)
	declare @OSCommandCreateFolderBackup			varchar(600)
	declare @OSCommandCreateFolderBackupTimestamp	varchar(600)
	declare @OSCommandMoveFile						varchar(600)

	declare @logTraceTerminate		varchar(600)
	declare @logTraceCreate			varchar(600)
	declare @logTraceFilterOnLogin	varchar(600)
	declare @logTraceStart			varchar(600)

	declare @dateCurrent datetime

	declare @CHAR_TAB varchar(30)

	declare @waitBetweenTraceTermination varchar(30)

	declare @traceIDRunning int
	declare @strTraceIDRunning varchar(30)
	declare @tracePathRunning sysname
	
	declare @traceActionRequested sysname

	declare @iNumberofRecordsAffected int

	declare @timestamp varchar(100)

	declare @strTraceID varchar(60)

	declare @TRACE_STATUS_REQUEST_STOP int
	declare @TRACE_STATUS_REQUEST_DISCARD int
	declare @TRACE_STATUS_REQUEST_START int

	declare @bFilterLogin bit

	set @traceIDNull = -1
	set @CHAR_TAB = char(9)

	set @TRACE_STATUS_REQUEST_STOP = 0
	set @TRACE_STATUS_REQUEST_DISCARD = 2
	set @TRACE_STATUS_REQUEST_START = 1

	set @on = 1

	set @bFilterLogin = 0
	set @waitBetweenTraceTermination = '00:00:05'

	--set @loginname=N'emmdatafeed'
	if (
			   (@loginname is null)
			or (ltrim(@loginname) = '')
		)
	begin

		set @loginname=N'everyone'
		set @bFilterLogin = 0

	end
	else
	begin

		set @bFilterLogin = 1

	end

	/*
		Set String Format Pattern
	*/
	set @OSCommandSyntaxCreateFolder ='If not exist "%s" mkdir "%s" '
	set @OSCommandSyntaxDeleteFile = 'If exist "%s*" del "%s*" '
	set @OSCommandSyntaxMoveFile = 'If exist "%s\*.trc" move "%s\*.trc" "%s\" '

	set @logSyntaxTraceTerminate
			 ='Terminate Trace ID %s running against %s '

	set @logSyntaxTraceCreating
			= 'Creating Trace. Targeting Folder %s ...'

	set @logSyntaxTraceFilterOnLogin
			= 'Setting Filter on Trace %s. Filtering for login %s ....'

	/*
		Initialize Variables
	*/
	set @maxfilesize = 100
	set @bCommit = 1
	set @dateCurrent = getdate()

	set @tracefile = @loginname 
	set @tracefolderBackup = @tracefolder + '\Backup\'
	set @tracefileFullName = @tracefolder + '\' + @tracefile

	set @timestamp =
				--Year
				+ datename(year, @dateCurrent)

				-- Month
				+ replicate('0', 2 - len(datepart(month, @dateCurrent)))
				+ rtrim(cast(datepart(month, @dateCurrent) as char(2)))

				-- Day
				+ replicate('0', 2 - len(datepart(day, @dateCurrent)))
				+ rtrim(cast(datepart(day, @dateCurrent) as char(2)))

				-- Hour
				+ replicate('0', 2 - len(datepart(hour, @dateCurrent)))
				+ rtrim(cast(datepart(hour, @dateCurrent) as char(2)))

				-- Minute
				+ replicate('0', 2 - len(datepart(minute, @dateCurrent)))
				+ rtrim(cast(datepart(minute, @dateCurrent) as char(2)))

	/*
		Prepare Backup Folder Name Based on Current Date & Time
	*/
	set @tracefolderBackupTimestamp 
			= @tracefolderBackup 
				+ '\'
				+ @timestamp


	/*
		If we are to terminate running Traces, let us do so..

			Access sys.traces to find running traces
	*/
	if (@terminateRunningTraces = 1)
	begin

		print 'Looking for running traces : ....'

		set @traceIDRunning = @traceIDNull
		set @iNumberofRecordsAffected = -1

		while (
				      ( @traceIDRunning is not null )
				  and ( @iNumberofRecordsAffected != 0)
			  )	
		begin

			/*
				Get Trace ID and Path of earliest running Trace
				  targetting same path as our's	
			*/
			select 
					  @traceIDRunning = tblST.id
					, @tracePathRunning = tblST.[path]

			from   sys.traces tblST

			where  tblST.[is_default] != 1

			and   tblST.[id] =
						(

							select min(id)
							from   sys.traces tblST
							where  tblST.[path] like + @tracefolder + '%'

						)

			set @iNumberofRecordsAffected = @@ROWCOUNT

			print '@traceIDRunning : '
					+ cast(isNull(@traceIDRunning, 0) as varchar(30))

			if (
					    ( @traceIDRunning != @traceIDNull)
					and (@iNumberofRecordsAffected >0)
			   )
			begin

				set @strTraceIDRunning = cast(@traceIDRunning as varchar)

				exec master.dbo.xp_sprintf
						   @logTraceTerminate output
						 , @logSyntaxTraceTerminate
						 , @strTraceIDRunning
						 , @tracePathRunning

				print @CHAR_TAB + @logTraceTerminate 

				/*
					Stop the Trace
				*/
				EXEC sp_trace_setstatus 
						   @traceid = @traceIDRunning
						,  @status =  @TRACE_STATUS_REQUEST_STOP

				/*
					Discard the Trace
				*/
				EXEC sp_trace_setstatus
						  @traceid = @traceIDRunning
						, @status = @TRACE_STATUS_REQUEST_DISCARD

				/*
					Wait for trace stop & discard to complete
				*/
				print @CHAR_TAB 
						+ @CHAR_TAB 
						+ 'Waiting between trace terminations...'

				WAITFOR DELAY @waitBetweenTraceTermination

			end

		end

	end


	/*
		If Traces are still running, please say so and exit
	*/
	if exists
		(
	
			select * 
			from   sys.traces tblST
			where  tblST.[is_default] != 1
			and    tblST.[path] like + @tracefolder + '%'
		)
	begin

		set @folderAlreadyInUseByRunningTrace = 'SQL Server Profiler\Trace running against ' 
													+ @tracefolder
			
		raiserror(@folderAlreadyInUseByRunningTrace, 16,1)

		return

	end


	--print '@tracefolderBackupTimestamp : ' + @tracefolderBackupTimestamp


	-- Please replace the text InsertFileNameHere, with an appropriate
	-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
	-- will be appended to the filename automatically. If you are writing from
	-- remote server to local drive, please use UNC path and make sure server has
	-- write access to your network share

	--set 'If exist "F:\Microsoft\SQLServer\Profiler\emmdatafeed.trc" del "F:\Microsoft\SQLServer\Profiler\emmdatafeed.trc" '
	/*
		Prepare Command
	*/
	exec master.dbo.xp_sprintf
			   @OSCommandCreateFolder output
			 , @OSCommandSyntaxCreateFolder
			 , @tracefolder
			 , @tracefolder

	exec master.dbo.xp_sprintf
			   @OSCommandDeleteFile output
			 , @OSCommandSyntaxDeleteFile
			 , @tracefileFullName
			 , @tracefileFullName

	exec master.dbo.xp_sprintf
			   @OSCommandCreateFolderBackup output
			 , @OSCommandSyntaxCreateFolder
			 , @tracefolderBackup
			 , @tracefolderBackup

	exec master.dbo.xp_sprintf
			   @OSCommandCreateFolderBackupTimestamp output
			 , @OSCommandSyntaxCreateFolder
			 , @tracefolderBackupTimestamp
			 , @tracefolderBackupTimestamp

	exec master.dbo.xp_sprintf
			   @OSCommandMoveFile output
			 , @OSCommandSyntaxMoveFile
			 , @tracefolder
			 , @tracefolder
			 , @tracefolderBackupTimestamp


	/*
		Create Base Folder
	*/
	print '@OSCommandCreateFolder : ' + @OSCommandCreateFolder

	exec master.dbo.xp_cmdshell
			   @OSCommandCreateFolder
			,  no_output 

	/*
		Create Base's Backup Folder
	*/
	print '@OSCommandCreateFolderBackup : ' + @OSCommandCreateFolderBackup

	exec master.dbo.xp_cmdshell
			   @OSCommandCreateFolderBackup
			,  no_output 

	/*
		Create Base's Backup TimeStamp Folder
	*/
	print '@OSCommandCreateFolderBackup : ' + @OSCommandCreateFolderBackupTimestamp

	exec master.dbo.xp_cmdshell
			   @OSCommandCreateFolderBackupTimestamp
			,  no_output 

	/*
		Move files from base to backup\timestamp
	*/
	print '@OSCommandMoveFile : ' + @OSCommandMoveFile

	exec master.dbo.xp_cmdshell
			   @OSCommandMoveFile
			,  no_output 
	

	if (@createTrace = 0)
	begin

		print 'Skipping Trace Creation!'
	end
	else
	begin

		set @traceActionRequested = 'sp_trace_create'

		--exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 

		exec master.dbo.xp_sprintf
			   @logTraceCreate output
			 , @logSyntaxTraceCreating
			 , @tracefolder

		print @logTraceCreate

		exec @rc = sp_trace_create 
						  @traceid = @traceID output
						, @options = 0
						, @tracefile = @tracefileFullName
						, @maxfilesize = @maxfilesize
						, @stoptime = NULL 
						, @filecount = NULL

		if (@rc != 0) goto error

		set @strTraceID = cast(@traceID as varchar(30))

		-- Client side File and Table cannot be scripted

		-- Set the events

		set @traceActionRequested = 'sp_trace_setevent'

		exec sp_trace_setevent @TraceID, 22, 7, @on
		exec sp_trace_setevent @TraceID, 22, 31, @on
		exec sp_trace_setevent @TraceID, 22, 8, @on
		exec sp_trace_setevent @TraceID, 22, 64, @on
		exec sp_trace_setevent @TraceID, 22, 1, @on
		exec sp_trace_setevent @TraceID, 22, 9, @on
		exec sp_trace_setevent @TraceID, 22, 41, @on
		exec sp_trace_setevent @TraceID, 22, 49, @on
		exec sp_trace_setevent @TraceID, 22, 6, @on
		exec sp_trace_setevent @TraceID, 22, 10, @on
		exec sp_trace_setevent @TraceID, 22, 14, @on
		exec sp_trace_setevent @TraceID, 22, 26, @on
		exec sp_trace_setevent @TraceID, 22, 3, @on
		exec sp_trace_setevent @TraceID, 22, 11, @on
		exec sp_trace_setevent @TraceID, 22, 35, @on
		exec sp_trace_setevent @TraceID, 22, 51, @on
		exec sp_trace_setevent @TraceID, 22, 4, @on
		exec sp_trace_setevent @TraceID, 22, 12, @on
		exec sp_trace_setevent @TraceID, 22, 20, @on
		exec sp_trace_setevent @TraceID, 22, 60, @on
		exec sp_trace_setevent @TraceID, 33, 7, @on
		exec sp_trace_setevent @TraceID, 33, 31, @on
		exec sp_trace_setevent @TraceID, 33, 8, @on
		exec sp_trace_setevent @TraceID, 33, 64, @on
		exec sp_trace_setevent @TraceID, 33, 1, @on
		exec sp_trace_setevent @TraceID, 33, 9, @on
		exec sp_trace_setevent @TraceID, 33, 41, @on
		exec sp_trace_setevent @TraceID, 33, 49, @on
		exec sp_trace_setevent @TraceID, 33, 6, @on
		exec sp_trace_setevent @TraceID, 33, 10, @on
		exec sp_trace_setevent @TraceID, 33, 14, @on
		exec sp_trace_setevent @TraceID, 33, 26, @on
		exec sp_trace_setevent @TraceID, 33, 30, @on
		exec sp_trace_setevent @TraceID, 33, 50, @on
		exec sp_trace_setevent @TraceID, 33, 3, @on
		exec sp_trace_setevent @TraceID, 33, 11, @on
		exec sp_trace_setevent @TraceID, 33, 35, @on
		exec sp_trace_setevent @TraceID, 33, 51, @on
		exec sp_trace_setevent @TraceID, 33, 4, @on
		exec sp_trace_setevent @TraceID, 33, 12, @on
		exec sp_trace_setevent @TraceID, 33, 20, @on
		exec sp_trace_setevent @TraceID, 33, 60, @on
		exec sp_trace_setevent @TraceID, 162, 7, @on
		exec sp_trace_setevent @TraceID, 162, 31, @on
		exec sp_trace_setevent @TraceID, 162, 8, @on
		exec sp_trace_setevent @TraceID, 162, 64, @on
		exec sp_trace_setevent @TraceID, 162, 1, @on
		exec sp_trace_setevent @TraceID, 162, 9, @on
		exec sp_trace_setevent @TraceID, 162, 41, @on
		exec sp_trace_setevent @TraceID, 162, 49, @on
		exec sp_trace_setevent @TraceID, 162, 6, @on
		exec sp_trace_setevent @TraceID, 162, 10, @on
		exec sp_trace_setevent @TraceID, 162, 14, @on
		exec sp_trace_setevent @TraceID, 162, 26, @on
		exec sp_trace_setevent @TraceID, 162, 30, @on
		exec sp_trace_setevent @TraceID, 162, 50, @on
		exec sp_trace_setevent @TraceID, 162, 3, @on
		exec sp_trace_setevent @TraceID, 162, 11, @on
		exec sp_trace_setevent @TraceID, 162, 35, @on
		exec sp_trace_setevent @TraceID, 162, 51, @on
		exec sp_trace_setevent @TraceID, 162, 4, @on
		exec sp_trace_setevent @TraceID, 162, 12, @on
		exec sp_trace_setevent @TraceID, 162, 20, @on
		exec sp_trace_setevent @TraceID, 162, 60, @on
		exec sp_trace_setevent @TraceID, 14, 1, @on
		exec sp_trace_setevent @TraceID, 14, 9, @on
		exec sp_trace_setevent @TraceID, 14, 6, @on
		exec sp_trace_setevent @TraceID, 14, 10, @on
		exec sp_trace_setevent @TraceID, 14, 14, @on
		exec sp_trace_setevent @TraceID, 14, 11, @on
		exec sp_trace_setevent @TraceID, 14, 12, @on
		exec sp_trace_setevent @TraceID, 20, 7, @on
		exec sp_trace_setevent @TraceID, 20, 23, @on
		exec sp_trace_setevent @TraceID, 20, 31, @on
		exec sp_trace_setevent @TraceID, 20, 8, @on
		exec sp_trace_setevent @TraceID, 20, 12, @on
		exec sp_trace_setevent @TraceID, 20, 60, @on
		exec sp_trace_setevent @TraceID, 20, 64, @on
		exec sp_trace_setevent @TraceID, 20, 1, @on
		exec sp_trace_setevent @TraceID, 20, 9, @on
		exec sp_trace_setevent @TraceID, 20, 21, @on
		exec sp_trace_setevent @TraceID, 20, 49, @on
		exec sp_trace_setevent @TraceID, 20, 6, @on
		exec sp_trace_setevent @TraceID, 20, 10, @on
		exec sp_trace_setevent @TraceID, 20, 14, @on
		exec sp_trace_setevent @TraceID, 20, 26, @on
		exec sp_trace_setevent @TraceID, 20, 3, @on
		exec sp_trace_setevent @TraceID, 20, 11, @on
		exec sp_trace_setevent @TraceID, 20, 35, @on
		exec sp_trace_setevent @TraceID, 20, 51, @on
		exec sp_trace_setevent @TraceID, 15, 15, @on
		exec sp_trace_setevent @TraceID, 15, 16, @on
		exec sp_trace_setevent @TraceID, 15, 9, @on
		exec sp_trace_setevent @TraceID, 15, 13, @on
		exec sp_trace_setevent @TraceID, 15, 17, @on
		exec sp_trace_setevent @TraceID, 15, 6, @on
		exec sp_trace_setevent @TraceID, 15, 10, @on
		exec sp_trace_setevent @TraceID, 15, 14, @on
		exec sp_trace_setevent @TraceID, 15, 18, @on
		exec sp_trace_setevent @TraceID, 15, 11, @on
		exec sp_trace_setevent @TraceID, 15, 12, @on
		exec sp_trace_setevent @TraceID, 17, 12, @on
		exec sp_trace_setevent @TraceID, 17, 1, @on
		exec sp_trace_setevent @TraceID, 17, 9, @on
		exec sp_trace_setevent @TraceID, 17, 6, @on
		exec sp_trace_setevent @TraceID, 17, 10, @on
		exec sp_trace_setevent @TraceID, 17, 14, @on
		exec sp_trace_setevent @TraceID, 17, 11, @on
		exec sp_trace_setevent @TraceID, 10, 15, @on
		exec sp_trace_setevent @TraceID, 10, 16, @on
		exec sp_trace_setevent @TraceID, 10, 9, @on
		exec sp_trace_setevent @TraceID, 10, 17, @on
		exec sp_trace_setevent @TraceID, 10, 2, @on
		exec sp_trace_setevent @TraceID, 10, 10, @on
		exec sp_trace_setevent @TraceID, 10, 18, @on
		exec sp_trace_setevent @TraceID, 10, 11, @on
		exec sp_trace_setevent @TraceID, 10, 12, @on
		exec sp_trace_setevent @TraceID, 10, 13, @on
		exec sp_trace_setevent @TraceID, 10, 6, @on
		exec sp_trace_setevent @TraceID, 10, 14, @on
		exec sp_trace_setevent @TraceID, 12, 15, @on
		exec sp_trace_setevent @TraceID, 12, 16, @on
		exec sp_trace_setevent @TraceID, 12, 1, @on
		exec sp_trace_setevent @TraceID, 12, 9, @on
		exec sp_trace_setevent @TraceID, 12, 17, @on
		exec sp_trace_setevent @TraceID, 12, 6, @on
		exec sp_trace_setevent @TraceID, 12, 10, @on
		exec sp_trace_setevent @TraceID, 12, 14, @on
		exec sp_trace_setevent @TraceID, 12, 18, @on
		exec sp_trace_setevent @TraceID, 12, 11, @on
		exec sp_trace_setevent @TraceID, 12, 12, @on
		exec sp_trace_setevent @TraceID, 12, 13, @on
		exec sp_trace_setevent @TraceID, 13, 12, @on
		exec sp_trace_setevent @TraceID, 13, 1, @on
		exec sp_trace_setevent @TraceID, 13, 9, @on
		exec sp_trace_setevent @TraceID, 13, 6, @on
		exec sp_trace_setevent @TraceID, 13, 10, @on
		exec sp_trace_setevent @TraceID, 13, 14, @on
		exec sp_trace_setevent @TraceID, 13, 11, @on

		/*
			If we are filtering on Login, then set filter appropriately
		*/
		if (@bFilterLogin = 1)
		begin

			set @traceActionRequested = 'sp_trace_setfilter'

			exec master.dbo.xp_sprintf
				   @logTraceFilterOnLogin output
				 , @logSyntaxTraceFilterOnLogin
				 , @strTraceID
				 , @loginname

			print @logTraceFilterOnLogin

			exec @rc = sp_trace_setfilter 
					  @traceid = @TraceID
					, @columnid = 11
					, @logical_operator = 1 -- 1 is OR
					, @comparison_operator = 6 -- 6 is LIKE
					, @value = @loginname

		end

		set @traceActionRequested = 'sp_trace_setstatus'

		exec master.dbo.xp_sprintf
			   @logTraceStart output
			 , @logSyntaxTraceStart
			 , @tracefolder

		print @logTraceStart

		-- Set the trace status to start
		exec @rc = sp_trace_setstatus 
				  @TraceID = @TraceID
				, @status = @TRACE_STATUS_REQUEST_START

		-- display trace id for future references
		select 
				TraceID=@TraceID

	end

	goto finish

	error: 

		set @traceRC = @rc

		select 
			   [ErrorCode] =@rc

			, [TraceActionRequested]
				= @traceActionRequested

	finish: 

end
		
go

Sample Invocation


	declare @tracefolder sysname
	declare @loginname sysname
	declare @terminateRunningTraces	bit
	declare @createTrace bit

	declare @traceID int
	declare @traceRC int

	set @tracefolder = 'D:\Microsoft\SQLServer\Profiler';
	set @loginname = 'emmdatafeed'
	set @terminateRunningTraces =0
	set @createTrace = 1

	exec dbo.sp_ServerSideTraceInitiate
		  @tracefolder = @tracefolder
		, @loginname = @loginname
		, @terminateRunningTraces = @terminateRunningTraces
		, @createTrace = @createTrace

		, @traceID = @traceID output
		, @traceRC = @traceRC output


	select
		  [@traceID] = @traceID
		, [@traceRC] = @traceRC

serverSideTraces

Review Traces

Code


select *
from sys.traces

Output:

ReviewRunningTraces

 

Explanation:

  1. Trace ID = 1
    • Default Trace ( Blackbox)
  2. Other Traces
    • Path
    • File Position
      • In our case, we read 34078720
        • To get the actual file we use a calculator and divide by 1024
          FileSize
    • Start and Last Event Time

 

Clean up

Code

Here is a quick clean up code.

We are targeting a specific Trace.



Set nocount ON
Set XACT_ABORT ON

DECLARE @TraceID int

DECLARE @statusStop int
DECLARE @statusCloseAndDiscardDefinition int

--Replace with Trace ID
--Select * from sys.traces where is_default != 1
SET @TraceID = 2

SET @statusStop = 1
SET @statusCloseAndDiscardDefinition = 2

if exists
	(
		select *
		from   sys.traces
		where  [id] = @traceID
	)
begin

	begin tran
		
		EXEC sp_trace_setstatus 
				   @traceid = @TraceID
				,  @status = @statusStop

		EXEC sp_trace_setstatus 
				  @traceid = @TraceID
				,  @status = @statusCloseAndDiscardDefinition

	commit tran

end

Microsoft – SQL Server – Query Comparison Using SQL Profiler

Prelude

In a previous post we touched on the fact that we can use Quest Software’s Benchmark Factory and SQL Sentry’s Plan Explorer ( Link ) to compare competing queries and query plans.

 

Background

Once again to get a generalized database, we downloaded SQL Server 2014 AdventureWorks from here and restored it.

 

SQL Objects

 

Once our AdventureWorks database is available, we created a  couple of Stored Procedures that we can use to retrieve data from it.

 

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_1

 

 


use [AdventureWorks2014]
go

if object_id('dbo.usp_PersonFetchFilteredOnName_1') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_1 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_1
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin

    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]


    from   [Person].[Person] tblPerson

    where
                (
                       (@Firstname is null)
                    or (@Firstname = '')
                    or (Firstname = @Firstname)
                )
            and
                (
                       (@Lastname is null)
                    or (@Lastname = '')
                    or (Lastname = @Lastname)
                )


end
go

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2

 

Here is the second Stored Procedure – dbo.usp_PersonFetchFilteredOnName_2


if object_id('dbo.usp_PersonFetchFilteredOnName_2') is null
begin
 
    exec('create procedure dbo.usp_PersonFetchFilteredOnName_2 as select 1/0 as [shell]')
end
go
 
alter procedure dbo.usp_PersonFetchFilteredOnName_2
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin
 
 
    set nocount on;
 
    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]
 
    from   [Person].[Person]
    where
            (
 
                    (
                        Firstname = 
                                    case
                                        when (@Firstname is null) then Firstname
                                        when (@Firstname = '') then Firstname
                                        else @Firstname
                                    end
                    )
 
                and
 
                    (
                        Lastname = 
                                    case
                                        when (@Lastname is null) then Lastname
                                        when (@Lastname = '') then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

Stored Procedure – dbo.usp_PersonFetchFilteredOnName_3

 

Here is the third Stored Procedure ( dbo.usp_PersonFetchFilteredOnName_3 )

 

 


if object_id('dbo.usp_PersonFetchFilteredOnName_3') is null
begin

    exec('create procedure dbo.usp_PersonFetchFilteredOnName_3 as select 1/0 as [shell]')
end
go

alter procedure dbo.usp_PersonFetchFilteredOnName_3
(
      @Firstname nvarchar(50) = null
    , @Lastname  nvarchar(50) = null
)
as
begin


    set nocount on;

    SELECT
               [BusinessEntityID]
              ,[PersonType]
              ,[NameStyle]
              ,[Title]
              ,[FirstName]
              ,[MiddleName]
              ,[LastName]
              ,[Suffix]
              ,[EmailPromotion]
             -- ,[AdditionalContactInfo]
             --,[Demographics]
             -- ,[rowguid]
              ,[ModifiedDate]

    from   [Person].[Person]

    where
            (

                    (
                        Firstname = 
                                    case @Firstname
                                        when null then Firstname
                                        when '' then Firstname
                                        else @Firstname
                                    end
                    )

                and

                    (
                        Lastname = 
                                    case @LastName
                                        when null then Lastname
                                        when '' then Lastname
                                        else @Lastname
                                    end
                    )
            )
end
go

 

SQL Management Studio

 

Here is the Query for invoking the Stored Procedures; along with the “Statistics IO” & “Statistics Time” measured.

 

Invoke SP

 

Here is the queries for invoking the SP.

 

 



exec dbo.usp_PersonFetchFilteredOnName_1
        @Firstname = 'Laura'
      , @Lastname  = 'Norman'


exec dbo.usp_PersonFetchFilteredOnName_2
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

    
exec dbo.usp_PersonFetchFilteredOnName_3
      @Firstname = 'Laura'
    , @Lastname  = 'Norman'

 

Statistics IO

 

StatisticsIO

 

 

Statistics Time

 

SQLServerTime

 

 

 

 

 

 

SQL Profiler

 

Here are the events we focused on using SQL Profiler.

 

Events Selection

 

Events Selection: Image

EventsSelection

 

Events Selection: Tabulate

 

Item Events & Columns  Item
Events & Columns
Stored Procedures
RPC:Completed
RPC:Starting
SP:Recompile
SP:Starting
 SP:StmtCompleted
Columns
TextData
CPU
Reads
Writes
SPID

 

 

 

 

 

Output

 

Output: Image

 

Here is the SQL Profiler Image.

SQLProfiler

 

Output:Tabulate

 

Metric SP-1 SP-2 SP-3
CPU 0 16  15
Reads 124 136  136
Writes 0 0  0
Duration 217 255  288

 

 

From the Screen-shot above, we can see that the Stored Procedures that use the “Case Clause” are a weeny bit more expensive.

 

Summary

 

Found that SQL Server Profiler when properly configured offers a slightly more concise view of a query’s performance data compared to SQL Server “Set statistics time on”.

 

 

SQLServer – Error Message – Login Failed Error: 18456, Severity: 14 State: 38

Client Software logged a couple of SQL Server related error messages this morning.

The error message stated “Login Failed Error: 18456, Severity: 14 State: 38“.

 

Googled a bit for it, but most of the hits were a generic.

They pointed to Database Accessibility permission issues.

There were helpful entries that suggested that we should initiate MS SQL Server Profiler

and track for “Security – Audit Login Failed”.

Did so, but nothing useful came up.

Sometimes it is hard to work for a Living.

Later on it all made sense, the MS SQL Server was recently restarted and the failures were due to the fact that the necessary “Database Recovery“that follows each SQL Server

Restart.

Was able to arrive at this from reviewing SQL Server’s Error Logs.

Obviously, it will be helpful if MS logged the Database that was inaccessible.