I was attending the TechEd session OFF310_Understanding SQL Server Database Options for Microsoft Office SharePoint Server 2007 presented by Todd Klindt where I picked up these tips regarding SQL Server used by SharePoint (MOSS).
Set ContentDB’s offline: despite what many people believe, this does not take your database offline as in not available anymore. Instead this tells SharePoint not to make this database available for new Site Collections. Using this feature you can force new Site Collections to be created in specific databases by setiing all other databases offline. As a reminder, you can set database offline in Central Administration -> Application Management -> Content Databases. Select the database you wish to set offline and then change the Database Status dropdown box to Offline
There is currently no support for SQL 2008 to use it with SharePoint.
SQL Express can be upgraded. Install Standard or Enterprise with the following syntax: setup.exe SKUUPGRADE=1.
Never shrink your databases! This operation fragments your indexes and data. Furthermore you will suffer some performance degradation when the database needs to grow again.
Don’t use autogrow for your contentdatabases, unless you let it grow with big chunks instead of the default 10% (e.g. 10GB at a time).
Defragmenting file system: SQL will be faster if the database files are contiguous in the file system. Using the built in defrag tool will have performance ramifications. Consider using something like Diskeeper and its intelligent defrag. Consider stopping SQL if possible.
Diskeeper White paper on SQL file fragmentation: http://files.diskeeper.com/pdf/SQLdefragmented.pdf
Properly configure SQL Surface Area for SharePoint
Use SP2 if possible, it has improvements for SharePoint
Do not change SharePoint databases via SQL Queries. Microsoft hates that.
Can use SharePoint farm backups to back up SQL
Don’t forget to include your System databases in your maintenance plans.
Attached to this post, you can find the slides of the Teched session