May 1, 2014 at 11:15 pm
Hello,
I have a table , having 900 records. I tried altering the table but it is taking decades to alter a table with just 900 records.
The table was not having a PK, so i thought of adding a PK Constraint , but that query is also taking too long.
Can anybody please help me to understand what is actually the problem over here.
Thanks.
May 1, 2014 at 11:50 pm
Oops double post.
May 1, 2014 at 11:52 pm
You may have another process accessing this table.
Check and wait or kill
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
o1.name 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id
May 1, 2014 at 11:52 pm
Hello,
Check the activitylog and check if any active transactions locking the table, 900 records is not at all a big table.
May 1, 2014 at 11:57 pm
Are you doing with GUI or commandline i.e. T-sql?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 3, 2014 at 4:24 am
Sounds like it's probably blocking from other resources accessing the table. Otherwise, it shouldn't take that long at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply