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 – Linked Server – TroubleShooting

Background

There are a variety of avenues for diagnosing Linked Server related connectivity issues.

 

Follow Up

As a quick follow up to earlier conversations on SQL Server and heterogeneous data sources:

  1. SQL Server / Linked Server – Oracle ODAC
    Link

 

Outline

Here are some of the tools for diagnosis heterogenous data related issues:

  1. Query
    • DBCC
      • Trace Flag 7300
  2. SQL Server Profiler
  3. Event Viewer
  4. SysInternals
    • Process Explorer

 

 

Query

DBCC

Trace Flag 7300

Query
Syntax

dbcc traceon(7300) 
	with no_infomsgs
	;
go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample

dbcc traceon(7300) 
	with no_infomsgs
	;
go

declare @linkedServer sysname

set @linkedServer = 'HRDB';

exec sp_tables_ex
		@table_server = @linkedServer

go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample – Grid
Sample – Textual

OLE DB provider "OraOLEDB.Oracle" for linked server "PRD" returned message "ORA-12170: TNS:Connect timeout occurred".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 4]
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PRD".


Explanation
  1. Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message
      • “ORA-12170: TNS:Connect timeout occurred”.
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  2. Using DBCC TraceOn (7300)
    • Allows us to see that we are experiencing connection timeout to the Oracle Server
    • Likely means network or TNS configuation error

 

 

SQL Server Profiler

Trace Properties

Trace Event Selections

Here are the events that we will be tracing on…

Event Category Event Event Description
Errors and Warnings
ErrorLog  Indicates error events logged in the SQL Server error log.
EventLog  Indicates events logged in the Windows application event log.
Exception  Indicates that an exception has occurred in SQL Server.
Execution Warnings  Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
User Error Message  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
OLEDB
OLEDB Call Event Includes event classes produced by the execution of stored procedures.
OLEDB Data Read Event Displays OLE DB IRowset::GetData calls made by SQL Server for fetching row data for distributed queries and remote stored procedures.
OLEDB Errors Indicates that an OLE DB error has occurred.
OLEDB Provider Information Occurs when a distributed query is run and collects information corresponding to the provider connection. This event class contains all the properties collected from the remote provider using various property sets such as DBPROPSET_DATASOURCEINFO, SQLPROPSET_OPTHINTS, DBPROPSET_SQLSERVERDATASOURCEINFO (SQL Server only), DBPROPSET_SQLSERVERDBINIT (SQL Server only) and DBPROPSET_ROWSET and interface IDBInfo.
OLEDB Query Interface Event Displays OLE DB IUnknown::QueryInterface calls made by SQL Server for distributed queries and remote stored procedures.
Security Audit
Audit Login Collects all new connection events since the trace was started, such as when a client requests a connection to a server running an instance of SQL Server.
Audit Logout Collects all new disconnect events since the trace was started, such as when a client issues a disconnect command.
Stored Procedures
RPC:Completed Occurs when a remote procedure call has been completed.
TSQL
SQL:BatchCompleted Occurs when the Transact-SQL statement has completed.
SQL:BatchStarting Occurs when a Transact-SQL batch is starting.

 

 

Traces

Sample Traces

Sample Trace #01
Image – Full Conversation

