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?

closed as off-topic by LowlyDBA, RDFozz, Erik Darling, Mr.Brownstone, McNets Feb 6 '18 at 8:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RDFozz, Erik Darling, Mr.Brownstone, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.

  • 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.