Description

Executing queries against the Links table might result in increased CPU/DTU consumption. In this case, operations that require access to the Link Database tend to get slower. The article provides recommendations on reducing the query execution time for the Link Database by creating the SQL indexes on the Links table.

Solution

To reduce the query execution time for the Link Database:

1. Determine the name of the database that is used to store the Links table. By default, it is determined by the defaultLinkDatabaseConnectionStringName variable in the configuration. The script that follows later in this procedure must be run against the database that the Links table is stored in.
2. Create a backup of the database. This step is optional but it is considered best practice when making changes to the database schema.
   Note: The Link Database can be rebuilt from scratch. To rebuild the Link Database, proceed as follows:
   
   1. On the Sitecore Launchpad, click Control Panel.
   2. On the Database tab, click Rebuild link databases and select the check boxes for all databases.
   3. Click Rebuild.
3. Change the type of the TargetPath column on the Links table of the database:

   ALTER TABLE [Links] ALTER COLUMN TargetPath NVARCHAR(MAX) NOT NULL

4. Create two new SQL indexes on the Links table of the database:
   

   • CREATE NONCLUSTERED INDEX [ndxSourceItemIdSourceDatabase] ON [dbo].[Links]
     (
     [SourceItemID] ASC,
     [SourceDatabase] ASC
     )
     INCLUDE ([TargetDatabase],[TargetItemId],[SourceLanguage],[SourceVersion],[SourceFieldID],[TargetLanguage],[TargetVersion],[TargetPath])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

   • CREATE NONCLUSTERED INDEX [ndxTargetItemIdTargetDatabase] ON [dbo].[Links]
     (
     [TargetItemId] ASC,
     [TargetDatabase] ASC
     )
     INCLUDE ([SourceDatabase],[SourceItemID],[SourceLanguage],[SourceVersion],[SourceFieldID],[TargetLanguage],[TargetVersion],[TargetPath])
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     GO

5. Delete the ndxSourceItemID and ndxTargetItemID SQL indexes on the Links table of the database.

Note: The previous steps help to reduce query execution time in most environments. However, we recommend that the changes are tested in a pre-production environment before rolling out to production. It is necessary to ensure that the cost of maintaining the indexes (updating the indexes when data changes) does not outweigh the boost in query performance.