Image – OLEDB Call Event
Explanation
  1. SQL:BatchStarting
    • Issues sp_tables_ex
    • Query Linked Server asking for a listing of tables
  2. OLEDB QueryInterface Event
    • IID_IDBProperties
      • <ppunk>0x0000003C47F941D8</ppunk>
        • Set ppunk to 0x0000003C47F941D8
  3. OLEDB Call Event
    • Property
      • DBPROP_INIT_TIMEOUT
      • DBPROP_INIT_GENERALTIMEOUT
      • DBPROP_INIT_DATASOURCE
      • DBPROP_AUTH_USERID
      • DBPROP_AUTH_PASSWORD
    • Result
      • hresult
        • 265946
          • ???
  4. OLEDB QueryInterface Event
    • IID_IDBInitialize
      • input
        • IID_IDBInitialize
      • hresult
        • 0
      • ppunk
        • <ppunk>0x0000003C47F941E0</ppunk>
          • returns 0x0000003C47F941E0
    • IID_ISSAsynchStatus
      • input
        • IID_ISSAsynchStatus
      • hresult
        • -2147467262
  5. OLEDB Errors
    • hresult
      • -2147467259
  6. OLEDB Query Interface Event
    • input
      • IID_ISupportErrorInfo
    • hresult
      • 0
    • outputs
      • 0x00000044686EBFC0
  7. OLEDB Call Event
    • input
      • IID_IDBInitialize
    • hresult
      • 0
  8. User Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message “ORA-12170: TNS:Connect timeout occurred“.
  9. Exception
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  10. User Error Message
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.

 

Event Viewer

Security

Filter

Tabulate

Courtesy of SANS.org:

SANS Institute
InfoSec Reading Room
Windows Logon Forensics
Link

Event  ID = Windows XP / Windows 2000 Windows 2008/2012/2014, etc Event Description
 528 4624 Successful logon: A user successfully logged on to a computer. For
information about the type of logon, see the next section
 529 4625  Logon failure. A logon attempt was made with an unknown user name
or a known user name with a bad password. For Windows 2008 and
above, event ID 4625 logs every failed logon attempt with failure status
code regardless of logon type or type of account
530 4625 Logon failure for a logon attempt to log on outside of the allowed time
 531 4625  Logon failure for a logon attempt using a disabled account.
 532 4625  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
 533  4625 Logon failure. A logon attempt was made by a user who is not allowed
to log on at this computer.
 534 4625 Logon failure. The user attempted to log on with a type that is not allowed.
 535 4625 Logon failure. The password for the specified account has expired.
 536 4625 Logon failure. The Net Logon service is not active
 537 4634 Logon failure. The logon attempt failed for other reasons. In some
cases, the reason for the logon failure may not be known.
 538 4634 The logoff process was completed for a user.
 538/551 4647 A user initiated the logoff process. It is logged for Interactive and
RemoteInteractive logons in place of logoff event 538/4634.
 539 4625 Logon failure. The account was locked out at the logon
 540 4624 Successful network logon: A user successfully logged on over a
network.
 552 4648 A user successfully logged on to a computer using explicit credentials while already logged on as a different user
 638 4778 A user has reconnected to a disconnected terminal session.
 683 4625 A user disconnected a terminal session without logging off.

 

Image

 

Sample
Sample 01
Image – Top

Image – Bottom
Explanation
  1. Security ID
    • NULL SID
  2. Logon Type
    • Logon Type 3 is Network
  3. Impersonation Level
    • Impersonation
  4. New Logon
    • Security ID
      • LABdadeniji
    • Account Name
      • dadeniji
    • Account Domain
      • LAB
  5. Network Information
    • Workstation Name
      • DADENIJI
    • Source Network Address
      • w.x.y.z
    • Source Network Port
      • #####
  6. Detailed Authentication
    • Logon Type
      • NtLmSsp
    • Authentication Package
      • NTLM
    • Package Name (NTLM only)
      • NTLM V2
    • Key Length
      • 128

SysInternals

Process Explorer

Application Context

OLE-DB Provider can be configured to run in process or out of process.

If run in process we need to track the sql server service.

When ran out of process, we need to track via dllhost.exe

Sample

Sample – 01
Image

Explanation
  1. dllhost.exe
    • COM Class
      • MSDAINITIALIZE Class
      • c:program filescommon filessystem32ole dboledb32.dll
      • OLEDB Core Services

tasklist

Preface

The OLE-DB Provider can either be configured to run as in-process or out-of process.

When in process it runs within the sqlservr.exe address space.

When out of process, it runs within a surrogate process, dllhost.exe.

out of Process – dllhost.exe

syntax

tasklist /m /fi "Imagename eq dllhost.exe"

Output
Output # 01

