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>,...'

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.