Skip to Content
0
Former Member
Feb 19, 2009 at 02:07 PM

MS SQL 2005 slow performance after upgrade from MS SQL 2000

93 Views

Hi

We recently upgraded our 64 bit Production NW04 BW instance from MS SQL 2000 SP3 to

MS SQL 2005 SP2, the DB is about 1.5TB, since the DB upgrade the BW team and users have seen a significant impact on system performance.

Looking at ST03 I noticed the average DB request time was about double the normal time, I ran the stored procedure sp_updatestats which ran for about 30 minutes and then system performance was greatly improved and the querie performance improved, also searching through the Runtime WorkBench improved as RSA 1 was sluggish.

We had been running NW04 on MS SQL 2000 for about 2.5 years without major

performance issues and without needing to run sp_updatestats, now we

are on MS SQL 2005 we have had to run sp_updatestats frequently

since the upgrade.

Auto update statistics and Asynchronous update statistics

are set in the DB options for SQL 2005, so this

should be updating statistics when required, but does not appear to be

working correctly in MS SQL 2005. I have had to schedule the

sp_updatestats twice weekly after dataloading has completed, as the

business is being affected by the impact of the queryperformance.

I have reviewed article 'SAP with Microsoft SQL Server 2005: Best

Practices for High Availability, Maximum Performance, and Scalability

SQL Server Technical Article' where it states:

'Missing or Outdated Optimizer Statistics

The Auto Update Statistics and Auto Create features in SQL Server 2005

do not work as expected for small tables of less than 500 rows. See SAP

OSS Note 542468 for more information. Following the instructions in the

SAP OSS Note is strongly recommended.

For a quick test, use the Query Analyzer to run update statistics

commands directly. The Query Analyzer can send SQL statements directly

to SQL Server, bypassing SAP BW. Because the problem only occurs on

small tables, it is sufficient to update the information on the

dimension tables. See SAP OSS Note 542468 for more information. The

recommendation is to use SQL Server scheduled or manually updated

statistics on an exception basis only. SAP typically relies on SQL

Server Auto Update Statistics.'

So this document states not to use sp_updatestats except on exception basis?

how often do people run the update stats on MS SQL 2005 ?

The BW team have told me they are updating the statistics as part of

the process chain as per OSS Note 542468 - Activate manual statistic

generation on infocubes in BW.

Has anyone else see a performance impact when upgrading from MS SQL 2000 to

2005?

Thanks.