Explanation
  1. We can see that Oracle dlls are loaded within the dllhost.exe

 

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        • Snehadeep
          • Troubleshooting “Cannot create an instance of OLE DB provider”
            Link
    • CSS SQL Server Engineers
      • pssql
        • How to get up and running with Oracle and Linked Servers
          Link
  2. Gianluca Sartori
    • Setting up linked servers with an out-of-process OLEDB provider.
      Link
  3. Sans Institute
    • Windows Login Forensics
      Link

SQL Server – RML Utility – Converting Extended Events to SQL Server Profiler Trace Files

Background

Extended Events is the modern way to collect events.  But, in some cases one might want to convert Extended Events to SQL Server Profiler Traces.

RML Utility

Reading through a blog post found out one can convert files generated through Extended Events into Profiler Trace Files.

Download Site

The latest version of RML Utility are available here.

Curiosity

The saying goes curiosity killed the cat.

Same here this last Monday in January.

 

Installation

Download and Installed RML Utilities.

Review Installation

Targeted Folder

The targeted folder is C:\Program Files\Microsoft Corporation\RMLUtils

ReadTrace

File Version

Image

Explanation

The file version is 9.4.51.0

 

Processing

Code


set "_folderReadTrace=C:\Program Files\Microsoft Corporation\RMLUtils\"

set "_xelFolder=Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\replay"

set "_xelFileFirst=replay_0_131617352214050000.xel"

set "_xelFileTarget=%_xelFolder%\%_xelFileFirst%"

set "_tracefile=Z:\Microsoft\SQLServer\ProfileTrace\TraceFiles"

set "_sqlServer=DBLAB"

if not exist %_tracefile% mkdir %_tracefile%

"%_folderReadTrace%\readtrace.exe"  -S%_sqlServer% -a -I"%_xelFileTarget%" -MS -o"%_tracefile%"

 

Output

Image

Textual


01/29/18 16:29:39.892 [0X000015D8] I/O Completion manager started
01/29/18 16:29:39.893 [0X00002BE4] Attempting DOD5015 removal of [Z:\Microsoft\S
QLServer\ProfileTrace\TraceFiles\ReadTrace.log]
01/29/18 16:29:39.896 [0X00002BE4] Readtrace a SQL Server trace processing utili
ty.
Version 9.04.0051 built for x64.
Copyright ⌐ 1997-2014 Microsoft. All Rights Reserved
01/29/18 16:29:39.897 [0X00002BE4]             Computer: D-ITS-AYSOSQL02
01/29/18 16:29:39.897 [0X00002BE4]          Base Module: C:\Program Files\Micros
oft Corporation\RMLUtils\ReadTrace.exe
01/29/18 16:29:39.897 [0X00002BE4]           Process Id: 6480
01/29/18 16:29:39.898 [0X00002BE4]  Active proc mask(0): 0x0000000F
01/29/18 16:29:39.898 [0X00002BE4]         Architecture: 9
01/29/18 16:29:39.898 [0X00002BE4]            Page size: 4096
01/29/18 16:29:39.898 [0X00002BE4]                 CPUs: 4
01/29/18 16:29:39.899 [0X00002BE4]     Processor groups: 1
01/29/18 16:29:39.899 [0X00002BE4]         Highest node: 0
01/29/18 16:29:39.899 [0X00002BE4]   Proximity: 00  Node: 00
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.900 [0X00002BE4]                Group: 0
01/29/18 16:29:39.900 [0X00002BE4] ---------------------------------------
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000001 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Package mask: 0x00000003
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000002 Function uni
ts: Separated
01/29/18 16:29:39.901 [0X00002BE4]         Processor(s): 0x00000004 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Package mask: 0x0000000C
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x00000008 Function uni
ts: Separated
01/29/18 16:29:39.902 [0X00002BE4]         Processor(s): 0x0000000F assigned to
Numa node: 0
01/29/18 16:29:39.907 [0X00002BE4] Current time bias: 480 minutes 8.00 hours DST
 Standard
