Is there a way to see the locks associated with a delete statement on a table (tab1) that has 5 or 6 Foreign key relationships. Trying to understand the impact of the delete on concurrency There are several delete deadlocks on one of the foreign key tables (tab2) and the system does not delete from the table (tab2) that is throwing the delete deadlock error. Wondering about the impact of foreign keys on deletes on Tab1 on Tab2.
Didn't see anything in query analyzer that would show the actual locks. It seems to have scans or such but no lock levels etc are shown.
Does anyone have any knowledge of how to see the actual locks thrown by a given statement. The delete on Tab1 statement is very quick so using EM has proved fruitless.
MikeOh, such simple question but resolving blocking/deadlocking is really an art. You might want to start here.
http://support.microsoft.com/kb/224453
Showing posts with label relationships. Show all posts
Showing posts with label relationships. Show all posts
Wednesday, March 7, 2012
Friday, February 17, 2012
Delete query taking long time to execute
hi,
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.
The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.
I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
Hi,
This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> hi,
> I have a table which is a Master to other 12 tables and it is Child for
> other 2 tables.
> Therefore, It has 14 relationships with other tables. The size of table is
> very small. (approx 30-40 rows). It also has a clustered index in its
> primary
> key.
> The problem I am facing here is that when I try to delete a row from the
> table using its primary key in where clause, it takes more than 30 seconds
> to
> execute it. Any subsequent attempt will be of a shorter duration (approx
> 20-25 sec.) but still it is very time taking.
> I can't understand what could be the problem here when the table size is
> very small. Is it because of constraints or any other sort.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||Thank you Hugo for the information.
FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.
Thanks
Hari
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> Hi Hari,
> There are of course two downsides to disabling the constraints:
> 1. If there is some error in the manual checking process, or some
> concurrent opdate after the manual checking, the database may be left in
> an unconsistent state.
> 2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
> know this, so the constraints are marked as "not trusted" once they are
> re-enabled. That means that the optimizer can no longer rely on those
> constraints for considering shortcuts for the query plan, which may
> result in longer running queries. (Unless you use the WITH CHECK option
> when the constraints are re-enabled, but in that case you still get the
> same performance hit, only at a later time).
> --
> Hugo Kornelis, SQL Server MVP
I have a table which is a Master to other 12 tables and it is Child for
other 2 tables.
Therefore, It has 14 relationships with other tables. The size of table is
very small. (approx 30-40 rows). It also has a clustered index in its primary
key.
The problem I am facing here is that when I try to delete a row from the
table using its primary key in where clause, it takes more than 30 seconds to
execute it. Any subsequent attempt will be of a shorter duration (approx
20-25 sec.) but still it is very time taking.
I can't understand what could be the problem here when the table size is
very small. Is it because of constraints or any other sort.
Please comment if you have any idea about it. Any help would be highly
appreciated.
Thanks!
Regards,
~Somesh
Hi,
This is because of the multiple relationship. While deleting it checks all
the relationship. Where as if you execute a SELECT statement for the same
query
it will be faster. If you can do this during offline you could disable the
constraints [See ALTER TABLE WITH NOCHECK] to make the DELETE faster.
Since the number of records is very less it is not required to disable the
constratins
Thanks
Hari
"Somesh" <Somesh@.discussions.microsoft.com> wrote in message
news:FD6E5B4A-34AF-4CF9-948D-6EA21944587C@.microsoft.com...
> hi,
> I have a table which is a Master to other 12 tables and it is Child for
> other 2 tables.
> Therefore, It has 14 relationships with other tables. The size of table is
> very small. (approx 30-40 rows). It also has a clustered index in its
> primary
> key.
> The problem I am facing here is that when I try to delete a row from the
> table using its primary key in where clause, it takes more than 30 seconds
> to
> execute it. Any subsequent attempt will be of a shorter duration (approx
> 20-25 sec.) but still it is very time taking.
> I can't understand what could be the problem here when the table size is
> very small. Is it because of constraints or any other sort.
> Please comment if you have any idea about it. Any help would be highly
> appreciated.
> Thanks!
> Regards,
> ~Somesh
>
|||Thank you Hugo for the information.
FYI, I have asked him to do the NOCHECK when the system is offline (No users
are connected) and after the delete activity he can
make ALTER statement WITH CHECK. For one of my manual delete ( 5 million
record deletion) I did this and identified a huge
performance difference.
Thanks
Hari
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:55ccm2dgsfvcdco0iogo0oge8j9bvs953c@.4ax.com...
> On Thu, 23 Nov 2006 02:08:26 -0600, Hari Prasad wrote:
>
> Hi Hari,
> There are of course two downsides to disabling the constraints:
> 1. If there is some error in the manual checking process, or some
> concurrent opdate after the manual checking, the database may be left in
> an unconsistent state.
> 2. Even if all checks were made aith 100% accuracy, SQL Server doesn't
> know this, so the constraints are marked as "not trusted" once they are
> re-enabled. That means that the optimizer can no longer rely on those
> constraints for considering shortcuts for the query plan, which may
> result in longer running queries. (Unless you use the WITH CHECK option
> when the constraints are re-enabled, but in that case you still get the
> same performance hit, only at a later time).
> --
> Hugo Kornelis, SQL Server MVP
Subscribe to:
Posts (Atom)