Add column on table cause blocking

Question

I was adding column on one of my user table and found out that my alter table query blocked another query which i have no idea where does it come from .

ALTER TABLE [dbo].[table] ADD [column] [int] NOT NULL DEFAULT 0

and this is the query which has been blocked by my add column spid as below

and this is the wait resource key

This is object ID for index 281474978938880

I believe a system query blocked by my add column query. Does anyone have idea what is that system query and why it is blocked when i m adding column on database which currently used by me only.

Answer 1

My guess is that it is Object Explorer in SSMS that generates this query. Should be easy to verify with a trace and clicking around a bit in OE.

Answer 2

This is the full scripts which is blocked by my Add column scripts

SELECT tr.name AS [Name], tr.object_id AS [ID], CAST( tr.is_ms_shipped AS bit) AS [IsSystemObject], CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType], CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted] FROM sys.triggers AS tr LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id WHERE (tr.parent_class = 0) ORDER BY [Name] ASC

Answer 3

Good day @Mario2286 ,

According to your images, I am guessing that you use sp_who2. Right?

If this is the case, then I recommend to check sp_whoisactive which is considered as better solution:

https://github.com/amachanic/sp_whoisactive

Anyhow, using sp_who2 (or sp_whoisactive ), the information is in your hand... just scroll up a bit from what we see in the first image and search session id 58. it seems like session ID 58 is blocked so using sp_who2 (or sp_whoisactive) you can get the information about which application executed this session - in the result set under the column "ProgramName"

Anyhow, execute sp_whoisactive and check the column "program_name" in order to identify the application

One more point...

I believe a system query blocked by my add column query.

You can use the is_user_process column in sys.dm_exec_sessions to tell which are system and which are user sessions

Or again you can use the sp_whoisactive t get this information

Answer 4

That is perfectly normal and expected.

Alter table requires an exclusive schema lock, which blocks all access until it is done.

SPID 59 is blocking itself, waiting for other threads to finish. Your table must be large and it is having to rewrite the table in parallel due to your alter table. This is perfectly normal and expected.

Answer 5

Hi Mario2286-5314,

You can use the following query quoted from this article to view what query is blocking your Add column scripts:

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS BlockedText,
h2.TEXT AS BlockingText,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Best Regards,

Amelia