01/29/18 16:29:39.907 [0X00002BE4] -SDBLAB
01/29/18 16:29:39.908 [0X00002BE4] -a
01/29/18 16:29:39.908 [0X00002BE4] -IZ:\Microsoft\SQLServer\ExtendedEvents\Trace
Files\replay\replay_0_131617352214050000.xel
01/29/18 16:29:39.908 [0X00002BE4] File mirroring enabled, with single file targ
et
01/29/18 16:29:39.909 [0X00002BE4] -MS
01/29/18 16:29:39.909 [0X00002BE4] -oZ:\Microsoft\SQLServer\ProfileTrace\TraceFi
les
01/29/18 16:29:39.910 [0X00002BE4] Using language id (LCID): 1024 [English_Unite
d States.1252] for character formatting with NLS: 0x0006020E and Defined: 0x0006
020E
01/29/18 16:29:39.911 [0X00002BE4] Attempting to cleanup existing RML files from
 previous execution
01/29/18 16:29:39.991 [0X00002BE4] Detecting list of matching XEL files in the s
ame directory
01/29/18 16:29:40.039 [0X00002BE4]  INFO: Using additional file system cache rea
d ahead for XEL input
01/29/18 16:29:40.046 [0X00002BE4] XEL READER ERROR: The tookLock argument must
be set to false before calling this method.
01/29/18 16:29:40.048 [0X00002BE4] XEL READER ERROR: Attempt to open file Z:\Mic
rosoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XE
L failed, see previous error(s) for more details.
01/29/18 16:29:40.049 [0X00002BE4] ERROR: Attempt to open Z:\Microsoft\SQLServer
\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
01/29/18 16:29:40.050 [0X00002BE4] ERROR: Unable to construct proper base metada
ta generation for Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay
_0_131617352214050000.XEL
01/29/18 16:29:40.053 [0X00002BE4] ERROR: Attempt to determine list of .XEL file
s failed.
01/29/18 16:29:40.057 [0X00002BE4] *** ERROR: Attempt to initialize trace file r
eader failed with operating system error 0x80070006 (The handle is invalid)
01/29/18 16:29:40.061 [0X00002BE4] Reads completed - Global Error Status 0xfffff
ffe
01/29/18 16:29:40.062 [0X00002BE4] Signaling worker threads to complete final ac
tions.
01/29/18 16:29:40.067 [0X00002BE4] Waiting for the worker threads to complete fi
nal actions.
01/29/18 16:29:40.069 [0X00002BE4] Performing general cleanup actions.
01/29/18 16:29:40.072 [0X00002BE4] Performing final mirroring actions.
01/29/18 16:29:40.075 [0X00002BE4] Total Events Processed: 0
01/29/18 16:29:40.076 [0X00002BE4]  Total Events Filtered: 0
01/29/18 16:29:40.077 [0X00002BE4] =============================================
=
01/29/18 16:29:40.079 [0X00002BE4] WARNING: A significant portion of the events
where filtered.
01/29/18 16:29:40.086 [0X00002BE4]          You may want to check the filtering
criteria.
01/29/18 16:29:40.088 [0X00002BE4] =============================================
=
01/29/18 16:29:40.090 [0X00002BE4] *********************************************
**********************************
* ReadTrace encountered one or more ERRORS. An error condition typically      *
* stops processing early and the ReadTrace output may be unusable.            *
* Review the log file for details.                                            *
*******************************************************************************
01/29/18 16:29:40.094 [0X00002BE4] ***** ReadTrace exit code: -2
01/29/18 16:29:40.097 [0X00002BE4]  INFO: Max set-aside XEL event hash table ent
ry count: 0
01/29/18 16:29:40.131 [0X00002BE4]
01/29/18 16:29:40.134 [0X00002BE4] INFO: Cleaning up connection info hash table
>

Explanation

