Very poor performance after upgrade to MSSQL 2008 buz of Update_Tabstats

[former_member211576]

Hi experts,

Does any encounter after upgrading to MSSQL 2008, the system is really slow/hang when SAP_<sid>_<SID>_Update_Tabstats is running. I can disable dbcc updateusage per 1178916 but I think there must be many people upgrade to MSSQL 208, but why does only my system has this issue.

My sap_tabstats is clear out after upgrade, and it always hang when dbcc updateusage is running? Do I run something wrong?

[former_member438568 · Accepted solution]

Hi Dennis,

Good that you are running at 10.0. It is a bug in the warning and it should be fixed in SP17 of 7.0 basis. See note 1224738.

[former_member211576]

Hi Scbhadip/Dale,

Thanks for you info.

After upgrade, the database compatibility is set to '10.0' by MSSQL 2008. I have asked Microsft(Basis) consultant, he say the warning message in DBACOCKPIT("The compability level is not 90" is probably a bug and it can be fixed after apply SP16). I run with Database compatibility: 10.0. Is there something wrong? Or I should change it to 9.0 per DBACOCKPIT message?

[former_member438568]

Hi Dennis,

Please do not change the compatibility level. That is a risky thing to do and could have unforeseen consequences. The records in your sap_tabstats were rendered useless when in the course of your upgrade the schema was changed from dbo to tcp. We will write a note that tells customers that if you rename your schema, you must update table sap_tabstats. I'll update your OSS case.

Regards,

Dale Adams SQL Server port dev support

[former_member211576]

Hi Subhadip,

Yes. I use ECC 6 SR2 SP15. kernel/dbsl 190. sp_configure use default setting. But there is a warning in 'Setup Check':The compability level is not 90. I run 10.0, is it no correct?

[Former Member]

Hi Dennis,

The version for SQL Server 2005 is 9.00 and for 2008 the version is 10.0. So if you run your SQL Server 2008 with compatibility level 9.0 it will behave as a SQL Server 2005. You can do this and check whether your problem has been resolved or not.

Rgds,

SK

[Former Member]

Did you configure SQL Server 2008 as per SAP note1237682,1152240,1144459?

Also upgrade the dbsl to the latest patch level.

Regards,

SK