Performance Issue on REISBP since SQL Server upgrade to 2005

[laurie_mcginley]

We recently upgrade our database from 2000 to 2005, using ECC5 (that didn't change). Since the upgrade one transaction in particular has taken a big hit... REISBP. It can range from seconds to 15+ minutes now to complete, which didn't happen before. the underlying table is BUT000.

Has anyone experienced this? Or other significant slowdowns after an upgrade.

(on a 32 bit Windows NT server - can't do anything about that for now.)

Thanks

Laurie

[markus_doehr2]

Did you run an update statistics after the upgrade?

Markus

[laurie_mcginley]

Yes, I did run update stats, also SGEN and used the "best practice" conifg options for the server settings (mem, parallel query, etc).

Unfortunately the users tend to use a business name = %sometext% when running this report... not good practice, but they were doing this prior to the upgrade and didn't have the performance hit.

Thanks

Laurie

[markus_doehr2]

I would do an SQL trace of the affected transaction and see which of the statement is taking too much time (use ST05). Maybe an additional index on the affected table is of help.

Markus

[laurie_mcginley]

I did run the STO5 sql trace and the issue is with the vibpobjrel table part query. There are no further indexes to put on that table tho help out, based on how the users are accessing the report. Based on how they are using the report, several thousand records can be returned, which are then parsed as parms in the next part of the report where a "for all entries" clause is used... causing many union all statements.

What I don't understand is why the average execution time was much less, using the same criteria, in SQL 2000.

At this time we've instructed the users to not use a text criteria in the name field if at all possible, as this forces reading the entire BUT000 table, multiple times based on the return from vibpobjrel.

Perhaps this is just a taste of the 2005 performance on our systems. I do see some increased times for other transaction over the previous version, but none stand out as much as this one.

Thanks,

Laurie

[markus_doehr2]

> Perhaps this is just a taste of the 2005 performance on our systems. I do see some increased times for other transaction over the previous version, but none stand out as much as this one.

I can just recommend to open an OSS call and let the support (BC-DB-MSS) have a look on the system/statements. I would also suggest you try the latest service pack and cumulative fixes (on a testsystem).

Markus

[laurie_mcginley]

Thanks Markus. We have considered opening an OSS note. For now will keep that as an option. In the meantime will continue to watch the system for other performance anomolies. We are at the current SAP supported SP3 release. Also getting ready to upgrade from ECC5 to ECC6 - that may help... or not.

Thanks again for you quick responses.

Laurie