Reorganize indexes from SAP database

[Former Member]

Hi Gurus, I would know if is there any possibility for reorganize every indexes of the sap database under mssql 2005.

Maybe with the instruction alter index ... reorganize... is possible but I don't know if this method is supported by sap.

By now my system (Netweaver ECC 6) is on line for 3 months (with the new mssql db) and until now the performance is good enough but I'm little bit worried about the future because by now the database is only 180GB but it is growing around 4 GB every month.

Should I have some kind of process for the maintenance of the database indexes?

Best regards and thanks in advance

[Former Member · Accepted solution]

Hi,

have a look at [this article|http://technet.microsoft.com/de-de/library/cc966447%28en-us%29.aspx]. Section 9 gives some ideas on how to handle alter index...reorganize.

Sven

[Former Member · Accepted solution]

Thanks a lot, so now I must find the most important indexes which will be defrag with "DBCC INDEXDEFRAG".

[Former Member · Accepted solution]

DBCC INDEXDEFRAG is depreciated from SQL Server 2005 on. ALTER INDEX ... REORGANIZE does the same. Check [this article|http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx] in Books Online and look for the REORGANIZE option.

Sven

[Former Member · Accepted solution]

Thanks again, I was reading about this change yet.

[Former Member · Accepted solution]

Hi again, I found in the books online of SQL Server the next script, I will test in our development environment, and if everything is ok I will chek the time and space needed in our Quality environment.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

[Former Member · Accepted solution]

You can also have a look at transaction ST10 (table call statistics). This gives you an overview about those tables that are called/changed most often in your system. In combination with the index fragmentation data you get a good idea which tables might benefit most from reorganization.

Sven

[Former Member]

Hi Juan,

actually, SAP recommends NOT to run any reorganization or rebuild on a regular basis for SQL Server databases in Netweaver environment - I know that this is a common maintenance task for other RDBMS like Oracle... but I've analyzed lots and lots of performance issues on SQL Server databases within the last 8 years and reorganization or rebuild never was the solution to any of them. Instead they're usually caused by all kinds of other problems like bad execution plans, bad I/O times, blocking locks, uneven data distribution, missing indexes, suboptimal configuration, paging, .... and so on.

If you really want to reduce the amount of space required by the database I instead recommend you to upgrade to SQL Server 2008 (R2) so you can seize page compression. Of course it also depends on the data in the database how much you can cut down the size of the whole database by compressing it - but I must say I've seen astonishing results

You can even compress most database objects online....

Have a look at

SAP Note 1488135 - Database compression for SQL Server

SAP KBA 1744217 - Basic requirements to improve the performance of a SQL Server database

Regards,

Beate

[Former Member]

While I may agree with your findings, saying index rebuild or reorganization is NEVER the solution to any of the performance problems is a blanket statement that may be taken by anybody managing the database without understanding the WHY. Records are stored in tables which are stored internally in data pages, index pages(if we have both clustered and non-clustered indexes) and heaps (if we don`t have any clustered indexes.) Since we constantly update our records - performing inserts, updates and deletes - the pages become full and, depending on the activity on the records, get disorganized. With this in mind, pages will get fragmented. This causes SQL Server to take some time to look for records in your query. Imagine trying to find a specific business card in a stack of disorganized cards with no pointers as to where the specific card is located, this is how SQL Server accesses the records. Maintaining the indexes by rebuilding or reorganizing, depending on the fragmentation level and the number of pages affected by the index, definitely helps SQL Server search for the record faster. Well maintained index assist in addressing the following

• bad execution plans since execution plans are generated based on the cost it takes to run a query
• bad I/O times since SQL Server will have to perform a lot of random I/O access caused by fragmented indexes
• excessive blocking since SQL Server relies on how fast it can acquire and release a lock on a resource
• uneven data distribution because statistics are automatically updated when an index is rebuild (reorganizing indexes does not update the statistics), etc.

In fact, a better way to approach this is to run SQL Profiler to trace a specific query before index maintenance and after index maintenance to see how long the query took and how much I/O was required to run the query. You can find more information on the top 10 best practices for SQL Server for SAP here

http://technet.microsoft.com/en-us/library/cc966447.aspx

I hope this clarifies things a bit as far as the impact of not maintaining indexes.

[xymanuel]

Hi Everybody,

i recommend to follow:

http://blogs.msdn.com/b/saponsqlserver/

which is the SQL Develop Team Blog. There you will find the answers to lot of SAP on SQL Server related questions. Also the question to how to rebuild indexes.

My short opinion and experiance:

If you dont know how to analyse what is causing the performance problem, do not blindly rebuild the index.

If you know what you are doing, DO IT. I resolved many performance problems related to heavy defragmented indexes.

http://scn.sap.com/docs/DOC-1006

Regards

Manuel

[Former Member]

You can reorganize indexes unconditionally with following ABAP-Code. I use it for my customer Z-tables.

First i tried to do it with 'EXEC SQL ...'-Form but i always got a database-error or a ABAP-dump. Using the class 'cl_sql_statement' instead it worked.

REPORT  z_index_rebuild.

DATA:          sql_index           TYPE string,
               statement           TYPE string,

               sql TYPE REF TO cl_sql_statement,
               err TYPE REF TO cx_sql_exception.


CREATE OBJECT sql.

EXEC SQL PERFORMING sql_exec_statement.
  select index_name into :sql_index from dba_indexes
    where
      table_owner =    'SAPR3' and
      table_name  like 'Z%'
    order by
      index_name
ENDEXEC.

*&---------------------------------------------------------------------*
*&      Form  sql_exec_statement
*&---------------------------------------------------------------------*
FORM sql_exec_statement.

  CLEAR statement.
  CONCATENATE 'alter index "SAPR3"."' sql_index '" rebuild' INTO statement.
  CONDENSE statement.

  TRY.
      CALL METHOD sql->execute_ddl
        EXPORTING
          statement = statement.
  ENDTRY.

ENDFORM.