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

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

Excel Pivot Table

Hi all,

I have upgrade my AS 2000 cube to As 2005, but there has some thing wrong with the Excel Pivot Table. In the cube I have one Time dimension with three attributes, Year, Month, Day. Then I create a Hierachies with three levels, Year, Month, and Day. Finally I retrieve the data though Excel Pivot Table. I put the Time Hierachies in the page field, and put the Year attribtues in the column filed. But no matter how I filter in the page fields, ex select January 2006 and 2007, the column field will display the whole year of data for 2006 and 2007. I didn't have this problem in AS 2000, anyone know the reason.

Thanks,

Tomas

My first guess is the relationships between the attribute hierarchies may not be correct. Could you describe the attribute relationships explicitly defined in the dimension? Could you also clarify if Month is modeled as January, February, etc. or January 2006, February 2006, ..., January 2007, February 2007, etc.?

Thanks,
Bryan

|||

The attributes relationship as follow:

Date(Usage: key)

|__Calendar Month

ex, 2007-1-1

Calendar Month(Usage: Regular)

|__Calendar Year

ex, January 2007

Calendar Year(Usage: Regular)

ex, 2007

Hierachies

*Calendar Year

**Calendar Month

***Date

Thanks,

Tomas

|||

Thomas,

This post was marked as answered. Has the problem been resolved?

Thanks,
Bryan

|||

sorry my mistake to marked as answered. The problem has not been resolved.

Thanks

|||

Not really sure what's going on with this. I'd suggest seeing if this is a problem in other browsers. If it is, try replacing the dimension and see if the problem persists. You may also want to open profiler to snag the query being submitted by Excel to see if it is just assembling a weird statement.

B.

|||

Here is what Excel generate:

WITH MEMBER [Time].[Year - Quarter - Month - Date].[XL_QZX] AS 'Aggregate ( { [Time].[Year - Quarter - Month - Date].[Quarter].&[2005-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2004-01-01T00:00:00] , [Time].[Year - Quarter - Month - Date].[Quarter].&[2003-01-01T00:00:00] } )' SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Time].[Year].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [MaxMinSales] WHERE ([Measures].[Store Sales], [Time].[Year - Quarter - Month - Date].[XL_QZX])

But the result is the whole year, instead of Quarter 1.

|||

I'm not sure exactly what the problem is, but quarter wasn't in the hierarchy description from above. Take a look at the relationship from month to quarter and then quarter to year. I'm thiking that's where the problem is likely at.

B.