Locks/Blocks in the coresuite database

PROBLEM:

I am experiencing a lot of locks in my MSSQL Database. The database performance is not up to the expected level my hardware is capable to provide.

SOLUTION:

A) Update to at least SAP 9.2 PL04 or 9.1 PL13 (See SAP note 2310023 for the details)

B) Create a SQL job that defragments the database and rebuilds indexes periodically.

Here's a description on how to defragment database indexes on a regular schedule:

Prerequisite:

• Have Microsoft SQL Server Management Studio connected
• SQL Server Agent installed
• SQL Server Agent Service is started

Steps to create a job:

1. Take second attached sql file and copy contents to new query window in SQL. Read through entire procedure and configure it to your liking as these changes are what will be used to determine when the indexes are rebuilt and tables are defragged.

2. Once you make the necessary changes to the SQL file, run the file to create the stored procedure. It is recommended you create this stored procedure in the coresuite database or a separate database created just for this job.

3. Create new job

4. Enter a name and a sysadmin sql user (category does not matter)

5. Under Steps(left side), add new step and set title to “reindex db” and command to
exec dba_indexDefrag_sp @executeSQL =1, @database = '<DBname1>,<DBname2>,<DBname3>,...'

Candidates to add here are: SAP Database, SBO-COMMON, coresuite and B1i Database (if it exists)

6. Press OK

7. Under Schedule (left side) add new Schedule. Set name to “reindex <DB name> step”. The schedule should be daily, as consecutive runs will take less time each day. Opposed to a single run once a month which could take hours to complete.

8. Press OK on the job schedule page to save it

9. Press OK once more to save the job.

Test the Job that it runs well:

10. Right click on the SQL Agent job (under “SQL Server Agent, Jobs) and press the “Start Job at step”. As you only have one step, this will automatically run the job.

**Attention**

Please note that the re-indexing job for the SAP database will take a lot of time, so please test the job for the coresuite database. Once the job starts you can close the running window.

Additionally, when you schedule these procedures to run, we recommend that you run these procedures off hours when little to no users are logged in as this can cause some slowness while the procedure runs in the background.

This procedure can also be applied to other databases.

Stored procedure download: