MS SQL 2005 slow performance after upgrade from MS SQL 2000

[Former Member]

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.

[Former Member]

Hi

I increased the SQL server memory to 12 GB, thats improved the data hit ratio to 98.5%, i'm also running the update stats twice a week now out of hours.

The BW team have also been investigating their data model designs, as a result they are improving the aggregates, which have significantly improved performance on the most popular queries which were slower, now they are down from 1 minute to around 10 seconds run time, so the user base is much happier with that.

I think throwing more resource - more cpu/memory/64 bit and the project expected signifcant performance improvements, but we did not see that and the BW model required tuning.

Thanks for all the advice.

[Former Member]

Hi my friend

For BW system, I think Auto update statistics should not be activated and performed during day time. Is it possible to pull out sp_configure settings of both previous one and current one and compare them? Besides, what's your tempdb size and layout?

Effan

[Former Member]

Hi Effan

We are now running the update stats twice a week, on a Monday and Wednesday at 06:00, just after the BW dataloads are completed, so they capture the data changes, they run for about 30 mins, so there is minimum impact on the users at that time.

The TEMPDB is 37GB and the BW DB is 1.3TB, both are set to auto extend, the datafiles for both DB's are on a SAN, so there should be no contention, looking in ST06 at the Disk Access, there is no queue or wait time on the disks.

There is 32GB on the server and we have SQL limited to 7GB, the data cache hit ration is 97.5% and proc cache is 92.5%, so they look ok.

The SP configure settings of the old server have been lost now, as we migrated from the 32 Bit server a couple of months ago.

The SAP BW instance and DB have been started for a month, so the cache should be uptodate, I've looked at the BW queres/system using RSRV & RSRT and they look ok, quries to when read navigation and hierachies, the cache is set for main memory no swapping. As its BW, its difficult to monitor performance, as this is dependant on the reports users are running, so I am working with the BW team to tune these, I have also recommended they investuagte partitioning, as this is fully available now in SQL 2005.

Thanks,

Paul.

[Former Member]

Hi,

Your experience make me worry We are in the middle of process to migrate our current BW landscape from 32-bit to 64-bit VMware and of course database from SQL 2000 to 2005. Based on Microsoft's literature, we are expecting SQL 2005 will improve the performance. Looks like your results just showed the opposite. Could you post here if and how you solve your performance problem?

Thanks,

Yujun Ran

[Former Member]

Hi Yujun

We have been running MS SQL 2005 on our ECC 5 instance for about 1 year without a performance issue, having upgrade from SQL 2000 earlier than BW.

The problem appeasr to be more around BW on SQL 2005, SAP have issued OSS Note 542468 - Activate manual statistic generation on infocubes in BW, as there is a problem with statistics being generated incorrectly on small tables, this can have an impact on BW due to the Star Schema, the BW team have followed the guidance and are creating the statistics for the Cubes in the process chains, but we are still getting compalins from the users that queries are slower than before we migrated, I am trying to investigate the problem to find out if its the way users running the reports (too much data) etc and whether the BW queries are efficient, using characteristics correctly etc.

The infrastructure team have checked the network and all looks ok there, we are a European roll out so have about 15 regions on SAP.

I would advise you thoroughly test the migration on you DEV and QA and try and benchmark performace with realistic data quantaties to analyse your performace, when we miugrated from 32 bit to 64 bit we moved to brand new 64 Bit servers running faster CPU's and more memory, so we were expecting a significant performance improvement, but the perception from the business is that query performance has decreased. hense my investigations !

Paul.

[Former Member]

Hi Paul,

we are already on SQLServer 2008 but here are some thoughts (that improved performance) back from the old days:

We ran (and still do so) autoupdate statistics sync (not async). You mentioned that SQL Server is fixed on 7 GB of RAM. On a BI system that is not enough. I would give SQL Server 16 GB of RAM. SQL Server is doing the job of joining the tables and putting the data together - not the SAP instance. Please also check Note 1178916 - Update_Tabstats job causes system performance problems. Other than that, we are running no extra update statistics jobs.

Sven

[Former Member]

Paul,

Thanks for the information and advise. We are doing the same as you did. And users already expect better performance because someone already told them the "great performance" 64-bit and SQL 2005 with more memory/CPU will have. To add another parameter here is that we are actually planning run our production on VMware environments! With all these combinations, I am not sure the eventually end-user performance will end up. And I can expect we will have many tuning (SQL, SAP, VM) things to do.

Thanks,

Yujun Ran

[Former Member]

Hello ecnirp,

It is generally recommended to update statistics once per week.

u can also refer to SAP note 98679 which talks about MSSQL parameters,it can help u improve the perfromance of system

Rohit