Re-organizing Indexes for Partitioned SQL Databases

If a Partitioned Microsoft SQL database is used as the eG backend, then make sure you re-build the database indexes by following the broad steps below:

1. Identify table sizes and exclude very large tables

2. Check if a table Is partitioned

3. Generate reorganize statements for Partitioned tables

4. Generate reorganize statements for Non-partitioned tables

Each of the steps is discussed elaborately in the section below.

Identify Large Tables

Login to the eG backend and run the following query, so that all the tables in the eG database are listed along with their size.

SELECT

t.name AS TableName,

SUM(ps.used_page_count) * 8 / 1024.0 AS UsedtableSizeMB,

SUM(ps.reserved_page_count) * 8 / 1024.0 AS tableSizeMB,

SUM(row_count) as row_count

FROM

sys.dm_db_partition_stats ps

INNER JOIN

sys.tables t ON ps.object_id = t.object_id

WHERE

ps.index_id IN (0, 1)

GROUP BY

t.name

ORDER BY

tableSizeMB DESC;

From the query results, identify the largest tables based on size and exclude those tables from reorganize operations, as they can take significant time to complete. Make note of the other table names, as they are candidates for reorganization.

 

Check if a Table is Partitioned

Before performing index maintenance, you must determine whether the table is partitioned or not.

To identify the partitioned tables, run the following query:

select

object_name(i.object_id) as [table_name],

i.name as [index_name],

s.name as [partition_scheme],

i.type_desc as [Index_type]

from sys.indexes i

left join sys.partition_schemes s on i.data_space_id = s.data_space_id

where i.index_id <>0 and i.object_id >255 and s.name is not null;

 

To identify non-partitioned tables, run the following query:

select

object_name(i.object_id) as [table_name],

i.name as [index_name],

s.name as [partition_scheme],

i.type_desc as [Index_type]

from sys.indexes i

left join sys.partition_schemes s on i.data_space_id = s.data_space_id

where i.index_id <>0 and i.object_id >255 and s.name is null;

Generate Partition-Wise Reorganize Statements

For partitioned tables, indexes must be reorganized per partition. Use the following script to generate the reorganize statements for any partitioned table you identified earlier:

select

'alter index '+i.name+' on '+object_name(p.object_id)+' reorganize partition='+cast(p.partition_number as varchar)+';' as statement

from sys.indexes i

join sys.partitions p on i.object_id=p.object_id and i.index_id=p.index_id

left join sys.tables t on i.object_id=t.object_id

where t.name in ('<enter_table_name>', '<enter_table_name>')

and p.rows >0

order by t.name, i.name;

Before executing the script, make sure you replace the <enter_table_name> placeholders in the script above with the exact names of the partitioned tables.

Once the statements are generated, copy them, and execute them individually in a new SQL window. Remember to rebuild / reorganize the Primary Key index before processing any non-clustered indexes.

Generate Reorganize Statements for NON-Partitioned Tables

For non-partitioned tables, index reorganize does not require partition numbers.

Use the script below to generate reorganize statements for non-partitioned tables:

select

'alter index '+i.name+' on '+object_name(i.object_id)+' reorganize ;' as statement

from sys.indexes i

left join sys.tables t on i.object_id=t.object_id

where i.type <>0 and t.name in ('<enter_table_name>', '<enter_table_name>') -- update the table name

order by t.name, i.name ;

Before executing the script, make sure you replace the <enter_table_name> placeholders in the script above with the exact names of the non-partitioned tables.