Excessive locks after upgrade to 2005 crashes server

We recently upgraded to sql 2005 from 2000.
this server has a lot of traffic around 1200 batches/sec
while the traffic is high the server was fast and it ran ok, around 30-70%
cpu.
and perf mon showed around 10-50,000 locks
after upgrading to 2005 it still works find, but as part of the upgrade
process
we run the following SQL statement:
EXEC dbo.sp_MSforeachtable 'update statistics ? WITH FULLSCAN'
which was recommended as part of the upgrade to 2005,
but after running this statement the SQL Server:Lock Requests/sec counter
goes into the millions and eventually crashes the server.
essentially this happens as soon as we turn on bespoke batch updates to our
site
which run various kinds of update and insert sql statements against the
database in rapid succession, these statements also fire various triggers
when each row is updated/inserted. but again it all worked fine in our 2000
install
Any help much appreciated. or info in how changes to the nature of
statistics in 2005 would affect queries. we have upgraded the SQl server to
2005 compatability as well
thanks
aaron navarro"aaron n" <aaronn@.discussions.microsoft.com> wrote in message
news:36EB9D47-75B6-4E1C-8CC2-08E4404736F3@.microsoft.com...
> We recently upgraded to sql 2005 from 2000.
> this server has a lot of traffic around 1200 batches/sec
> while the traffic is high the server was fast and it ran ok, around 30-70%
> cpu.
> and perf mon showed around 10-50,000 locks
> after upgrading to 2005 it still works find, but as part of the upgrade
> process
> we run the following SQL statement:
> EXEC dbo.sp_MSforeachtable 'update statistics ? WITH FULLSCAN'
> which was recommended as part of the upgrade to 2005,
> but after running this statement the SQL Server:Lock Requests/sec counter
> goes into the millions and eventually crashes the server.
> essentially this happens as soon as we turn on bespoke batch updates to
> our
> site
> which run various kinds of update and insert sql statements against the
> database in rapid succession, these statements also fire various triggers
> when each row is updated/inserted. but again it all worked fine in our
> 2000
> install
> . . .
It is quite possible that certian queries in your workload are more
expensive in SQL 2005. As part of the 2005 upgrade you should analyze the
workload and see if you can improve any of most expensive queries.
Using the Management Studio Server Reports, SQL Profiler (or Server Trace)
and the Database Engine Tuning Advisor you can identify and start to analyze
poor-performing queries. Rewriting queries, changes in the physical
database design, query hints, or Plan Guides are the basic tools for
addressing troublesome queries.
Another option in SQL 2005 to reduce locking behavior is to put the database
in Read Consistent Snapshot Isolation mode, which eliminates shared locks
altogether and silently redirects readers to the row version store.
David