SQL Server – Linked Server – TroubleShooting – SysInternals / Process Monitor

Background

Though better minds would disagree, I am lured into running OLE/DB providers out of process when configuring Linked Servers.

 

SysInternals/Process Monitor

For one, it is a bit easier to debug and troubleshoot via SysInternals/Process Monitor.

 

Filter

Here is what we will be filtering on…

Image

Explanation

  1. Process Name
    • is
      • dllhost.exe

 

Capture

Image

Explanation

  1. sqlservr.exe
    • TCP*
      • TCP Accept
      • TCP Receive
      • TCP Send
  2. DLLhost.exe
    • Process Create
    • Thread Create
  3. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • combase.dll
      • sechost.dll
      • rpcss.dll
  4. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( OLE-DB )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32.dll
      • C:\Windows\System32\MSDART.dll
      • C:\Windows\System32\ole32.dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32r.dll
  5. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • C:\Windows\System32\comsvcs.dll
      • C:\Windows\System32\authz.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
  6. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDB12.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\VERSION.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDBgmr12.dll
      • OCI
        • E:\app\oracle\client\product\12.2.0\client_1\bin\OCI.dll
        • C:\Windows\System32\OCI.dll
        • E:\app\oracle\client\product\12.2.0\client_1\oci.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCP120.dll
  7. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows – MSVCP* )
    • dll
      • Visual C++ Redistributable Packages for Visual Studio 2013
        • C:\Windows\System32\msvcp120.dll
        • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCR120.dll
  8. Oracle.key
    • E:\app\oracle\client\product\12.2.0\client_1\bin\Oracle.Key
  9. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
        • The oraociei12.dll file is the main binary for Instant Client
      • E:\app\oracle\client\product\12.2.0\client_1\oraons.dll
        • Oracle Basic Instant Client & Oracle Basic Light Instant Client
  10. Oracle Key & OLEDB DLLS
    • E:\app\oracle\client\product\12.2.0\client_1\Oracle.Key
    • E:\app\oracle\client\product\12.2.0\client_1\Oraoledbic12.Dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDB12us.dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDBpus12.dll
  11. Oracle Network
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\oraaccess.xml
    • C:\Windows\System32\mswsock.dll
      • MS Windows WinSock
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\sqlnet.ora
    • E:\app\oracle\client\product\12.2.0\client_1\log\diag\clients
    • C:\Windows\System32\SHCore.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\intchg.ora
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnav.ora
    • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnames.ora
    • E:\app\oracle\client\product\12.2.0\client_1\NETWORK\mesg\tnsus.msb
  12.  Network
    • TCP Reconnect
      • :

        -> [destination/hostname]:[destination/port-number (1521) ]

  13. Oracle Client Logs
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace\sqlnet.log
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\incident
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\metadata
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\alert\log.xml
  14. Network
    • TCP Send/Receive/Disconnect
      • [destination/hostname]:[destination/port-number] ->

        :

  15. Thread Exit/Process Exit/Close File

 

 

Oracle Log files

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_####_###\trace

Image

Textual


***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
Time: 21-FEB-2018 10:46:59
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNS
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Message 505 not found; No message file for product=NETWORK, facility=TNS
nt secondary err code: 60
nt OS err code: 0
Client address: 

Tabulated

  1. Errors Recorded
    • Main
      • Fatal NI connect error 12170
    • Detail
      • TNS-12535
        • ns secondary err code :- 12560
        • nt main err code :- 505
      • TNS-00505
        • ns secondary err code :- 60
        • nt OS err code :- 0

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_##\alert\log.xml

Image

Explanation

XML file which contains error messages from the Oracle Connectivity Client.

 

Summary

Our error is “Fatal NI connect error 12170” and that translates to “ORA-12170: TNS:Connect timeout occurred“.

Basic firewall issues.

Though DCOM itself as a container does not expose a lot of errors, client applications themselves are free to do so.

Using SysInternals’ Process Monitor, we are able to quickly familiarize ourselves with our OLE-DB Provider.

 

References

  1. Native Instruments
    • Native Instruments > General > OS and Computer Tuning
      • Windows Error Message: Missing MSVCP120.dll File
        Link
  2. Oracle Technology Network / Database / Database Features / Oracle Call Interface
    • Oracle Instant Client ODBC Release Notes
      Link
  3. Burleson Consulting
    • Donald Burleson
      • fatal ni connect error 12170
        Link

SQL Server – Linked Server – Identify Dependencies and Usage

Introduction

We are slated for a major database upgrade and I am trying to document our current DB Environment.  One of the areas that used to be a bit difficult to fully document are Linked Server Dependencies.

Background

There is likely a bit of curmudgeon and shall we say general grumpiness in writers.  Be they Political reporters, crime beat writers, or even the Skip Bayless of the world.

 

