Performance slow of Delete query in SQL Server -
i have application using entity framework db operations. in 1 table when performing delete operation takes more 3 minutes. other similar tables doesn't take time. have debugged code , find out there no issue code.but executing query in sql server took time.
any troubleshooting steps/root cause issue ?
my table below,
id (pk,uniqueidentifier,not null) firstvalue(real,not null) secondvalue(real,not null) thirdvalue(real,not null) lastvalue(int,not null) config_id(fk,uniqueidentifier,not null)
something isn't adding here, we're not seeing full picture...
there multitude of things can slow down deletes (usually):
- deleting lot of records (which know isn't case here)
- many indexes (which suspect case here)
- deadlocks , blocking (is development or production database?)
- triggers
- cascade delete
- transaction log needing grow
- many foreign keys check (i suspect might happening)
can please give screenshot of "view dependencies" feature in ssms? this, right click on table in object explorer , select view dependencies
.
also, can open query on master
database, run following queries , post results:
select name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced sys.configurations (nolock) name in ( 'backup compression default', 'clr enabled', 'cost threshold parallelism', 'lightweight pooling', 'max degree of parallelism', 'max server memory', 'optimize ad hoc workloads', 'priority boost', 'remote admin connections' ) order name option (recompile); select db_name([database_id]) [database name], [file_id], [name], physical_name, [type_desc], state_desc, is_percent_growth, growth, convert(bigint, growth/128.0) [growth in mb], convert(bigint, size/128.0) [total size in mb] sys.master_files (nolock) order db_name([database_id]), [file_id] option (recompile);
Comments
Post a Comment