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

enter image description here

SQL Server 2014

enter image description here

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?

  • I have to note that the SQL 2016 maintenance task doesn't seem to be configured, so it's hard to know if any index maintenance is actually happening there. Agree with @Kin 's answer re, using Ola Hallengren scripts, and importance of stats updates. – RDFozz Feb 5 '18 at 18:52

This is longer than a comment .. so putting this as answer ...

So, I have rebuild the index and everything was fine.

Rebuilding an index will update stats on the columns associated with that index.

There can be a lot of variables which might be different between 2 servers (2014 vs 2016)

  • sys.configurations settings
  • compatiblity level of database
  • Any custom trace flag enabled
  • max memory setting
  • cost threshold of parallelism setting
  • max dop setting

You can run Best practice analyzer script from MSSQLTiger team or just run sp_Blitz from Brent Ozar's team.

As a side note: Use Ola's Index maintenance solution ... much flexible and well tested by a lot of organizations.

From your screenshot, it seems that you are setting the page fullness (FILLFACTOR) to 80% i.e. leave 20% free. You should monitor fragmentation (in your case 12% is nothing + you need to consider how many pages are in the index. I would suggest you to start looking into indexes with 1000+ pages). I would doubt that this will cause a major performance problem.

| improve this answer | |
  • Yes, I know rebuilding the PK rebuilds the NI and updates statistics. That's why I think there is something wrong with the DB maintenance plan. Everything you mention is the same, except the trace flags - they must be set with their default values on both instance, but I will check them, too. – gotqn Dec 4 '16 at 7:27
  • What trace flags are different? – Kin Shah Dec 4 '16 at 18:04
  • DBCC TRACESTATUS returns nothing on both instances, so I guess everything is the same as set up, instead these options in the maintenance plan. – gotqn Dec 5 '16 at 9:59
  • I mean except the diff in the maintenance plans. – gotqn Dec 5 '16 at 12:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.