Excluding indexes from Statistic Updates

[former_member254066]

Dear SAP Experts,

I would like to know if it possible to exclude some objects, especially indexes from update statistics process in MSSQL Server?

On Oracle Database I can use DB20, but in MS-SQL this transaction is not supported.

[Sriram2009]

Hi

I am not sure, Is this any reason why you want to excluding the Indexes statistics? and which version MS Sql server ?

BR

SS

[former_member254066]

Dear all,

I know that changing default settings for update statistics  on MSSQL Server it is not recommended, but we need to change this configuration, because of lack in performance of the SQL query.

The query used a wrong index of the table and we suppose that is caused by wrong update statistic procedure.

The SQL version is MSSQL 2012.

[Rudi_Wiesmayr]

Servus Jaroslaw!

Outdatet statistics are very rarely the true reason for slow queries. But there are cases.

What makes you think you have such a case?

Do you have columns in the WHERE clause where the values are "counting up"?

But you asked to EXCLUDE indexes from updating ... You NEVER can make things worse by updating statistics. The only chance you have is that you wake up some sort of "intermittent" problems showing up as a pattern I call "Good-Plan-Bad-Plan" ...

Dont hesitate to ask further + Good Luck

Rudi

[xymanuel]

HI Jaroslaw,

this is definately the wrong way.

First, delete the query plan in dbacockpit, to ensure that the SQL Server will generate it again.

After this, wait again until the statement is called, to analyse the query plan.

Check if traceflags are set: 617,1117,1118,2371

i'm not 100%sure but i guess the 2371 is for a generic calculation of auto update statistics, depending on the size of the table and the changed data. So this is an imporant one if the DB is using the correct index, but after time choosing the wrong.

Check the fragmentation of the clusted index in dbacockpit. If it is higher then 40-50%, rebuild the indexes. I solved a problem that the query optimizer is choosing the wrong index doing this.

If you problem occours in an non standard program (Y* Z*), you maybe can work with database hints in abap code (but these are very rare cases). If this is a standard program, check if there is a note.

I had a problem, where a note added a database hint to solve a problem like this. Just search for the reportname in sapsupport.

Maybe you can show us the indexes of the table, the abap statement and the query plan.

Regards

Manuel

Message was edited by: Manuel Herr

[luisdarui]

There are some tables that SAP on purpose disable the Automatic Statistics: VBDATA, VBMOD and VBHDR.

I know that for those tables it makes sense. I don't know which table, index or query you're getting problems, but if you want to go ahead, read the following page in Books Online:

sp_autostats (Transact-SQL)

This KB article: https://support.microsoft.com/en-us/kb/2754171

This Blog post: http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-...

This blog post http://blogs.msdn.com/b/saponsqlserver/archive/2015/04/21/worst-practices-for-maintaining-sql-server...

Check point 14 (I suggest to read the entire post, it is a masterpiece!).

Regards,

Luis Darui

[Mofizur]

Hi,

Please go through below Note 1744217 - MSSQL: Improving the database performance

2. Statistics
   
   If you follow point 7 SQL Server itself will take care of automatically updating statistics. Please do not schedule any additional statistics updates unless SAP explicitly recommends you to. Besides the automatic statistics update, please implement SAP Note 1558087.

Thanks,

Mofizur