I have to debug a performance issue on a SQL Server 2016 database. The query was pretty simple - a few temporary tables joined with SQL tables and aggregation. The amount of rows of involved in temporary tables was really small (several tables, hundreds of rows).
I have found that one of the tables PK has 12% index fragmentation. If the table was removed from the query it runs faster. I rebuilt the index and everything was fine.
I believe there must be something wrong with the settings of the SQL Server 2016 instance, because the query is running perfectly fine on SQL Server 2014. The only difference I have found in the settings was in the maintenance plans - the pictures below shows the plans:
SQL Server 2016
SQL Server 2014
I guess there is something not set correctly in the Index Stats Options, which is causing statistics not to be recalculated. Can anyone tell me if am I am right (as I am not DBA) or say if the plan looks OK, so I can search for the issue in another place?