> 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
> using.

> Would anyone have any suggestions? Id be much appreciated!

> Cheers,
> James

> .. maintainance plan running that drops/recreate indexes ???

> Aren't you using DBCC DBREINDEX or DBCC INDEXDEFRAG ?

> check BOL

> jobi


> > 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
> > using.

> > Would anyone have any suggestions? Id be much appreciated!

> > Cheers,
> > James

>the friday and the monday is that we have a maintainance
plan running that
>drops/recreate indexes for the databases.

>-----Original Message-----
>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

>fine for the rest of the week. In relation to deadlocks,
most of the
>deadlocks are specific to two tables. I have set flag

>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

>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

>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
>using.

>Would anyone have any suggestions? Id be much appreciated!

>Cheers,
>James

>.

> The only difference between
> >the friday and the monday is that we have a maintainance
> plan running that
> >drops/recreate indexes for the databases.

> Do you mean the MP is running on Mondays when you are
> having the problems? If so, change the time it runs.

> >-----Original Message-----
> >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
> >using.

> >Would anyone have any suggestions? Id be much appreciated!

> >Cheers,
> >James

> >.