SQL Server 2016 Enterprise poor performance

Question

Sorry to be long, but I want to give you as much information as possible so that might be helpful to the analysis.

I know there are several posts with similar issues, however, I have already followed these various posts and other information available on the web, but the problem remains.

I have a serious performance problem in SQL Server that is driving users crazy. This problem drags on for several years, and until the end of 2016 was managed by another entity and from 2017 came to be managed by me.

In the middle of 2017, I was able to resolve the problem by following the indexing hints indicated by Microsoft SQL Server 2012 Performance Dashboard Reports. The effect was immediate, it sounded like magic. The processor that was in the last days almost always in the 100%, became super serene and the feedback of the users was resounding. Even our ERP technician was delighted, as it usually took 20 minutes to get certain listings and finally he could do it in seconds.

Over time, however, it slowly began to worsen. I avoided creating more indexes, for fear that too many indexes would worsen performance. But at some point I had to erase the ones that had no use and create the new ones that Performance Dashboard suggests to me. But no impact.

The slowness felt is essentially when saving and consulting, in the ERP.

I have a Windows Server 2012 R2 dedicated to SQL Server 2016 Enterprise (64-bit) with the following configuration:

• CPU: Intel Xeon CPU E5-2650 v3 @ 2.30GHz
• Memory: 84 GB
• In terms of storage, the server has a volume dedicated to the operating system, another dedicated to the data and another dedicated to the logs.
• 17 databases
• Users:
  • In the biggest DB are connected more or less 113 users concurrent
  • In another there are about 9 users
  • In two of them are 3 + 3
  • The rest have only 1 user each
  • We have a web that also writes for the larger database, but where the use is much less regular, and that should have about 20 users.
• Size of DBs:
  • The largest of the databases has 290 GB
  • The second largest has 100GB
  • The third largest has 20 GB
  • The fourth 14 GB
  • The remainder are just over 3 GB each

This is the production instance, but we also have an instance of development that I believe can be disregarded for this purpose, because most of the time I'm the only connecting there, but this problem happens constantly, even when I'm not connected.

The processor is almost always like this:

We have routines that run during the night (not problematic) and some that run during the day.

Users connect through Remote Desktop to other machines that are configured by ODBC 32 to access SQL Server.

The Datacenter where the servers are located has 100/100 Mbps, as well as where I am. Most sites are linked by MPLS and others by IPSec (from FO to 4G). The provider made many analysis and the circuit is ok.

Cache Hit Ratio is 99% (both User Requests and User Sessions)

The waits look like this:

I have already collected data with Perfmon and I have the results if it help with your analysis - personally, I did not get any conclusions from the analysis.

I count on your support in solving this problem, being available to provide the information that you consider necessary for the resolution.

Thank you very much.

Here's the sp_blitz markdown (I replaced companies names with pseudonyms):

Priority 1: Reliability:

• Last good DBCC CHECKDB over 2 weeks old

  • master

  • model - Last successful CHECKDB: 2018-02-07 15:04:26.560

  • msdb - Last successful CHECKDB: 2018-02-07 15:04:27.740

Priority 10: Performance:

• CPU w/Odd Number of Cores

  • Node 0 has 5 cores assigned to it. This is a really bad NUMA configuration.

  • Node 1 has 5 cores assigned to it. This is a really bad NUMA configuration.

Priority 20: File Configuration:

• TempDB on C Drive tempdb - The tempdb database has files on the C drive. TempDB frequently grows unpredictably, putting your server at risk of running out of C drive space and crashing hard. C is also often much slower than other drives, so performance may be suffering.

Priority 50: Reliability:

• Errors Logged Recently in the Default Trace
  • master - 2018-03-07 08:43:11.72 Logon Error: 17892, Severity: 20, State: 1. 2018-03-07 08:43:11.72 Logon Logon failed for login 'example_user' due to trigger execution. [CLIENT: IPADDR]

(note: many error like this because of an enabled trigger that limit user sessions - for ERP licensing usage control)

• Page Verification Not Optimal

  • DATABASE_A - Database [DATABASE_A] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_B - Database [DATABASE_B] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_C - Database [DATABASE_C] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_D - Database [DATABASE_D] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_E - Database [DATABASE_E] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_F - Database [DATABASE_F] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_G - Database [DATABASE_G] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_H - Database [DATABASE_H] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_I - Database [DATABASE_I] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_Z - Database [DATABASE_Z] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_K - Database [DATABASE_K] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_J - Database [DATABASE_J] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_L - Database [DATABASE_L] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_M - Database [DATABASE_M] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_O - Database [DATABASE_O] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_P - Database [DATABASE_P] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_Q - Database [DATABASE_Q] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_R - Database [DATABASE_R] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_S - Database [DATABASE_S] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_T - Database [DATABASE_T] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_U - Database [DATABASE_U] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_V - Database [DATABASE_V] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

  • DATABASE_X - Database [DATABASE_X] has NONE for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

• Remote DAC Disabled - Remote access to the Dedicated Admin Connection (DAC) is not enabled. The DAC can make remote troubleshooting much easier when SQL Server is unresponsive.

