Hi

We encountered decreasing performance on some of our sqla 16 dbs lately. During querying we had a huge disk load.

So we started with rebuilding the database with the dbunload utility. This changed the size of the db files, but the performance and disk load did not change much.

Then we executed the sa_index_density system procedure, what gave us a lot of indexes with low density <0.7

According to documentation you could either: REORGANIZE TABLE testtable PRIMARY KEY or ALTER INDEX PRIMARY KEY ON testtable REBUILD;

What ist the difference between these two statements? When should we prefer one over the other? Should these queries be executed periodically?

Thanks

asked 22 Dec '18, 02:49

loonyx's gravatar image

loonyx
11112
accept rate: 0%

are all queries slow, or only some of them? E.g. if you give us the execution plan with the statistics, will it be possible? Some gurus say here that the reorganization might not help. Others can suggest you to drop the statistics, but again, without the execution plan nobody knows what is going wrong.

(22 Dec '18, 04:40) Vlad

This is a general question, as the documentation from SAP concerning this topic is too shallow from my point of view.

We executed the ALTER INDEX PRIMARY KEY ON testtable REBUILD and performance was way better. This query is quite slow and it fixed the index density on some, but not all indexes.

We executed the REORGANIZE TABLE testtable PRIMARY KEY and performance was much better. This query is really fast and it fixed the index density on most, but not all indexes.

For me I do not really understand the difference between the two queries.

Does anyone execute such queries as db maintenance on a regular basis?

In our case, we have a lot of delete and inserts in some tables. Maybe this is something that makes the indexes slow.

(22 Dec '18, 05:30) loonyx
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×32
×28
×11

question asked: 22 Dec '18, 02:49

question was seen: 1,294 times

last updated: 22 Dec '18, 05:30