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.