Because different customers use software in different ways, not every customer will benefit from exactly the same database-level indexes.

As a best practice, when users complain about database performance in a specific area of the application, or when performance tuning in general, I like to let the customer run with the application for a few days, and then use a query such as the one included below (adapted from Jeff Garbus at Soaring Eagle: https://mssqlperformance.blogspot.com):

–Source: Jeff Garbus from Soaring Eagle – Blog: https://mssqlperformance.blogspot.com/
–DRM – 2020-05-04 – Adapted by Darren to deal with mixed SQL Environments, include table name in index name, deal with table names containing spaces, etc.
–Use this SQL Script to identify indexes that if added could help improve performance.
–IMPORTANT: Do not BLINDLY implement the script recommendations.  For example, if the SAME TABLE is mentioned multiple times, then
–           it may be better to COMBINE the recommendations of multiple statements into a single one.
–           If you need help, Soaring Eagle. 
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY(‘Edition’))
DECLARE @bEnterpriseEdition bit
IF (LEFT(@Edition, 9) = ‘Enterpris’ OR LEFT(@Edition, 9) = ‘Developer’)
SET @bEnterpriseEdition = 1

SELECT
      ‘CREATE INDEX IX_’
            + REPLACE(obj.Name, ‘ ‘, ”) +’_’
            + replace(replace(replace (equality_columns, ‘[‘, ”),’]’,”),’, ‘,’_’)
            + ‘ ON ‘
            + sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + ‘.’ + QuoteName(obj.name)
            + ‘ (‘
            + equality_columns
             + CASE WHEN inequality_columns IS NULL
                   THEN ”
                   ELSE ‘,’ +  inequality_columns
               END
            + ‘)’
            + CASE WHEN included_columns IS NOT NULL
                   THEN ‘ INCLUDE (‘ + ISNULL(included_columns,”) + ‘) ‘
                   ELSE ”
               END
            + CASE WHEN @bEnterpriseEdition = 1
                   THEN ‘ WITH (online = ON)’
                    ELSE ”
              END
            + ‘ — ‘ + CONVERT (varchar, avg_user_impact) + ‘% anticipated impact’
      FROM sys.dm_db_missing_index_details mid
        JOIN sys.dm_db_missing_index_groups mig
            ON mid.index_handle = mig.index_handle
        JOIN sys.dm_db_missing_index_group_stats migs
             ON migs.group_handle = mig.index_group_handle
        JOIN sys.objects obj
            ON obj.object_id = mid.object_id
        JOIN sys.schemas sch
            ON obj.schema_id = sch.schema_id
      WHERE
        database_id = db_id()
        AND avg_user_impact > 60
        AND equality_columns IS NOT NULL
      ORDER BY
        obj.name
        , equality_columns
         , inequality_columns
        , included_columns

The output of this script should be reviewed carefully to see if the tables / recommendations make sense for the specific scenario.  Remember that there is a cost for implementing index changes that may outweigh the benefits.  The most common change that I make to the results from the above is to combine multiple recommendations for the same index into a single index with more elements in the INCLUDE() section.

For Blue Link employees reading this Blog, a version of the above is now available as IndexCreationRecommendationPROC().

After applying SQL Server 2016 SP2 Backups of Databases that had SQL Change Tracking enabled at the time SP2 was applied to the server no longer work.  Backups will fail with this message:

TITLE: Microsoft SQL Server Management Studio
——————————

Backup failed for Server ‘ServerName’.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.15700.28+((SSMS_Rel).160810-2135)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Failed to flush the commit table to disk in dbid 10 due to error 3601. Check the errorlog for more information. (Microsoft SQL Server, Error: 3999)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=3999&LinkId=20476

——————————
BUTTONS: OK
——————————

My Scenario: Server OS: Windows Server 2016 Datacenter

SQL Server 2016 Standard, SP2 Applied

Databases affected are those that had SQL Change Tracking enabled at the time of SQL Server 2016 SP2 being applied to the server, like this:

Ola Hallengren Backup Scripts Also Fail

If you happen to be using the Ola Hallengren DatabaseBackup SPROC, you may also notice that SPROC start to fail with error

Msg 207, Level 16, State 1, Line 1 Invalid Column name ‘modified_extent_page_count’.

That failure is happening because there is no column ‘modified_extent_page_count’ in the DatabaseName.sys.dm_db_file_space_usage views after the application of SQL 2016 SP2.

This is the specific line from the DatabaseBackup SPROC that fails:

SET @CurrentCommand07 = ‘SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.sys.dm_db_file_space_usage’

Solution: Remove SP2:Click Start, type “Update” and then choose “View Installed Updates”:

Scroll down to “Microsoft SQL Server 2016 (64-Bit)

Select “Service Pack 2 for SQL Server 2016 (KB4052908) (64-bit) 

Click Uninstall

If as in my case a restart is required, the setup will tell you that.  Restart the server then repeat this process and you’ll be able to click through the wizard to the end.

When the service pack removal is complete, backups will work once again.

Disabling change tracking does not solve this problem only uninstall of SP2 worked for me

I’m adding the steps to disable change tracking here for your easy reference, but in my experience this DOES NOT actually solve the problem.  Using a script like this you can disable change tracking:

SELECT ‘ALTER TABLE dbo.’ + QuoteName(t.name) + ‘ DISABLE CHANGE_TRACKING’ as tSQLToRun
FROM sys.change_tracking_tables ctt
JOIN sys.tables t
    ON t.object_id = ctt.object_id
JOIN sys.schemas s
    ON s.schema_id = t.schema_id
ORDER BY s.name, t.name

–Copy the results of the script above to a new query window and execute to turn SQL Change Tracking off on the tables for which it was enabled, and then run a command like this to disable change tracking on the database.

ALTER DATABASE DatabaseName SET CHANGE_TRACKING = OFF