Hi, we have a single sql 2000 server with 4 CPUs. We are finding that each
monday the server critically slows down. Some mondays it is due to blocking,
other mondays we find there are a fair few deadlocks occuring. The server is
fine for the rest of the week. In relation to deadlocks, most of the
deadlocks are specific to two tables. I have set flag 1204 and have logged
the deadlock info to the sql log. I am finding that the locks are occuring
on the index i.e. I only get 'modes' of type range-X-X, range-S-U etc for
instance. So these represent range locks on indexes. This was accompanied by
a fair few 1222 errors (lock timeout errors). The only difference between
the friday and the monday is that we have a maintainance plan running that
drops/recreate indexes for the databases.
It seems all fingers are pointing towards the indexes. I have checked
everything else. Perf mon is running fine. I ran profiler and it mainly only
comes up with deadlock issues. The profiler log also shows the duration of
committed queries taking upto 200 seconds. Maybe I should do some sort of
index analysis. I am not clear on the steps to take. I might use sql
statistics to see which colums are most used on the tables that are involved
in deadlocks and reindex them according to columns with more unique
values/most accessed. Due to the locking, it seems that the indexes are too
clustered together and that they should be more spread out as im getting a
lot of range-x-x locks occuring.
I ran the index tuning wizard through a sample 5 min profiler trace I
generated, but it made no alterations to the indexing it was alreading
Would anyone have any suggestions? Id be much appreciated!