Fragmented Disk causing deadlock?

Hi,
I was hoping someone may be able to shed some light on a deadlocking issue w
hich I feel may be due to fragmentation.
Server Spec:
win2k sp4
sql server 2000 sp3
2 G RAM
1600 M allocated to SQL Server
12 G Disk Drive
4 G Database Size
5 G Free on Disk Drive
80 concurrent users, approximately half are connecting through odbc while ha
lf are connecting using jdbc.
I encounter a deadlock where an odbc user is trying to select a particular t
able while a jdbc user is trying to delete from the same table. The affecte
d table has 1.2 million rows. The delete command from the jdbc involves 1 in
dexed row while the select
command from the odbc involves approximately 50 indexed rows.
The issue has only recently appeared after 2 years of running the same odbc/
jdbc applications.
I have tried running the sql statements on separate Query Analyzers to repli
cate the deadlocking issue but this does not reproduce it.
I have rebuilt all indexes, updated statistics, rebooted Server, installed l
atest jdbc driver but no joy.
Recently I have had to shrink the datafile to free up much needed disk space
. There is fragmentation on the data disk, there is one datafile which holds
the database which is fragmented into 5 segments.
The Extent Scan Fragmentation on the database table is 31%.
Has any one else encountered a similar issue with deadlocks occurring due to
a fragmented disk and or fragmented datafile?
Any suggestions are appreciated.Hi Michael
Fragmentation *can* contribute to deadlocking if it lengthens transaction
times by delaying resource acquisition times. Basically, anything that makes
queries run slower can make transaction times longer & therefore increasing
deadlock susceptibility.
I'm surprised that your scan fragmentation is 31% given you said that you've
re-indexed. Does that table have a clustered index?
When you want to trouble-shoot a deadlock, it's important to get a solid
grasp as to exactly what resources are contributing to it. To do this, you
issue the command dbcc traceon (1204, 3605, -1) which causes sql server to
write detailed deadlock information into the error log. When a deadlock
occurs you can then go to the error log and see exactly what resources
(tables, rows, indexes etc) are being deadlocked by what statements.
It would be very useful to get that output to help you further here.
Regards,
Greg Linwood
SQL Server MVP
"Michael" <anonymous@.discussions.microsoft.com> wrote in message
news:684A2202-4242-4F5F-A27F-5E820108A21F@.microsoft.com...
> Hi,
> I was hoping someone may be able to shed some light on a deadlocking issue
which I feel may be due to fragmentation.
> Server Spec:
> win2k sp4
> sql server 2000 sp3
> 2 G RAM
> 1600 M allocated to SQL Server
> 12 G Disk Drive
> 4 G Database Size
> 5 G Free on Disk Drive
>
> 80 concurrent users, approximately half are connecting through odbc while
half are connecting using jdbc.
> I encounter a deadlock where an odbc user is trying to select a particular
table while a jdbc user is trying to delete from the same table. The
affected table has 1.2 million rows. The delete command from the jdbc
involves 1 indexed row while the select command from the odbc involves
approximately 50 indexed rows.
> The issue has only recently appeared after 2 years of running the same
odbc/jdbc applications.
> I have tried running the sql statements on separate Query Analyzers to
replicate the deadlocking issue but this does not reproduce it.
> I have rebuilt all indexes, updated statistics, rebooted Server, installed
latest jdbc driver but no joy.
> Recently I have had to shrink the datafile to free up much needed disk
space. There is fragmentation on the data disk, there is one datafile which
holds the database which is fragmented into 5 segments.
> The Extent Scan Fragmentation on the database table is 31%.
> Has any one else encountered a similar issue with deadlocks occurring due
to a fragmented disk and or fragmented datafile?
> Any suggestions are appreciated.
>