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?
Best Answer
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.