For nearly any engine persisting data, it is helpful, if not essential, to understand the internal workings of the engine, in order to maintain good performance over time.
That’s a bold statement. So, let me give some evidence to back it up. Let’s talk about SQL Server specifically for right now. Here are some things that if you know about them, will help keep your SQL database working well.
Breaking out TempDB onto its own drive reduces contention for all databases hosted on an SQL Instance. You can get better performance if your server has multiple cores by creating multiple TempDB files based on the number of cores (Do google search on what the current recommended strategy is…it depends, of course).
Size your database files ahead of time. That way the disk space allocated is contiguous. If your data files are fragmented on the disk, it takes more time to gather data.
Rebuild clustered indexes to remove fragmentation of a file within a data file. Again, the more records contained
contiguously on a disk, the less time waiting for the disk to spin, and the heads to move to get the next set of records.
Store BLOB data on separate data file. Generally, you don’t defragment BLOB data. So, placing it in it’s on file object
helps keep it from becoming fragmented between relational data, slowing down search times.
Know what makes a good Clustered index, and non-clustered index. Reduce the number of indexes by using composite indexes, or natural keys.
Maintain statistics and defragment tables during off-peak loads when possible.
All of these simple tips make a big difference in the performance of your database. This kind of information, and much more, is found in the SQL Server Unleashed series, as well as the SQL Server Internals. If you are architecting databases, or keeping them running, look into adding these skills to your toolbelt.
Cheers,
Ben