Priority 50: Server Info:

• Instant File Initialization Not Enabled - Consider enabling IFI for faster restores and data file growths.

Priority 100: Performance:

• Fill Factor Changed

  • DATABASE_A - The [DATABASE_A] database has 417 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_B - The [DATABASE_B] database has 318 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_C - The [DATABASE_C] database has 346 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_D - The [DATABASE_D] database has 663 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_E - The [DATABASE_E] database has 335 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_F - The [DATABASE_F] database has 1705 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_G - The [DATABASE_G] database has 671 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_H - The [DATABASE_H] database has 2364 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_I - The [DATABASE_I] database has 1658 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_Z - The [DATABASE_Z] database has 673 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_K - The [DATABASE_K] database has 312 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_J - The [DATABASE_J] database has 864 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_L - The [DATABASE_L] database has 1170 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_M - The [DATABASE_M] database has 382 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_O - The [DATABASE_O] database has 356 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • msdb - The [msdb] database has 8 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_P - The [DATABASE_P] database has 291 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_Q - The [DATABASE_Q] database has 343 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_R - The [DATABASE_R] database has 2048 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_S - The [DATABASE_S] database has 325 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_T - The [DATABASE_T] database has 322 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_U - The [DATABASE_U] database has 351 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_V - The [DATABASE_V] database has 312 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • DATABASE_X - The [DATABASE_X] database has 352 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

  • tempdb - The [tempdb] database has 2 objects with fill factor = 70%. This can cause memory and storage performance problems, but may also prevent page splits.

• Many Plans for One Query - 20763 plans are present for a single query in the plan cache - meaning we probably have parameterization issues.

• Server Triggers Enabled - Server Trigger [connection_limit_trigger] is enabled. Make sure you understand what that trigger is doing - the less work it does, the better.

• Stored Procedure WITH RECOMPILE

  • master - [master].[dbo].[sp_AllNightLog] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.

  • master - [master].[dbo].[sp_AllNightLog_Setup] has WITH RECOMPILE in the stored procedure code, which may cause increased CPU usage due to constant recompiles of the code.

Priority 110: Performance:

• Active Tables Without Clustered Indexes

  • DATABASE_A - The [DATABASE_A] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_B - The [DATABASE_B] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_C - The [DATABASE_C] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_E - The [DATABASE_E] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_F - The [DATABASE_F] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_H - The [DATABASE_H] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_I - The [DATABASE_I] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_K - The [DATABASE_K] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_O - The [DATABASE_O] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_Q - The [DATABASE_Q] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_S - The [DATABASE_S] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_T - The [DATABASE_T] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_U - The [DATABASE_U] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_V - The [DATABASE_V] database has heaps - tables without a clustered index - that are being actively queried.

  • DATABASE_X - The [DATABASE_X] database has heaps - tables without a clustered index - that are being actively queried.

Priority 150: Performance:

(Note: Nany advices here, but I could not include them because of the limitation of characters. If there is another way to share, please indicate.)

Answer 1

You gave us a long (and very detailed) question. Now you have to deal with a long answer. ;)

There are several things I would suggest to change on your server. But lets start with the most pressing issue.

One time emergency measures:

The fact that the performance was satisfying after deploying the indexes on your system and the slowly degrading perfomance is a very strong hint that you need to start maintaining your statistics and (to a lesser degree) take care of index framentation.

As an emergency measure I would suggest an one time manual stats update on all of your databases. You can get the nessecary TSQL by executing this script:

DECLARE @SQL VARCHAR(1000)  
DECLARE @DB sysname  

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
   SELECT [name]  
   FROM master..sysdatabases 
   WHERE [name] NOT IN ('model', 'tempdb') 
   ORDER BY [name] 

OPEN curDB  
FETCH NEXT FROM curDB INTO @DB  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)  
       PRINT @SQL  
       FETCH NEXT FROM curDB INTO @DB  
   END  

CLOSE curDB  
DEALLOCATE curDB

It is provided by Tim Ford in his blogpost on mssqltips.com and he is also explaining why updating statistics matter.

Please note that this is an CPU and IO intensive task that should not be done during buisiness hours.

If this solves your problem, please do not stop there!

Regular Maintenance:

Have a look at Ola Hallengren Maintenance Solution and then set up at least this two jobs:

• A statistics update job (if possible every night). You can use this CMD code in your agent job. This job has to be created from scratch.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d MSSYS -Q "EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @MaxDOP = 0, @LogToTable = 'Y'" -b

• An index maintenace job. I would suggest starting with a scheduled execution once a month. You can start with the defaults Ola provides for the IndexOptimize Job.

There are several reasons I am suggesting the first job to update stats separately:

• An index rebuild will only update the statistics of the columns covered by that index while an index reorganization does not update statistics at all. Ola separates fragmentation in three categories. By default only category high indexes will be rebuild.
• Statistics for columns not covered by an index will only be updated by the IndexOptimize job.
• To mitigate the Ascending Key Problem.