Document Linked Server Usage

So again I will rather write about the difficulty of finding SQL Linked Server Object Dependencies, but Microsoft has closed that trap.

And, so here I am and I might as well give it to you straight.

If you ‘re running MS SQL Server v2008 or later and you need to find objects that reference Linked Server, then issue a query that is similar to:


SELECT 

	  schema_name(tblObjectReferencing.schema_id) as referencingSchema
	, OBJECT_NAME(tblSQLExpressionDependency.referencing_id) AS referencingObject
	, tblObjectReferencing.type_desc as referencingType
	, referenced_server_name AS referencedServer
	, referenced_database_name AS referencedDatabase
	, referenced_schema_name AS referencedSchema
	, referenced_entity_name as referencedEntity

FROM sys.sql_expression_dependencies tblSQLExpressionDependency

    left outer join sys.objects as tblObjectReferencing

      on   tblSQLExpressionDependency.referencing_id = tblObjectReferencing.object_id

    left outer join sys.objects as tblObjectDependency

       on   tblSQLExpressionDependency.referenced_schema_name 
               = schema_name(tblObjectDependency.schema_id)
       and  tblSQLExpressionDependency.referenced_entity_name 
             = tblObjectDependency.name

where tblSQLExpressionDependency.referenced_server_name is not null

The star of the show is of course the sys.sql_expression_dependencies DMV.

If you want to iterate this information for a specific database object, you can rest on the sys.dm_sql_referenced_entities dynamic management view.


Syntax

SELECT 

	  tblSQLReferencedEntity.referenced_server_name as referencedServer
	, tblSQLReferencedEntity.referenced_database_name as referencedDatabase
	, tblSQLReferencedEntity.referenced_schema_name as referencedSchema
	, tblSQLReferencedEntity.referenced_entity_name as referencedEntity

FROM sys.dm_sql_referenced_entities([full-object-name], [object-type]) 
        tblSQLReferencedEntity

where  tblSQLReferencedEntity.referenced_server_name is not null


Sample


SELECT 

	  tblSQLReferencedEntity.referenced_server_name as referencedServer
	, tblSQLReferencedEntity.referenced_database_name as referencedDatabase
	, tblSQLReferencedEntity.referenced_schema_name as referencedSchema
	, tblSQLReferencedEntity.referenced_entity_name as referencedEntity

FROM sys.dm_sql_referenced_entities('dbo.usp_BuildCustomerTree', 'Object') 
          tblSQLReferencedEntity

where  tblSQLReferencedEntity.referenced_server_name is not null

Please keep in mind that you have to pass in a valid object name and object class. Null will not do.

Also, keep in mind that you should run this in each database that you ‘re trying to get this information for.

 

Requirements

Your SQL Server Instance must at least be running v2008.

MS SQL Server v2005 and below

If you happen to be running MS SQL Server v2005 or below, I will suggest that you try this more voluminous path.

Syntax:


select 
	  tblComment.id as objectID
	, tblUser.name as schemaName
	, object_name(tblComment.id) as objectName
	, tblObject.type as [objectType]
	, tblComment.[text] as objectText
	, datalength(tblComment.[text]) as [datalength]
	, tblComment.colid

from   syscomments tblComment

	  inner join sysobjects tblObject
		on tblComment.id = tblObject.id

Sample:

To zero in on specific Linked Server, you can try:

select 
	  tblComment.id as objectID
	, tblUser.name as schemaName
	, object_name(tblComment.id) as objectName
	, tblObject.type as [objectType]
	, tblComment.[text] as objectText
	, datalength(tblComment.[text]) as [datalength]
	, tblComment.colid

from   syscomments tblComment

	  inner join sysobjects tblObject

		on tblComment.id = tblObject.id

where  tblComment.text like '%LS_HRDB%'

The query will look for objects that references LS_HRDB; note that it will also pick up objects that just contain LS_HRDB as part of their comments (/* LSD_HRDB*/).

References

Technical: Microsoft – SQL Server – Data I/O – Computed Columns

Technical: Microsoft – SQL Server – Data I/O – Computed Columns

Introduction

For the last couple of weeks or so, I have struggled with a little SQL Script that transfers data between two SQL Server Instances.

Background

The Transact SQL is simple enough.  It uses Microsoft’s SQL Server highly regarded Linked Server (Heterogeneous Database) to copy data from a legacy system to a new one.

Original Query – Linked Server

