Problem with Index usage (Table DRAW)

[Former Member]

Hello,

Poor system performance, because index draw~1 is not used by all queries

in MSSQL database

We can observe poor system performance on system PRD, while querying

table DRAW in customer program. Analysis with ST05 shows index DRAW~1 is

not always used by query optimizer, although the sql statement is always

the same (Please see screenshot 1 – executing with index). Actualizing

the index with ‘update statistics’ normally fix the issue and for the

next time all queries are using the index. After a certain time period

the index is not used anymore.

In lt_documentkeys_drad are approx. 17.000 entries or more possible.

For saving an document out of our Programm we call SAP BAPIS.

We have already rebuild the index DRAW~1 and update statistics in short

times (every 15 min). Even in this short time period the behaviour takes

place.

Why is index DRAW~1 not used by all queries? How can we ensure that all

statements are using index DRAW~1?

Checked SAP notes:

1016001 - SQL error 1844 for "CREATE INDEX WITH (ONLINE=ON)"

1660220 Microsoft SQL Server: Common misconceptions

1702408 Configuration Parameters for SQL Server 2012

1712785 Support for Microsoft SQL Server 2012

1744217 MSSQL: Improving the database performance

THX and Best Regards

Daniel

[Sriram2009]

Hi Daniel.

If you know the transaction code or report query the table DRAW, enable the ST12 trace to find the right index fields

Refer the ST12 trace usage -

Regards

SS

[luisdarui]

Hi Daniel,

There can be many reasons that the optimizer is not choosing such index. I can't find a document attached to find out what you were trying to explain, but in your case, reason can be the case of uneven data distribution in one of the columns in the where clause of the query.

Please take your time to read the following blog post:

Data distribution and SQL Plans | Running SAP Applications on the Microsoft Platform

Best regards,

Luis Darui

[luisdarui]

Hi Daniel,

Any updates?

Best regards,

Luis

[Former Member]

Hi Luis,

thank you for your post.

My colleague opened a SAP Support Ticket. And the answer was a workarround. Our developers hard coded the use of the index.

I think this is not the best solution, but for now the problem is fixed.

Best regards.

Daniel

[luisdarui]

Hi Daniel,

I agree it is not the best option. Curently I found the incident and the query is FAE (for all entries) and the captured query plan shows a Clustered Index Scan on the PK DRAW~0.

I would not stick with forcing this index and I would rather continue working on this incident with SAP.

Best Regards,

Luis