SQL Server uses all kinds of statistics about database usage in order to be able to optimize SQL queries. Examples of such statistics are: number of records, density of pages, and histograms. SharePoint is responsible for keeping the statistics of (almost all of) its own databases up to date, which SharePoint leaves up to specific SharePoint timer jobs. As a result, no explicit action needs to be taken by administrators in this regard. However, outdated statistics can have serious performance repercussions, and it’s very useful to check periodically if SharePoint databases are recent. If they are not, this may indicate a problem with the timer jobs that needs to be addressed, but no direct action should be taken at the database level.
The next query finds the state of statistics for a specific database.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS ‘Statistics Last Updated’
, s.rowcnt AS ‘Row Count’
, s.rowmodctr AS ‘Number Of Changes’
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS
DECIMAL(28,2)) * 100.0)
AS DECIMAL(28,2)) AS ‘% Rows Changed’
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName
This query lets you find an overview of all statistics for a specific database. The primary source of information for this DMV query is sys.indexes, containing information about indexes, row counts, number of rows that have changed since statistics were last updated, etc.
Please Note The query only shows statistics for indexes that have more than 500 rows, because statistics are only relevant for larger indexes.
In SharePoint 2010 and up environments, it is recommended to let SharePoint take care of keeping the SQL statistics of various SharePoint databases up to date. SharePoint does this by letting SharePoint timer jobs run nightly and call the following stored procedures:
· proc_DefragmentIndices
· proc_UpdateStatistics
· proc_UpdateStatisticsNVP
Please Note When SharePoint leverages these SharePoint stored procedures to keep SharePoint database statistics up to date, the database option AUTO_CREATE_STATISTICS should be set to OFF (please refer to http://blogs.msdn.com/b/chunliu/archive/2011/11/17/auto-update-statistics-and-auto-create-statistics-on-or-off-for-sharepoint-2010-databases.aspx for more information). This prevents the situation where two mechanisms are working against each other, both trying to take responsibility of keeping SharePoint statistics up to date.
proc_DefragmentIndices
This stored procedure determines the fragmentation percentage of the associated SharePoint database and rebuilds indexes for large tables (row count > 10,000) when the fragmentation percentage is > 30%. When SQL Server Enterprise Edition is used, indexes are rebuilt online; otherwise indexes are rebuilt offline which locks the affected database table. Index fragmentation is the normal result of SQL insert, update, and delete operations and causes suboptimal disk I/O when accessing database tables.
proc_UpdateStatistics
Updates index statistics. The formula for updating statistics on indexes is:
(sys.sysindexes.rowmodctr * 100) / (sys.sysindexes.rowcnt + 1) > 1
Where:
· rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table.
· Rowcnt returns the total number of rows in a table and may also contain uncommitted data. For big tables, reading this column is far more efficient than calculating actual table size by querying current table row count.
The result of this formula is that indexes are updated nightly in most cases, a little less frequently once the database table gets larger (at least 1M+ rows of data). Results > 1 lead to index partitioning. Results of 0 or 1 lead to table partitioning.
proc_UpdateStatisticsNVP
Updates index statistics or “NameValuePair” database tables, which are only present in SharePoint content databases. Please refer to http://support.microsoft.com/kb/2635071/en-us for more information. “NameValuePair” database tables are used when indexing columns in a SharePoint list (at the software level, not at the database level). As a result of the indexing of a column of a SharePoint list, the NameValuePair_[SQLCollation] table is filled with all values of the columns that are indexed, as well as a reference to the corresponding list item.
Please Note Every time a list item is modified, the associated NameValuePair row is modified as well. Please refer to http://apmblog.compuware.com/2009/01/28/sharepoint-list-performance-how-list-column-indices-really-work-under-the-hood/ for more information.
The proc_UpdateStatisticsNVP stored procedure is only present on SharePoint content databases; the other stored procedures are present on the following types of SharePoint databases:
· Configuration database
· Content database
· User Profile Service Application Profile database
· User Profile Service Application Social database
· Web Analytics Service Application Reporting database
· Web Analytics Service Application Staging database
· Word Automation Services database
· WSS_Logging database
The stored procedures are run nightly and called by SharePoint timer jobs executed by the following database maintenance Health Analyzer rules:
· Databases used by SharePoint have fragmented indices (Daily)
· Database used by SharePoint have outdated index statistics (Daily)
The Health Analyzer framework is a SharePoint feature (for SharePoint 2010 and up) that enables administrators to schedule built-in jobs that identify problems with SharePoint and potentially automatically fix those problems. The timer job responsible for executing mentioned Health Analyzer rules is the Health Analysis Job (Daily, Microsoft SharePoint Foundation Timer, Any Server). This can be checked by following the next procedure:
1. Start SQL Server Profiler.
2. Run the various Health Analyzer rules on demand.
3. Watch stored procedures getting executed as the result of each Health Analyzer rule.
4. Then, check the Monitoring page in SCA to review timer job definitions until you see one of the stored procedures mentioned in this section.
5. Run the timer jobs on demand until SQL Server Profiler output matches the output of the Health Analyzer rule mentioned above. Limit your search to timer jobs that are run Daily with a scope of Any Server.
Please Refer to http://www.mssqltips.com/sqlservertip/2648/sharepoint-2010-databases-maintenance-health-analyzer-rules/ for more information.
The next Figure shows an example result of the State of Statistics query:
The DMV query returns the following information:
· SchemaName, the name of the database schema. A schema provides a way to logically group database objects, such as tables, views, and stored procedures. The schema name “dbo” is the name of the default schema in SQL Server.
· TableName, the name of the database table.
· IndexName, the name of the table index.
· Statistics Last Updated, the date and time when index statistics were updated for the last time.
· Row Count, the number of rows in the table.
· Number of Changes, the number of changes that have taken place since the last statistics update.
· % Rows Changed, percentage of changes related to total number of rows in the database table.
Look out for:
· Outdated statistics. Take a look at the date and time when the statistics were updated for the last time. Usually, these statistics are updated nightly. Older statistics indicate problems with SharePoint timer jobs responsible for maintaining statistics.
· Inspect the Number of Changes and % Rows Changed. If huge amounts of changes take place in SharePoint databases, it may be necessary to execute SharePoint statistics timer jobs more frequently either manually or by adjusting their schedules.
Please Note It’s extremely rare that this is necessary.
SharePoint Dragons 