SQL Server will auto update the statistics if the default is left enabled. The problem with that are the thresholds (less of a problem with your SQL Server 2016). Statistics get updated when a certain amount of rows change (20% in older Versions of SQL Server). If you have large tables this can be a lot of changes before statistics get updated. See more info on thresholds here.

Since you are doing CHECKDBs as far as I can tell you can keep doing them like before or you use the maintenance solution for that as well.

For more information on index fragmentation and maintenance have a look at:

SQL Server Index Fragmentation Overview

Stop Worrying About SQL Server Fragmentation

Considering you storage subsystem I would suggest no to fixate to much on "external fragmentation" because the data is not stored in order on your SAN anyway.

Optimize your settings

The sp_Blitz script gives you an excellent list to start.

Priority 20: File Configuration - TempDB on C Drive: Talk to your storage admin. Ask them if your C drive is the fastest disk available for your SQL Server. If not, put your tempdb there... period. Then check how many temdb files you have. If the answer is one fix that. If they are not the same size fix that two.

Priority 50: Server Info - Instant File Initialization Not Enabled: Follow the link the sp_Blitz script gives you and enable IFI.

Priority 50: Reliability - Page Verification Not Optimal: You should set this back to the default (CHECKSUM). Follow the link the sp_Blitz script gives you and follow the instruction.

Priority 100: Performance - Fill Factor Changed: Ask yourself why there are so many objects with fill factor set to 70. If you do not have an answer and no application vendor strictly demands it. Set it back to 100%.

This basically means SQL Server will leave 30% empty space on these pages. So to get the same amount of data (compared to 100% full pages) your server has to read 30% more pages and they will take 30% more space in memory. The reason it is often done is to prevent index fragmentation.

But again, your storage is saving those pages in different chunks anyway. So I would set it back to 100% and take it from there.

What to do if everybody is happy:

• See the rest of the output of sp_Blitz and decide if you change them as suggested.
• Execute sp_BlitzIndex and have a look at the indexes you created, if they are used or where there might be an opportunity to add/change one.
• Take a look at your Query Store data (as suggested by Peter). You can find an introduction here.
• Enjoy the rock-star live a DBA deserves. ;)

Answer 2

Not disregarding all your answers that were very useful and which I applied or will apply, the biggest problem was not easy to find.

The problem got worse in the days after our last messages.

As we are based on cloud, neither I nor the company that manages the infrastructure and gives us support has access to the physical hosts.

Something made me wonder when I noticed that some days the processor was on average at 20% and other days it was much higher, over 60%, when the workload, although never exactly the same, is similar. There are the same number of people performing more or less the same type of operations.

Earlier this week, users started to get stuck for several minutes and only the processor was strangled. I asked several users to log out (those who were spending more resources but still nothing out of the ordinary), I turned off various services linked to the database, and in the end nothing has changed. I asked the sysadmin that supports us and that can communicate with the guys of our cloud to remote to my machine to see what I was seeing and to help me find something, because I could not do better to find the problem.

The technician also did not find anything. He finally started to give me some reason that something else had to be causing this problem and was when he contacted the cloud. In the cloud, they did not realize anything, just that because there is configured load balancing between physical hosts, the VM that supports our SQL Server had been moved a few times that day between physical hosts. Fortunately, I told our technician exactly at what time the problems began to occur that day, which coincided with the time the VM had been moved the last time to one of the physical hosts from which it had not left the rest of the day.

If the technician had not followed closely this problem, this was going to be more one of those times when he could even talk to the cloud guys, but when they saw performance samples, they would not get anything, because once again the cloud only saw samples with CPU on the order of 40/50%, when in fact it was on average above 80% and often stuck at 100%.

Now the machine is standing on a physical host (not moving between hosts) and although we have not yet achieved the perfect performance, everyone is working and giving a lot more positive feedback, because the average CPU is about 20% with all our users and services.

In the meantime, we also put tempdb on another disk (it was on the Operating System disk) and we increased the files, to be more in agreement with the number of cores of the CPUs.

The number of cores were also adjusted based on the recommendations of sp_Blitz.

There was also an automatic routine that was running all day based on an old date ... and since it did not end in the morning when we arrived, and we have no way to check whether it is running or not, I still started to run manually. But probably the other was still running and were running twice during that time. We've changed the date to reduce the time it takes, and now it's late at night. But this was not the solution, as it was solved before many problems we had as the one described here.

We also managed to get the ERP assistant to schedule a meeting with the manufacturer, so we are going to show our system and look for suggestions, as well as clarify some doubts, as there are recommendations in the training videos that are contrary to most of recommendations, including Microsoft itself, such as Priority Boost on and Fill Factor 70%.

Since the application also has a maintenance screen, I'll look for the required periodicity of these maintenance, and what's left to do outside the application. My idea is to use Ola Hallengren's plans.

I believe that Thomas Kronawitter answer is absolutely correct and I'm applying it, however, I think this description can be important to other people that after following all the good practices still can't fix the problem because it can be in the physical hosts. Thanks Thomas.