HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

sp_tables blocking my other statements. Who runs it?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sp_tablesstatementswhoblockingotherruns

Problem

In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement.

We use SQL Server 2012. In our code and queries in our stored procedures we don't make use of sp_tables.

How can I trace who is the responsible of this call? At least how can I log when this call happens?

I read that it's also called by third parties, in this case how can I track who is responsible?

Edit:

in our application we use Hibernate as ORM and c3p0 for connection pooling.

EDIT 2:
we didn't succed in understanding when and by who the sp_tables was called but today we succeded in replicate the issue.

Seems that the sp_tables is called in responde to a failed delete tried on the schema.

The delete fails because there are other rows referencing the row that we try to delete, so we get an exception

SQLServerException: The DELETE statement conflicted with the REFERENCE constraint..

Is there evidence somewhere that this kind of errors triggers an sp_tables on the db?

Solution

You can set up an extended event session with following code. I tested this and it works (at least the way I was calling).

CREATE EVENT SESSION [CallSP_tables] ON SERVER ADD EVENT 
    sqlserver.sp_statement_completed
   (SET collect_statement=(1)   
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,
    sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,
    sqlserver.plan_handle,sqlserver.query_hash) 
    WHERE ( [sqlserver].[like_i_sql_unicode_string]
            ([object_name],N'sp_tables') 
        ) 
    ), 
ADD event sqlserver.sql_statement_completed( 
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash)) 
ADD TARGET package0.event_file(SET filename=N'C:\callspTables.xel',
        max_file_size=(50), 
        max_rollover_files=(100)) WITH (max_memory=4096 kb, 
        event_retention_mode=allow_multiple_event_loss, 
        max_dispatch_latency=120 seconds, 
        max_event_size=0 kb, 
        memory_partition_mode=none, 
        track_causality=OFF, 
        startup_state=ON)
        GO

Code Snippets

CREATE EVENT SESSION [CallSP_tables] ON SERVER ADD EVENT 
    sqlserver.sp_statement_completed
   (SET collect_statement=(1)   
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,
    sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,
    sqlserver.plan_handle,sqlserver.query_hash) 
    WHERE ( [sqlserver].[like_i_sql_unicode_string]
            ([object_name],N'sp_tables') 
        ) 
    ), 
ADD event sqlserver.sql_statement_completed( 
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash)) 
ADD TARGET package0.event_file(SET filename=N'C:\callspTables.xel',
        max_file_size=(50), 
        max_rollover_files=(100)) WITH (max_memory=4096 kb, 
        event_retention_mode=allow_multiple_event_loss, 
        max_dispatch_latency=120 seconds, 
        max_event_size=0 kb, 
        memory_partition_mode=none, 
        track_causality=OFF, 
        startup_state=ON)
        GO

Context

StackExchange Database Administrators Q#172653, answer score: 2

Revisions (0)

No revisions yet.