I understand what the professor is trying to teach. The difference in performance will probably only show on a system that has insufficient available memory to store all data in memory. And even then, any buffer layer in the OS that delays disk writes might affect your ability to properly measure the different, especially with a set of just 100,000 rows of 47 bytes of data, since that would only be 5 MB worth of data.
The reason is, that the engine may not write a changed data page immediately to disk. If at some point the "lazy write" comes by and write "dirty" pages to disk, then the writes will typically be sequential and (more importantly) not part of the performance that you are measuring.
Once you have a database that is much larger than the memory that is assigned to SQL Server, then you will see more reads and writes for the more expensive scenarios.
Gert-Jan
No comments:
Post a Comment