For SharePoint 2007 it is recommended to enable AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS, if disabled, it may require manual statistics update. If one or more indexes with row modification counter exceeds 500+20% of rows, performance issues could be occurring.
For SharePoint 2010 it is recommended to disable AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS. Enabling the Auto Update stats option can result in serious blocking issues with databases. Microsoft recommends disabling this option and let SharePoint Time jobs take care of it for you. more here
In SharePoint 2007 Defrag your indexes, Index Fragmentation exists when the logical ordering of pages in an index does not match the physical ordering of data on the disk. In addition, fragmentation can decrease performance by increasing the number of reads that are necessary to retrieve information. An index that is more than 5 percent or less than 30 percent fragmented should be reorganized. An index that is more than 30 percent fragmented should be rebuilt.
Note: In SharePoint 2010, You shouldn’t have to touch SharePoint indexes as the Health Analyzer rules do it for you. This is documented here http://technet.microsoft.com/en-us/library/cc262731.aspx In the majority of cases you should not need to alter these, however there are scenarios that you may need to have the stats updated more frequently than what the rule allows.
To further improve index data storage and performance, use fill factor. When indexes are created or rebuilt, the fill factor value (1-100) determines the percentage of space that can be filled with data on each leaf level page. The remaining space is reserved for future growth. For many situations, the default server-wide fill factor level of 0 (fill each page to 100% full) is optimal. However, for SharePoint, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.
In SharePoiiunt 2010Don’t mess with indexes or statistics in SharePoint databases! SharePoint takes care of it!
The exceptions are:
* Search Administration Database
* Secure Store Database
* State Service Database
* Profile Sync Database
* Usage Database
* Managed Metadata Database
* Business Connectivity Services Database
* PerformancePoint Services Database
For these databases, rebuild indexes if fragmentation is over 30%. This is not typically necessary as
these databases rarely fragment to that level.
The SQL Server Global Configuration Setting: Max Degree of parallelism (MAXDOP) should be set to 1 for SharePoint environments. see article. You do not have to gradually increase it, If a storedproc needs it, its
defined in the storedproc that the MAXDOP is overridden.
Running the same collation on the SQL instance as SharePoint uses for the databases has a positive
Performance impact. Reason: Whenever SharePoint has to do anything with the temp DB, the data has to be converted to the collation of the temp DB. So when you run the same collation, there is no conversion needed. The Temp DB is always running in the collation of the SQL Instance. Changing the collation of a SQL Instance basically means reinstalling the instance.
DBCC CHECKDB should be run weekly to look for problems. The following checks are performed: Validate each table’s storage engine metadata, Read and check data index and text pages, Check all inter-page relationships, Check the page header counts. Do not configure it to run any necessary repairs, send errors to logs for analysis. SharePoint does not Support running repair tools. How long can it take, read here What does CHECKDB really do? here