Is it possible to create own index on table?

[Former Member]

Hi,

is it allowed from SAP to create own index on system or user table? Is allowed to make dbcc dbreindex on table?

thanks

Petr

[Former Member · Accepted solution]

I don't even neeed to look to know that the offical SAP answer is No.

Have you checked the avg_fragmentation_in_percent, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats?

These stats tell you if your index needs reorganizing or defragging.

1) avg_fragmentation_in_percent > 15 is a candidate for a rebuild

2) avg_page_space_used_in_percent < 60 is a candidate for a rebuild

I would create a SQL maintenance plan with a rebuild all indexes job before I thought about adding a new index.

eg.

select index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats

(db_id('Your_database'), object_id('table'), null, null, 'detailed') where index_id <> 0

to rebuild a single index

alter index all on <tablename>

rebuild with (fillfactor = 90)

unless you have enterprise version of sql you will want to do this after hours.

[Former Member · Accepted solution]

Hi John,

thanks for reply. I expected, that the answer is NO

What I need to do is, that I add my user field to table and this field will be the joining field in query and because the field is varchar and table has a lot of records, the joining takes too long.

Petr