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

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

Post by James E » Thu, 08 May 2003 14:23:01



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

 
 
 

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

Post by Jobi » Thu, 08 May 2003 14:32:21


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


 
 
 

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

Post by James E » Thu, 08 May 2003 15:59:18


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


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

 
 
 

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

Post by chri » Thu, 08 May 2003 17:44:30


 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

>.

 
 
 

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

Post by James E » Sat, 10 May 2003 05:32:15


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

> >.

 
 
 

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

Hi,

I am regulary getting the above message, localised within a particular
database on our server.

I realise that this is a locking problem but how can I get more
information about this and where it is occuring.

I have been advised to use a trace, can anybody advise me what trace
flag to use to be able to localise where the errors are occuring.

Any help would be welcome

Thank you
-BKaur

2. MIT software developer, database expert, seeks offsite work - SQL Server DBA, Sybase, C++, Delphi, VB, HTML, TCPIP, NT, Unix, Dialogic

3. Lock 1222 with no blocks

4. SQL-Query in VB5

5. error 1222 but with no on blocking

6. Problem using db_owner group

7. Error 1222: lock request timeout period exceeded

8. Keymaps in Windows/4GL

9. Lock timeout error 1222

10. Error 1222 = Lock request time out period exceeded

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

12. Lock timeout error 1222

13. trapping error 1222 (lock timeout)