Key details:

  1. [0X00002138] Detecting list of matching XEL files in the same directory
  2. [0X00002138] INFO: Using additional file system cache read ahead for XEL input
  3. [0X00002138] XEL READER ERROR: The tookLock argument must be set to false before calling this method.
  4. [0X00002138] XEL READER ERROR: Attempt to open file Z:\Microsoft\SQLServer\ExtendedEvents\TraceFies\Replay\replay_0_131617352214050000.XEL failed, see previous error(s) for more details.
  5. ERROR: Attempt to open Z:\Microsoft\SQLServer\ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL failed.
    [0X00002138] ERROR: Unable to construct proper base metadata generation for Z:\Microsoft\SQLServe
    \ExtendedEvents\TraceFiles\Replay\replay_0_131617352214050000.XEL
  6. [0X00002138] ERROR: Attempt to determine list of .XEL files failed.
  7. [0X00002138] *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x80070006 (The handle is invalid)
  8. [0X00002138] Reads completed – Global Error Status 0xfffffffe
  9. [0X00002138] ***** ReadTrace exit code: -2

 

TroubleShooting

ReadTrace

Code


"C:\Program Files\Microsoft Corporation\RMLUtils\readtrace.exe" /?

Output

Explanation

  1. -I File name of the first .TRC or .XEL file to process [REQUIRED]. Note: XEL processing is ‘BETA’ and limited.
    • Clearly states that XEL ( Extended Events ) processing is in BETA mode and limited

SysInternals

Process Monitor

Captured Events
Image

Explanation

Confirmed that the extended event file was located and opened successfully.

 

Summary

It appears that readtrace at Version 9.4.51.0, last modified on Dec 10,2014 is starting to show its age.

BTW, in SQL Server parlance 9.x.y.z is MS SQL Server v2005.

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.

 

 

Plan Guide & Plan Cache – FAQ – 1

Background

Here are answers gleaned from the web to questions and doubts that I had in my mind as I started to make sure that our Plan Guides are in fact being used.

 

Plan Cache

Monitor plan cache size and data cache size

https://msdn.microsoft.com/en-us/library/cc293620.aspx

  1. In general, as more queries are run, the amount of memory used for data page caching should increase along with the amount of memory used for plan cache.
  2. Version
    • In SQL Server 2005 prior to Service Pack 1, the maximum limit for plan cache could grow to be up to 80 percent of the total buffer pool before memory pressure would start forcing plans to be evicted. This can result in severe performance degradation for those queries that depend on good data caching behavior.
    • For any amount of memory greater than 4 GB, Service Pack 2 changes the size limit that plan cache can grow to before memory pressure is indicated.
  3. One of the easiest places to get a comparison of the pages used for plan cache and the pages used for data cache is the performance counters. Take a look at the following counters: SQL Server: Plan Cache\Cache Pages(_Total) and SQLServer: BufferManager\Database pages.

Code



;with cteTotal
(
	[sum]
)
as
(
	SELECT 
			sum( cntr_value)

	FROM sys.dm_os_performance_counters

	WHERE  
		(
			counter_name IN ( 'Cache Pages')
	    )

	AND (
			instance_name = '_Total'                                                                                                                          
		)

)

,cteBufferPages
(
	[value]
)
as
(
	SELECT 
			[cntr_value]

	FROM sys.dm_os_performance_counters

	--BufferManager\Database pages
	WHERE  
		(
			     [object_name] = 'SQLServer:Buffer Manager'
			 and [counter_name] = 'Database pages'                                                                                                                  
	    )


)

SELECT 
		  tblSOSPC.instance_name

		, tblSOSPC.counter_name

		, [numberofPages]
			= tblSOSPC.cntr_value

		, [sizeInMB]
			= tblSOSPC.cntr_value * 8192
				/ ( 1024 * 1024)

		, [sizeInGB]
			= cast
				(
					(
						tblSOSPC.cntr_value * 8192.00
							/ ( 1024 * 1024 * 1000)
					)
					as decimal(10,2)
				)


		, [%OfCache]
			= cast
				(
					(tblSOSPC.cntr_value * 100.00 )
						/cteT.[sum]
					as decimal(10, 2)
				)

		, [%OfTotal]
			= cast
				(
					(tblSOSPC.cntr_value * 100.00 )
						/cteBP.[value]
					as decimal(10, 2)
				)

