0

I have an old SQL Server 2014 Express which I wanted to upgrade to SQL Server 2017 Enterprise.

I've installed it on the same machine and moved one of the databases (through a backup -> restore).

Then I ran this query on both servers:

SELECT T1.FileName, T2.BatchId
FROM Table1 AS T1
JOIN Table2 AS T2 ON T1.BatchGuid = T2.Guid
ORDER BY T2.BatchId, T1.FileName

Unexpectedly, the old server returned ~1 200 000 rows in 8-12 seconds and the new server in ~90 seconds.

I've tried to go through the same steps on my laptop and it worked perfectly (12 seconds).

Then I've tried to run the query without order by and both servers returned same 1 200 000 rows in 4 seconds.

What could be the reason of such a slow performance with an order by clause?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aleksandr Bazarnyi
  • 1
  • *What could be the reason of such a slow performance with an order by clause?* - hah, how about ordering 1.2 million rows? But seriously, how warm was the new server, how did you really make it an actual fair test etc? – Caius Jard Jan 30 '21 at 13:29
  • What do you mean saying warm? – Aleksandr Bazarnyi Jan 30 '21 at 13:31
  • I've just ran 2 queries one after another on two different servers and saw the results in the right bottom corner. – Aleksandr Bazarnyi Jan 30 '21 at 13:33
  • How much had it been used? Did you set the restore job going overnight, then the server sat around a few hours idling, never been queried before etc, then you hit it with this query and compared it to the live server that had been being used constantly, with all this data still in memory etc.. From what you've painted out so far you don't seem to have taken a very methodical, scientific approach to determining any differences and ruling them out - you just seem to be jumping to a conclusion that "2017 is slower at sorting data than 2014-why?" - slim chance that anyone's crystal ball will know – Caius Jard Jan 30 '21 at 13:34
  • The database is not so big, it took around 5 minutes to restore it. – Aleksandr Bazarnyi Jan 30 '21 at 13:36
  • I'm trying to make it work not slower then the old one and asking for help. Just point me to where I need to search. – Aleksandr Bazarnyi Jan 30 '21 at 13:38
  • 1
    Make it a fair test, then start looking for differences, get query plans, table organization etc – Caius Jard Jan 30 '21 at 13:40
  • I would appreciate it if you would tell me something by looking at plans. https://fex.net/s/osklek7 – Aleksandr Bazarnyi Jan 30 '21 at 13:56
  • I've found here: https://stackoverflow.com/questions/3862045/sql-queries-execution-plans-and-parallelism a suggestion to use option (maxdop 1) and it helped. But what could be the reason of such a slow performance with more than one CPU? – Aleksandr Bazarnyi Jan 30 '21 at 14:09
  • Share with https://pastetheplan.com Try `UPDATE STATISTICS Table1 WITH FULLSCAN; UPDATE STATISTICS Table2 WITH FULLSCAN;` – Charlieface Jan 30 '21 at 21:58

0 Answers0