I mentioned here that sequential I/O was good and random I/O was bad. Bad is the wrong word to use. Random I/O is comparatively less efficient than sequential I/O when accessing the same amount of data. The point to take home is that when a hard disk head is seeking to a different location, it isn't reading and returning data. In the sequential I/O scenarios there is very little seeking and the drive can quickly return all the data in a single stream. In contrast, random I/O causes the disk to spend half its time seeking and it can only return half the amount of data in the same amount of time as the sequential scenario.
Here are two performance considerations for designing and administering a SQL Server database and server:
Index seeks and scans use random and sequential I/O respectively. The performance gained by index seeks is in the amount of data that needs to be accessed. Finding 1 row in just 4 random I/Os is much quicker than scanning through 10000 I/Os worth of data sequentially. There is a point at which scanning is quicker, and that's when the tables are small.
Log files are accessed sequentially, so it makes sense to put each database's log file onto its own disk for the best performance. Sharing one disk between many different log files will lose you the performance benefit.
Subscribe to:
Post Comments (Atom)
1 comment:
Of course, if your data is fragmented, you won't really be doing physically sequential I/O anyway, so don't forget to defragment your indexes regularly!
Post a Comment