FROM sys.dm_os_performance_counters tblSOSPC

cross apply cteTotal cteT

cross apply cteBufferPages cteBP

WHERE  
		(
			( 
				tblSOSPC.[counter_name] IN ( 'Cache Pages')
			)
	    )

AND	    (
			tblSOSPC.[instance_name] != '_Total'                                                                                                                          
		)




Output

cachedPages

Explanation

  1. Percentile
    • SQL Plans
      • 90% of Cache Total
      • 24% of Overall Memory Total
    • Objects Plans ( Stored Procedures, Triggers, Views, and Triggers )
      • 7.5% of Overall Total
      • 1.2 % of Total
  2. Total
    • SQL Plans is 3.5 GB

Plan Cache

I have seen blog posts that suggest that upon creating a plan cache, one should clear the cache, and observe & track usage of the plan guide.

But, this does not seem to indicate Microsoft’s full intent.

Here is what the doc states:

Plan Guide Effect on the Plan Cache
https://msdn.microsoft.com/en-us/library/ms190417.aspx

  1. Creating a plan guide on a module removes the query plan for that module from the plan cache.
  2. Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value.
  3. Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

It appears that MSFT in fact goes the extra mile to indicate that as part of its codebase, a plan is created, and plan cache is walked through, and corresponding entries are pruned.

Settings

Level – SQL Instance

Optimize for Adhoc Queries?

Here is a worthy code originally written by Brett Hawton on the Idera’s web site ( Link )

James’ SQL Footprint ( Link )

Code



/*
	Brett Hawton has a query which can help you determine if you need to use 'optimize for ad hoc workloads'
*/
SET NOCOUNT ON;

SELECT 
		[Cache Store Type]
			= objtype

        , [Total Num Of Plans]
			= COUNT_BIG(*)

        , [Total Size In MB]
			= cast
				(
					SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576
						as decimal(14, 2)
				)

        , [All Plans - Ave Use Count]
			= AVG(usecounts)

        , [Size in MB of plans with a Use count = 1]
			= cast
				(
					SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/ 1048576
						as decimal(14, 2)
				)

        , [Number of of plans with a Use count = 1]
			= SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Size in MB of plans with a Use count = 1] DESC

DECLARE @AdHocSizeInMB decimal (14,2)
DECLARE @TotalSizeInMB decimal (14,2)

DECLARE @switchOn	   varchar(255)
DECLARE @switchOff	   varchar(255)

Declare @AdHocSizeInMBHighWatermark int
Declare @AdHocPercentileHighWatermark int
declare @AdHocPercentile float

set @AdHocSizeInMBHighWatermark = 200
set @AdHocPercentileHighWatermark = 25

set @switchOn = 'Switch on Optimize for ad hoc workloads as it will make a significant difference'
set @switchOff = 'Setting Optimize for ad hoc workloads will make little difference'

SELECT 
	  @AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes ELSE 0 END) as decimal(14,2))) / 1048576
        , @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576
FROM sys.dm_exec_cached_plans 

if (@TotalSizeInMB > 0) 
begin

	set @AdHocPercentile = cast(
									(@AdHocSizeInMB * 100 )
										/ @TotalSizeInMB
									as decimal(10, 2)
								)

end

SELECT 
		[Current memory occupied by adhoc plans only used once (MB)]
			= @AdHocSizeInMB

        , [Total cache plan size (MB)]
			= @TotalSizeInMB

		/*
			, [% of total cache plan occupied by adhoc plans only used once]
				= CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))
		*/

		, [% Adhoc]
			= @AdHocPercentile

/*
	200MB or > 25%
*/
IF  (
		   ( 
				@AdHocSizeInMB 
					> @AdHocSizeInMBHighWatermark -- 200MB
		   )
		or ( 
				( 
					(@AdHocSizeInMB / @TotalSizeInMB) * 100
				) 
					> @AdHocPercentileHighWatermark  -- 25
			)
	)
