Keeping your indexes tuned up is essential for every database, without exception. This is a dogmatic statement that is easy to defend. You say your database is small, so index tuning has little value. My response would be that because your database is small, the indexes remain adequately tuned by default. You say your database has very little change and doesn’t require constant tuning. Again, my response is that the size does not matter; it is the occurrence of fragmentation, and statics about your indexes that must be optimized. If they are optimized, and the data changes slowly, then the indexes will remain optimized for a much longer period of time.
I worked on one database where 100% of the data was replaced at least once daily. My indexes fragmented very quickly in this situation. The point is, your index optimization is only good for the period of time when fragmentation increases the cost for retrieving the data, or statistics no longer accurately represent the data contained in the indexes, resulting in a less than optimal query plan.
There are two kinds of fragmentation that occur in a database. The first is at the Operating System level. Your database resides in two or more files. If the files run out of space, they acquire additional space from the operating system. If that additional space is not contiguous to the existing file allocation, then the file itself becomes fragmented. The second kind of fragmentation is created by SQL Server itself. When your table consumes the available space in a data page, it acquires additional space from SQL Server. If that space is not contiguous to the existing table data, internal, SQL Server fragmentation occurs.
You have probably figured out that fragmentation is bad. The closer your table data is physically in storage, the faster it is retrieved, because there is no latency waiting for the disk to spin, or the head to be moved, to the next location of your table data.
There is only one way to defragment tables inside SQL Server. The indexes have to be re-built. SQL Server requires as much empty space in the database as the largest index for the index being re-built. You can do a partial defragmentation on a table by re-organizing an index. This process re-balances at the leaf nodes only. It is much faster than a complete re-building of the index, but has less of an impact.
I mentioned statistics as well. Statistics on an index, or even on columns in a table not having indexes, provide information to the query analyzer used to determine the best execution plan for your SQL queries. They can be created automatically or manually. They may also be maintained automatically as data is inserted, or updated manually during off peak hours.
For your homework, if this is something you think you need to implement in your database, seek guidance for rebuilding and reorganizing of indexes, and maintaining statistics on your tables.
Cheers,
Ben