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
exactly. the idea is to teach us the diference in performance in real life scenarios when a server has a lot of load.
but.. shouldnt the DBCC DROPBCLEANBUFFERS write the data present in the cache/buffers to the disk? At least is what my professor says.
I found some configurations about memory resources available for the SQL Server... Should I try with something like 1MB for max memory?
No comments:
Post a Comment