begin
		select
			[Recommendation]
				= @switchOn
					
end
ELSE
begin
        select
			[Recommendation]
				= @switchOff
end
GO

 

Output

OptimizeForAdHocQueries-20160520-0500PM

 

Explanation

  1. The code basically gets the size of the total plan cache and also the size used by Adhoc queries
  2. If the size used by Ad-hoc queries is over 200 MB in size or over 25% it recommends to turn on “optimize of ad hoc queries

Cache Type

Compiled Plan Stu / Compiled Plan Stub

  1. Introduced in Microsoft SQL Server 2008
    • Name
      • SQL Server v2008 – RTM
        • Compiled Plan Stu
      • SQL Server v2008 – SP1
        • Compiled Plan Stub
  2. The first time a query is compiled, it is added in the Plan Cache under this title
  3. How does it work?
    • When a query is first executed, the query is placed in the plan cache as a compiled plan stub
      • A small fraction of an an actual plan
    • The second time the query is received, the query is compiled and it is now recorded as a compiled plan

Auto Parameterization

Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization ( 2014-11-27 )
http://www.madeiradata.com/parameterization-part-6-simple-vs-forced/

 

Optimization level can be either “Trivial” or “Full”.

OptimizationLevel-Trivial

So under simple parameterization mode, SQL Server will only parameterize queries with a trivial plan. This is good, because there is no risk of hurting performance as a result of reusing the wrong plan. If the same plan is good for all literal values, then it makes no sense to compile the query again and again for each value. This is what SQL Server does by default.

From Query Plan, Is Query Statement Parameterized?

The easiest way to find out if a query is parameterized is to use graphical XML plan. Just point to the operator and take a look at some of the seek predicate.

Let’s use this update example:

update t1 set c1 = 12, c2 = 23 where c1 = 12

The table t1 has c1 as clustered primary key (the reason why I also update c1 is related to customer’s problem which I will talk about later). So the plan has a clustered index seek predicate on c1.

