I have SQL Server installations. Both are running under HyperVisor on the same processor. Both have the same amount of core / RAM / disk space allocated.
- One is the old server and the other is the new one.
- The old server is running Windows Server 2008 with SQL Server 2008.
- The new server will run Windows Server 2017 with SQL Server 2017.
Databases are exactly the same. They were transmitted overnight via the new server. We have also removed a new one of them on the new server, so we have a little less database on the new server.
All queries on the old server are different from those on the new server 70 and 100 times slower, This is tested in both cases in Remote Desktop directly on the server with the
A simple example is when I check with locks
sp_who2, On the old server it is about 50 ms. It can take up to 1900-2100 ms on the new server, which is about 50 times slower. Then we come to a more average query like the following to get the 500 worst-performing queries. Note that a standard SQL Server database is used here. So the default settings are used and nothing is changed.
SELECT top 500 GETDATE () AS "Pickup Date", qs.last_execution_time as "Date of last execution", qs.execution_count AS "execution counter", SUBSTRING (qt.text, qs.statement_start_offset / 2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) AS "query text", DB_NAME (qt.dbid) AS "DB name", qs.total_worker_time AS "total CPU time", qs.total_worker_time / qs.execution_count AS "Average CPU Time (ms)", qs.total_physical_reads AS "Total Physical Reads", qs.total_physical_reads / qs.execution_count AS "Average physical reading", qs.total_logical_reads AS "Total logical reads", qs.total_logical_reads / qs.execution_count AS "Average Logical Read", qs.total_logical_writes AS "Logical Total Writes", qs.total_logical_writes / qs.execution_count AS "Average logical writing", qs.total_elapsed_time AS "Total duration", qs.total_elapsed_time / qs.execution_count AS "Average Duration (ms)", qp.query_plan AS "Plan" FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) AS qp FROM WHERE qs.execution_count> 50 OR qs.total_worker_time / qs.execution_count> 100 OR qs.total_physical_reads / qs.execution_count> 1000 OR qs.total_logical_reads / qs.execution_count> 1000 OR qs.total_logical_writes / qs.execution_count> 1000 OR qs.total_elapsed_time / qs.execution_count> 1000 SORT BY qs.execution_count DESC, qs.total_elapsed_time / qs.execution_count DESC, qs.total_worker_time / qs.execution_count DESC, qs.total_physical_reads / qs.execution_count DESC, qs.total_logical_reads / qs.execution_count DESC, qs.total_logical_writes / qs.execution_count DESC
This query runs on the old server in 1 or 2 seconds. On the new server, it takes between 1 min 40 seconds and 2 min 20 seconds, which is 160 times slower. I have the worst queries for events in the 20 seconds, which now take up to 5-6 minutes. And yes, these tests are all done at night when nobody is connected
My question is whether there are some default settings in SQL Server 2017, or maybe something in Windows Server 2017 that needs to be changed now to get the same performance as in 2008.
Check the Windows performance tools for all queries do not CPU, RAM and floppy have either an old or a new server. So we know something in SQL Server / Windows slows things down.