Here is the original query that uses Linked Server



        declare @logdateDestMax datetime
        declare @logdateDestMaxDefault datetime

        set @logdateDestMaxDefault = '1/1/1900'

        set @logdateDestMax = ( 
                                 select max(createDate) 
                                 from dbo.employee 
                              )

        if ( @logdateDestMax is null)
        begin
              set @logdateDestMax = @logdateDestMaxDefault
        end
	insert into dbo.employee	
        (
	   [username]
	  ,[createDate]
	  ,[createdBy]
	)
	select 
	   [username]
	  ,[createDate]
	  ,[createdBy]
        from [DBLABJ_NC_10_ODBC].[dbHR].dbo.employee tblSrc
	where [logdate] > @logdateDestMax

Rewrite  Query – OpenQuery

As I know that 4 part name queries can sometimes be problematic and slow, I thought may be I should rewrite to use openquery.

Here is the re-written query that uses Linked Server \ Open Query


        declare @logdateDestMax datetime
        declare @logdateDestMaxDefault datetime

        set @logdateDestMaxDefault = '1/1/1900'

        set @logdateDestMax = ( 
                                 select max(createDate) 
                                 from dbo.employee 
                              )

        if ( @logdateDestMax is null)
        begin
              set @logdateDestMax = @logdateDestMaxDefault
        end
	insert into dbo.employee	
        (
	   [username]
	  ,[createDate]
	  ,[createdBy]
	)
	select 
	   [username]
	  ,[createDate]
	  ,[createdBy]
        from openquery(
                           [DBLABJ_NC_10_ODBC]
                         , 'select * from [dbHR].dbo.employee'
                      ) tblSrc
	where [logdate] > @logdateDestMax

OpenQuery – Poor Auditioning

The example above is a very poor staging of the beenfits of openquery.

Usually, we want a query that can be completely serviceable at the Other Server. The query above still brings all the data over to the querying server.

Once all the data is brought over, the resident SQL instance then issues a comparison.

There are things we can do to better ‘position’ the linked Server.  Our alternate choices includes:

  • Creating a Stored Procedure on the Linked Server.  The SP will  accept parameters that will help ‘our case’

Compare 4 part name and OpenQuery

Compare Execution Plans

Here is a quick comparison of both plans.

ExecutionPlan

One can quickly see that the plan that uses the 4 part name is 53%, while the one that uses openquery is at 36%.

Compare IO Stats

With “set statistics io on”, we can see that IO stats for the first query:

Table 'employee'. Scan count 1, logical reads 1369, physical reads 0, read-ahead 
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

We do not have corresponding IO stats for the query that uses openquery; as that query is completely ‘materialized’ on the Linked Server and seemingly Profiling Stats are not returned for externally ran queries.

Remote Scan Stats

If we focus on the remote Scan Stats, we are able to dig a bit deeper into our estimated and actual costs:

Linked Server Cost

RemoteQuery-Slow-4PartName

Open Query Cost

RemoteQuery-Slow-OpenQuery

Here is the break-down

Item 4 part name Open Query
Actual Number of Rows 5 49821
Actual Rebinds  1 0
Actual Rewinds  0 0
Estimated CPU Cost  4.9921 3.3633
Estimated I/O Cost  0 0
Estimated Number of Executions 1 1
Estimated Number of Rows  14936.3 10000
Estimated Operator Cost  4.9921 (100%) 3.36333 (100%)
Estimated Rebinds  0  0
Estimated Rewinds  0  0
Estimated Row Size 448 Bytes  448 Bytes
Estimated SubTree Cost 4.9921  3.36333
Logical Operation Remote Query Remote Scan
Node ID 1 1
Number of Executions 1 1
Output List  DBLAB].[dbo].[UpdateLog].col1, [DBLAB].[dbo].[UpdateLog].col2, [DBLAB].[dbo].[UpdateLog].logdate, [DBLAB].[dbo].[UpdateLog].col3 SQLNCLI11
Parallel False False
Physical Operation  Remote Query Remote Scan
Remote Object SELECT “Tbl1001”.”col1″ “Col1003″,”Tbl1001”.”col2″ “Col1004″,”Tbl1001″.”logdate” “Col1005″,”Tbl1001”.”col3″ “Col1006” FROM “DBLAB”.”dbo”.”UpdateLog” “Tbl1001” WHERE “Tbl1001″.”logdate”>? select * from [DBLAB].dbo.UpdateLog tblSrc
Remote Source  Named Instance name Actual Name Linked Server