If the plan is parameterized, you will see the seek predicate on c1 as “Scalar Operator (CONVERT_IMPLICIT(int,..)” or “Scalar Operator (@2)” as shown in figure 1. But if the plan is not parameterized, the hard coded value will show up in the seek predicate like “Scalar Operator (12)” as shown in figure 2 below.

Parameterized

IsQueryStatementParameterized-Parameterized

Hard Coded

IsQueryStatementParameterized-Hardcoded

 

SQL Server Profiler

Andrew Fryer – SQL Server 2008 Plan Guides
https://blogs.technet.microsoft.com/andrew/2008/11/14/sql-server-2008-plan-guides/

There are event classes to see if it’s being used or missed e.g. you might have changed the schema for example and these  are cunningly named as

  • plan guide successful
  • plan guide unsuccessful

Performance-PlanguideSuccessful

 

 

Single/Batch Query

If you try to indicate a plan guide on a multi-statement batch, you will get the error posted below.

 

SQL


set quoted_identifier off
go

declare @querytext		nvarchar(max)
DECLARE @templatetext	nvarchar(max);
DECLARE @parameters		nvarchar(max);


set @querytext = "if exists(select 1 from MailingListEntryRecords where listID = 8 and receiverID='daniel@gmail.com' and receiverCD='ST')
update MailingListEntryRecords set listUID='27a9f40fae',email='daniel@gmail.com',lastupdateDt=getdate()  where listID = 8 and receiverID='daniel@gmail.com' and receiverCD='ST'
else
insert MailingListEntryRecords(listID,receiverID,receiverCD,listUID,email) values(8,'daniel@gmail.com','ST','27a9f40fae','daniel@gmail.com')
"

exec sp_get_query_template 
		  @querytext = @querytext
		, @templatetext = @templatetext output
		, @parameters = @parameters     output

Error


Msg 10523, Level 16, State 3, Procedure sp_get_query_template, Line 345
Cannot generate query template because @querytext does not contain a valid single query.

 

Summary

Unfortunately, Microsoft’s documentation is not always clear as to when a particular functionality was added.

In those cases, we skipped that important information, as well.

References

Guide Plan

  1. Geek City: Clearing a Single Plan from Cache
    http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx


Plan Guide

  1. Plan Guides
    https://msdn.microsoft.com/en-us/library/ms190417.aspx

 

Creation

  1. Creating Plan guide using Query Plan handle
    https://denaliblogs.wordpress.com/category/plan-guides/

 

Guide Plan – SQL Profiler

  1. Andrew Fryer – SQL Server 2008 Plan Guides
    https://blogs.technet.microsoft.com/andrew/2008/11/14/sql-server-2008-plan-guides/

 

Guide Plan – View Properties

  1. View Plan Guide Properties
    https://msdn.microsoft.com/en-us/library/bb895162.aspx

 

Guide Plan – Troubleshooting

  1. Troubleshooting Plan Cache Issues
    https://msdn.microsoft.com/en-us/library/cc293620.aspx

 

Optimize for ad-hoc workloads

  1. SQL Server Quickie #23 – Plan Cache Pollution
    http://www.sqlpassion.at/archive/2015/07/13/how-optimize-for-adhoc-workload-impacts-your-plan-cache/
  2. Masayuki.Ozawa – The cache of the stub by optimize for ad hoc workloads
    http://blog.engineer-memo.com/2011/02/14/optimize-for-ad-hoc-workloads-%E3%81%AB%E3%82%88%E3%82%8B%E3%82%B9%E3%82%BF%E3%83%96%E3%81%AE%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3%83%A5%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6/
  3. James’ SQL Footprint – Ad hoc query optimization in SQL Server
    http://jamessql.blogspot.com/2012/05/ad-hoc-query-optimization-in-sql-server.html
  4. Jose Barreto – SQL Server 2008 Optimize for Ad Hoc Workloads
    https://blogs.technet.microsoft.com/josebda/2009/03/19/sql-server-2008-optimize-for-ad-hoc-workloads/
  5. Database Engine Instances (SQL Server) \ Configure Database Engine Instances \ Server Configuration Options (SQL Server) \ optimize for ad hoc workloads Server Configuration Option
    https://msdn.microsoft.com/en-us/library/cc645587(v=sql.110).aspx
  6. Query plan cache bloated by ad-hoc queries, even with “Optimize for Ad-hoc Workloads”
    http://dba.stackexchange.com/questions/65237/query-plan-cache-bloated-by-ad-hoc-queries-even-with-optimize-for-ad-hoc-workl

 

Parameterization – Simple, Forced

  1. Guy Glantser – Parameterization Part 6: Simple vs. Forced Parameterization
    http://www.madeiradata.com/parameterization-part-6-simple-vs-forced/

 

Parameterization – Simple

  1. Jack Li – How Simple Parameterization works
    https://blogs.msdn.microsoft.com/psssql/2013/12/04/how-simple-parameterization-works/

 

Plan Caching

  1. Caching Mechanisms
    https://msdn.microsoft.com/en-us/library/cc293623.aspx

Catalog Objects

sys.syscachedobjects

  1. sys.syscacheobjects (Transact-SQL)
    https://msdn.microsoft.com/en-us/library/ms187815.aspx
  2. Query Processing – Retrieving Information about Execution Plans – Main_Page – Monitoring & Tuning – Tuning Tools – SQL Server Query Optimizer
    https://www.toadworld.com/platforms/sql-server/w/wiki/9849.query-processing-retrieving-information-about-execution-plans

sys.dm_exec_cached_plans

  1. sys.dm_exec_cached_plans
    https://msdn.microsoft.com/en-us/library/ms187404.aspx
  2. sys.dm_exec_cached_plans
    http://www.rmjcs.com/SQLServer/DMVs/sysdm_exec_cached_plans/tabid/194/Default.aspx