Background

A quick follow-up to a post over the weekend.

The forwarding post is titled “SQL Server – Index Rebuild – Blocked / Blocking” and it is here.

In that post we spoke of how a scheduled Business Back Office Job was hung and could not proceed.

Using Adam Mechanic’s sp_whoIsActive, we discovered we had were indeed experiencing session blocking, but not an actual deadlock, which in fact would have triggered a vote as to which session to abort

We stopped the blocking database maintenance job which is an Index Defrag job.  The Index Defrag job simply calls Ola Hallengren’s IndexOptimize Stored Procedure.

TroubleShooting

TroubleShooting – Day 1

Adam Machanic

dbo.sp_WhoIsActive

Code

exec  [dbo].[sp_WhoIsActive]

Image

Session

SQL Text

Explanation

1. Sessions
   • Login :- app
     • We are familiar with the app account
     • The sessions are the bottom two
   • SQL :- OpenRowSet
     • A SQL Server Profiler Trace we initiated to track the ongoings
   • Status :- suspended // wait_info :- ASYNC_NETWORK_IO
     • used_memory
       • 10, 000 KB ( 10 MB)

TroubleShooting – Day 2

Microsoft

Dynamic Management Views

sys.dm_exec_sessions

SQL

declare @appNameMSFTODBC sysname
declare @appNameMSFTOffice sysname
declare @appNameMSFTSSMS sysname
declare @appNameMSFTJavaJDBC sysname

declare @clienInterfaceName sysname

set @appNameMSFTODBC = 'Microsoft® Windows® Operating System'
set @appNameMSFTOffice = 'Office'

set @appNameMSFTSSMS = 'Microsoft SQL Server Management Studio - Query'
set @appNameMSFTJavaJDBC = 'Microsoft JDBC Driver for SQL Server'

set @clienInterfaceName = 'Microsoft JDBC Driver 4.0'

select 
		  tblDES.session_id
		, tblDES.program_name
		, tblDES.transaction_isolation_level
		, tblDES.open_transaction_count
		, tblDES.host_name
		, tblDES.client_interface_name
		, tblDES.client_version
		, tblDES.[status]
		, tblDES.[row_count]
		, tblDES.[prev_error]
		, tblDES.reads
		, tblDES.last_request_start_time
		, tblDES.last_request_end_time
		, [timeSinceLastCommunicationInMinutes]
			= datediff
				(
					  minute
					, tblDES.last_request_end_time
					, getdate()
				)

from  sys.dm_exec_sessions tblDES

where  tblDES.session_id >= 50

and	   (
			(
						
				   ( tblDES.program_name like @appNameMSFTODBC )
				or ( tblDES.program_name like '%' + @appNameMSFTOffice + '%' )

			)
			and
			(

				       ( tblDES.program_name != @appNameMSFTSSMS )
				   and ( tblDES.program_name != @appNameMSFTJavaJDBC )
			)

	   )

Output

Microsoft SQL Server Profiler

Image

Explanation

Here is the conversation captured:

1. SQL:BatchStarting
   • SELECT Config, nValue FROM MSysConf
2. SQL:BatchStarting
   • SELECT “dbo”.”oe_dep_audit”.”oe_dep_emp_location”,”dbo”.”oe_dep_audit”.”oe_dep_emp_ssn”,”dbo”.”oe_dep_audit”.”oe_year”,”dbo”.”oe_dep_audit”.”oe_dep_no”,”dbo”.”oe_dep_audit”.”oe_dep_session_ID”,”dbo”.”oe_dep_audit”.”oe_dep_record_type”,”dbo”.”oe_dep_audit”.”oe_dep_record_flag” FROM “dbo”.”oe_dep_audit”
     • Gets all the records in table
3. SQL:BatchStarting
   • SELECT CASE DATABASEPROPERTYEX( DB_NAME(), ‘Updateability’) WHEN ‘READ_ONLY’ THEN ‘Y’ ELSE ‘N’ END
4. RPC:Completed
   • SQL
     • declare @p1 int
       set @p1=1
       exec sp_prepexec @p1 output,N’@P1 char(2),@P2 char(9),@P3 char(4),@P4 int,@P5 char(10),@P6 char(1),@P7 char(1)’,N’SELECT “oe_dep_emp_location”,”oe_dep_emp_ssn”,”oe_year”,”oe_dep_no”,”oe_dep_session_ID”,”oe_dep_record_type”,”oe_dep_record_flag”,”oe_dep_name”,”oe_dep_birthdate”,”oe_dep_relationship”,”oe_dep_SSN”,”oe_dep_sex”,”oe_dep_disabled”,”oe_dep_medical”,”oe_dep_dental”,”oe_dep_optical”,”oe_dep_legal”,”oe_dep_PCP”,”oe_dep_current_patient”,”oe_dep_deleted” FROM “dbo”.”oe_dep_audit” WHERE “oe_dep_emp_location” = @P1 AND “oe_dep_emp_ssn” = @P2 AND “oe_year” = @P3 AND “oe_dep_no” = @P4 AND “oe_dep_session_ID” = @P5 AND “oe_dep_record_type” = @P6 AND “oe_dep_record_flag” = @P7′,’01’,’000000000′,’2012′,1,’a733167067′,’B’,’ ‘
       select @p1
   • Prepares a fetch Statement
5. RPC:Completed
   • SQL
     • exec sp_execute 2,’01’,’00140′,’2013′,3,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,3,’a08938′,’O’,’A’,’01’,’00140′,’2013′,3,’a57784′,’O’,’A’,’01’,’00140′,’2013′,3,’a94593′,’O’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’B’,’ ‘,’01’,’00140′,’2013′,4,’a08938′,’O’,’A’,’01’,’00140′,’2013′,4,’a577848305′,’O’,’A’,’01’,’0014′,’2013′,4,’a94593′,’O’,’ ‘,’01’,’00154′,’2005′,100,’R50001′,’O’,’ ‘,’01’,’00154′,’2006′,1,’N6000′,’B’,’ ‘

Microsoft Network Monitor

Filter

//IP Address
(

    ( IPv4.Address == 10.1.20.182 )

)
and 
(

	(
          not ( Conversation.ProcessName == "Ssms.exe")
    )

	and
        ( 
             not ( Conversation.ProcessName == "PROFILER.exe")
        )
)

Traffic

Image

Explanation

1. Using a Network monitor tool we can see that there is quite a bit of ongoing Network Activity between the client node running MS Access and the Database Server
2. The protocols are plain TCP and TDP
3. And, the ports are the default SQL Server Port of 1433 and the ephemeral ports from the Source Node

Summary

When the table queried from MS Access is reasonably large, the database connection is kept opened.  And, the DB table is locked with an object stability lock.

It is a designed behavior by MS Access and it reduces the amount of local resources on the client host.