- The first time the data is accessed is much slower than the next access, because it reads the data from the disk and writes it to the buffer;
- Aggregate queries (Sum,count, etc.) and other queries that scan most tables or indexes require a lot of buffering and can adversely affect performance if it causes SQL Server to discard other data from the cache;
- It is also possible to use SQL Server as a memory cache after careful design;
- Write performance is largely determined by the speed with which SQL Server writes the log sequentially, while read performance is almost determined by the amount of RAM available and the speed at which data is randomly read from the data file;
- If the log files and data files are on the same disk volume, the final disk seek causes the speed of access to the log file to drop quickly when the write database log operation is performed concurrently with the deferred write thread writing the modified page to the data file operation or from the disk read data operation. For this reason, it is important to separate the log files from the data files on their respective disks.
Important conclusions of SQL Server performance impact
