Why could query performance improve enormous after db restore on new server with worse hardware?

Question

A asp.net page doing very slow to load a list from SQL Server has increased it's performance very much after the whole database has been restored on a new SQL Server. The asp.net app has moved from and to the same servers.

The page's performance has improved to something about 1/5 of the previous load time.

The new system even has less Memory (12 GB instead of 30 GB). - on the old SQL Server is restricted to use 20 GB of 30 GB and it claims 20 GB. - on the new SQL Server is restricted to use 8 GB of 12 GB and it actually claims 4.5 GB only

The new system has an Intel Xeon E CPU while the old has an Intel Xeon X CPU.

Users say the performance has improved since the moving in general.

Unfortunately I have no benchmark of the page and/or the queries I am investigating on and also not for the involved servers at all.

This behavior is the opposite of my expectations!

So the question is, what could possibly have improved this?

Do statistics possibly play a role here? I would guess they are part of the db and so included in the restored db - or have they been created from scratch? Any thoughts and ideas are very welcome.

EDIT 11-March 2016:

I tested disk performance of both systems: The old disk performance made 0,3 MB/S to 55 MB/s with Average of 30 Mb/s. The new system shows 141 Mb/s to 1690 Mb/s with average 803 Mb/s. So since the disk performance seems to be more than 10x better, I guess this maybe a good reason for better performance. We are having a lot of disk usage because of bad design (select *) and lots of blobs and (n)TEXT types. So lots of data is not in cache and has to be loaded repetetively from storage. Additional the transaction log has been separated from the data files to another drive (Average 800 Mb/s), this was not the case on the old server). Would anyone agree that we can assume this is (besides all your interesting thoughts) the main reason for improvement?

Answer 1

The amount of memory available to the SQL Server is an important input to the choice of execution plan made by the query optimizer.

It is certainly possible that the new SQL Server, with its reduced memory, is encouraging plans without memory-consuming operations (e.g. sorts and hashes) that happen to be 'better' for the type of queries you are executing.

If this is indeed a primary factor, reducing the amount of memory available on the original server would also likely increase its performance. Typically, this would be unusual, assuming statistics are good and the queries usually get reasonable cardinality estimates. Nevertheless, it might explain what you are seeing; a detailed analysis would be required to be sure.

To answer your final question: yes statistics are a persistent part of the database and are included in a backup/restore.

Update

Based on the new information in the question concerning the vastly improved I/O performance of the second system, yes, that is most likely the reason.

Answer 2

I Had the same situation. In my case, the database have been restored to a empty hard drive so there is no file fragmentation, the same to the database it self. As it comes from a restore file, it has no data fragmentation and the indexes where created during the restoration.

Answer 3

I had a similar situation, but in my case I was creating a copy of the database on the same server (using a different name for the database), so there were no hardware differences. A query that was previously taking 2 1/2 minutes now took less that 1 second on the copy. Comparing SQL Server query plans between the the two databases, I saw that the plans were different and that the one from the original database had significant differences between estimated and actual row counts.

When SQL Server's estimated row counts are wildly off, it affects the query plan SQL creates and how much memory it allocates to the query. This in turn affects the performance of the query. SQL Server makes these estimates from the statistics on the table.

To correct the problem, I updated the statistics on the main table in the query (using the UPDATE STATISTICS command). This resulted in the queries in the original database being as fast as in the copy.

Curiously, a maintenance job that ran the night before had executed Exec sp_updatestats. The logs of the maintenance job indicated that 57 of 59 statistics had been updated and that the other two did not need updating. Apparently they did.