SQL Server database design goals: Memory, Disk and I/O - maximize the scalability with monitoring
Do you consider the I/O whenever a database design project is assigned?
It is one of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. Over the time it may be one of the criteria that extensive usage of data and defautl behaviour on SQL Server builds a buffer pool in memory to hold pages read from the database.
While queries are getting designed the code must be optimized and dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. By design SQL optimizer balances between keeping buffer pool from becoming so big that the entire system is low on memory and minimize physical I/O to the database files by maximizing the size of the buffer pool. As such the SQL Server dynamically acquires and frees memory as required. Unless you are sure about how data is managed it is not a good practice to specify how much memory should be allocated to SQL Server (min and max), although the option still exists and is required in some environments.
In some cases the specific hardware may have advantage in performance as the system's processors has its own memory and possibly its own I/O channels, and each CPU can access memory associated with the other groups in a coherent way, refer to NUMA for more information. As per the usage practices a monitoring strategy should be build to obtain the system statistics, this is where Dynamic Management Views (DMV) comes in handy. To the topic on I/O statistics - sys.dm_io_virtual_file_stats dynamic management view is an useful one.
uses Microsoft Windows operating system input/output (I/O) calls to perform read and write operations on your disk. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system. To capture the statistics you should monitor disk activity involves two areas of focus: "Monitoring Disk I/O and Detecting Excess Paging" & "Isolating Disk Activity That SQL Server Creates". Also FYI How SQL Server manages when a database is created in terms of I/O and disk usage? too a good one to understand when you have frequent database creation exercise.
Further refer to the blog posts from Rob Pearl on how best you can monitor & capture the statistics:
I/O I/O - It's why my server's slow.....Examing I\O Statistics
I/O You a followup - More about I/O, Performance Tuning & Troubleshooting....