Unstable sql server - locking/blocking/1222 errors help pls 
Author Message
 Unstable sql server - locking/blocking/1222 errors help pls

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



Sun, 23 Oct 2005 13:23:01 GMT
 Unstable sql server - locking/blocking/1222 errors help pls

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

Aren't you using DBCC DBREINDEX or DBCC INDEXDEFRAG ?

check BOL

jobi

Quote:
> 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



Sun, 23 Oct 2005 13:32:21 GMT
 Unstable sql server - locking/blocking/1222 errors help pls
what I mean is that within the maintenance plan: Options > Reorganize data
and index pages ... is selected i.e. it causes table indexes in the database
to be dropped and re-created with a new fill factor.

cheers, James


Quote:
> .. 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



Sun, 23 Oct 2005 14:59:18 GMT
 Unstable sql server - locking/blocking/1222 errors help pls
 The only difference between

Quote:
>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.

Quote:
>-----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
Quote:
>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
Quote:
>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
Quote:
>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

- Show quoted text -

Quote:
>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

>.



Sun, 23 Oct 2005 16:44:30 GMT
 Unstable sql server - locking/blocking/1222 errors help pls
nope, maint. plan runs on late friday nights
J.


Quote:
> 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

> >.



Tue, 25 Oct 2005 04:32:15 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL Server 7: Error Msg - Error: 1222, Severity: 16, State: 50 Lock Request Timeout period exceeded

2. Lock 1222 with no blocks

3. error 1222 but with no on blocking

4. Error 1222: lock request timeout period exceeded

5. Lock timeout error 1222

6. Error 1222 = Lock request time out period exceeded

7. Error 1222 : Lock request time out period exceeded in Enterprise Manager

8. Lock timeout error 1222

9. trapping error 1222 (lock timeout)

10. Error 1222 (please help me)

11. Sql Server2000 Error 1222

12. 1222 (lock time out) is untrappable


 
Powered by phpBB® Forum Software