Explanation

  • “Actual Number of Rows”.  OpenQuery is bringing back all the records and comparing them against our data filter.  It seems 4 part name is only indicating the number of records that will be returned by the select statement
  • For 4 part name, we have rebinds.  For best explanation of what rebinds are, please “Check out” Scary DBA’s Blog posting – Rebind and Rewind in Execution Plans (http://www.scarydba.com/2011/06/15/rebind-and-rewind-in-execution-plans/“)
  • Estimated CPU Cost – 4 the four part name it is a bit higher
  • Estimated Number of Rows – In case of  “4 Part Name”, the Engine can consult with the Linked Server and ask it for the number of records in the table; whereas, for the “Open Query”, the  query will have to be ran.  And, so we get a place holder 10,000 count
  • Estimated Operator Cost – Same as Estimated CPU Cost; it seems most of our cost is attributed to CPU; very little I/O cost
  • Estimated Sub Tree Cost – All query costs eaten up by this operation
  • Logical Operation – “Remote Query” (Linked Server) vs “Remote Scan” ( openquery)
  • Output List :- Openquery lists our SQL Server Provider (SQLNCLI11)
  • Parallel :- Our record count is relatively low and no parallelism
  • Physical Operation – Same as Logical Operation **** “Remote Query” (Linked Server) vs “Remote Scan” ( openquery) ***
  • In this case our Linked Server is on the same box as our calling Server.  For 4 part name, our entry is the Instance Name, not the full Instance Name, just the instance itself (SQLExpress).
    And, the openquery as the full instance name for our Linked Server

Summary

  • Linked Server offers a bit more reliable instrumentation
  • I/O Costs for the remote scan that is employed when we use OpenQuery are hidden

Bcp

Intro

We decided to try bcp as Linked Server “selects” was taken about 1.5 hours for a measly 15K (15000) records.

Now that we are out of MS SQL Server, we will use Gammadyne’s Timer.exe ( http://www.gammadyne.com/cmdline.htm#timer) to time the performance of our bcp sessions.

As we are only profiling for performance, we will tweak our code by making a couple of adjustments.

  • Add -L <N (-L 1000) :- Only return 1000 records

Bcp Performance – Table

Get data from the entire table.

bcp  "dbo.employee" out employee.txt -c -S hrDB -d hr -T -L 1000

 Image

bcpTable

  • So we are only getting 11.63 rows per sec.
  • And, it is taken us 90 seconds to get 1000 records

Bcp Performance – QueryOut

Get data for all columns.

bcp  "select * from dbo.employee" queryout employee.txt -c -S hrDB -d hr -T -L 1000

 Image

bcpQueryOut

  • So we are only getting 11.56 rows per sec.
  • And, it is taken us 86.8 seconds to get 1000 records

So our numbers are still not nothing to jump around about.

Looked at the following areas on the Linked Server:

  • Wait Stats

Bcp Performance – QueryOut – Filtered ColumnList

Reviewed our column list  and noticed that one of the columns we are bringing back is a computed column.

We removed that column by explicitly listing the columns that we want:

bcp  "select FIRSTNAME, LASTNAME, USERNAME, PASSWORD from dbo.employee" queryout 
employee.txt -c -S hrDB -d hr -T -L 1000

 Image

bcpQueryOutFilteredColumnList

  • So we are only getting 62,500 rows per sec.
  • And, it is taken us 62 milliseconds to get 1000 records

Computed Columns

So our biggest drag was the “computed column”.  We do not even need it, as it is will be regenerated on the other side.

Find Computed Columns

Find Computed Columns – MS SQL Server 2000


select 
		  tblUser.name as schemaName
		, tblObject.name as objectName
		, tblColumn.name as columnName

from   syscolumns tblColumn

	  inner join sysobjects tblObject

	 	on tblColumn.id = tblObject.id

	 inner join sysusers tblUser

		on tblObject.uid = tblUser.uid

where  tblObject.[type] = 'U' 
and    tblColumn.iscomputed = 1

Find Computed Columns – MS SQL Server 2005 and above



select 
		  tblSchema.name as schemaName
		, tblObject.name as objectName
		, tblColumn.name as columnName
		, tblColumnComputed.definition
		, tblColumnComputed.is_computed as [isComputed]		
		, tblColumnComputed.is_persisted as isPersisted		

from   sys.columns tblColumn

	  inner join sys.objects tblObject

	     on tblColumn.object_id = tblObject.object_id

	  inner join sys.schemas tblSchema

	     on tblObject.schema_id = tblSchema.schema_id

	  inner join sys.computed_columns tblColumnComputed

	     on  tblColumn.object_id = tblColumnComputed.object_id
	     and tblColumn.column_id = tblColumnComputed.column_id

where  tblObject.type = 'U'
and    tblColumn.is_computed = 1


Image

ListComputedColumns_v2005

Conclusion

So again keep an eye on computed columns.

They were introduced in MS SQL Server v2000, but at that time they can not be persisted.

From v2005 on out, they can be persisted.

Persisted means their values are computed upon initial creation or subsequent updates.  And, thereafter the saved value is read and thus “readers” are not forced to pay the price of re-calculations.

But, even then make sure that you only include them when you need them.  Be especially careful when you use * (select * from <schema-name>.<object-name>); or when you reference the entire table.