Description
Application performance can suffer greatly because the database is not optimized for the current data volume.  It is very common for a new install to experience performance problems as soon as a significant volume of data is added to the system.  The solution is to make sure that the database is optimized by implementing periodic index and statistics maintenance.

The steps below make use of the 2 attached SQL scripts, named 40 Index Fragmentation System Health.sql and 50ReindexAsNecessaryWoutputMsg_v2.0.sql.  Download these scripts to be run in MS SQL Management Studio.

The attachments are found at the bottom of this Knowledgebase article.

Resolution
Very often, a new install is not set up with regular index and statistics maintenance.  Without this maintenance, the database server will not make efficient choices for query plans.  SQL Server, like all modern SQL databases, uses a cost-based optimizer that relies on statistics regarding data volume and key value distribution to choose an efficient query plan.  The SQL statement specifies what logical operations to perform, and the database optimizer writes a program to perform the data operation.   When there is very little data in the system, many different query plans will have acceptable performance. Once data volume increases, it becomes more important to ensure that an efficient plan is chosen.  This can be done by rebuilding the indexes and recalculating index statistics, which will cause the optimizer to re-compute the query plan the next time the query runs.  The query plan that results from the updated database statistics will be better.  SQL Server versions 2008 R2+ are better at automatically recalculating statistics periodically.  But indexes can be very severely fragmented if they have not been rebuilt since adding data.   So the best plan is to periodically re-index.  Here is a link to background information on this topic.

http://msdn.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

After this initial reorganization, some indexes may become fragmented.  The Footprints 12 definition tables used for defining the structure of Workspace Items and Address books and the CMDB are frequently used and will benefit from re-indexing as often as daily or after several successive publishes.

Detection of Fragmented tables
The script 40 Index Fragmentation System Health can be used to look at overall fragmentation of the FootPrints database and see which indexes are fragmented and what parts of the Footprints system are affected.  Use this script if you believe that you have implemented a reindex plan, or if you are examining a FootPrints database and want to know what the backup status is.

Implementing Regular Re-indexing
If you are the owner of a FootPrints database and you want to implement regular re-indexing, you should coordinate with your DBA.  There are many good scripts and tools available for this purpose.   This article will help you establish a re-indexing plan if you do not have one and want a quick and easy solution.
BMC provides a stored procedure that will detect the level of fragmentation and re-index only those tables that show fragmentation.  The Rebuild command option is used to fully rebuild the indexes, and the online option is used by default so that indexes can be used while the SQL Server is in use.  If your SQL Server edition does not support online re-indexing, and the parameter for online is true, the re-index command will failover to run without the online option. 

Steps:

1. Log into SQL Management Studio with a domain account that has SQL Server administration access.
2. Run the script 50ReindexAsNecessaryWoutputMsg_v2.0 one time in SQL Server Management Studio.  This will create the needed views and stored procedures, and it will perform the one time execution.
3. Set up a database maintenance task to regularly run the re-index program. 

Enabling the SQL Server Agent
For security purposes, SQL Server must be enabled to use the Agent to schedule maintenance tasks.

1. Use the Surface Area Configuration tool and make sure that Agent XPs are allowed.  Or enable Agent XPs using code.  https://msdn.microsoft.com/en-us/library/ms178127.aspx
2. Set the SQL Server Agent startup type to automatic in Control Panel..Services.
3. Set the Agent to Autorestart.  https://msdn.microsoft.com/en-us/library/ms178130(v=sql.120).aspx

Setting up the maintenance task​
You can use Microsoft’s maintenance plan wizard for creating a reindex plan.  We recommend creating a Maintenance plan to run the stored procedure provided because it will avoid unnecessary reindexing and run faster and with less unavailability of the system.  This example shows dialogs from SQL Server 2014.

1. Right-mouse New Maintenance Plan.

2. Drag and Drop 2 Maintenance Tasks to the surface as shown.  Connect them as shown.

3. Double-Click and edit the T-SQL task as shown.  The parameters are optional, these are the defaults. 
   • The minimum percentage of fragmentation to cause a rebuild.  Some small tables will not improve beyond a set amount.  You can avoid churn by setting this higher.  Run repeatedly from the command window and check messages to see which tables don’t improve.
   • IsOnline should be set to true, unless you know you are running when the FP application is down or idly used.  It should be set to 0 if you know that online indexing is not possible, to avoid resubmitting each command after failure.  Check the messages on the manual run from the command window.
   • The minimum number of pages to reindex.  This is set very low to catch some small tables where indexing and redoing the query plan is important.

• You must have run the script that creates the stored procedure in the FootPrints Database
• You need to set the database context for the procedure you wish to run. Note the 3 part naming above:  database_name.schema_name.procedure_name.
• You can reindex additional FootPrints databases.  It is better to use additional steps so any failures are more clear in the history.

1. This isnt a backup plan, so we don’t have backup history.  Choose a retention period that you like.

5. Save
6. Manually run when FP is down or idle to test.

7. Set the schedule for regular execution.

8. Save
9. Periodically, check the job history for execution.

10. If there are job failures, make sure that the stored procedure does run without errors.  Check the execution account.  Otherwise, rely on Microsoft documentation and troubleshooting to figure out why the job is not executing.