patternsqlMinor
sp_tables blocking my other statements. Who runs it?
Viewed 0 times
sp_tablesstatementswhoblockingotherruns
Problem
In our application we sporadically experience a locking issue caused by the execution of an
We use SQL Server 2012. In our code and queries in our stored procedures we don't make use of
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
Is there evidence somewhere that this kind of errors triggers an sp_tables on the db?
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)
GOCode 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)
GOContext
StackExchange Database Administrators Q#172653, answer score: 2
Revisions (0)
No revisions yet.