SharePoint 2010–After Update “Could not find stored procedure ‘proc_UpdateStatisticsNVP”

Oh Microsoft, how I do love to hate you… so per KB2635071 Microsoft has released a fast publish which basically says “Nothing to see here, move along.”

Or, to put it more exactly…

This is a known issue that will be fixed in a future release of the product. The error does not affect the functionality of the product in any way and can be safely ignored.

However, there are those of us who actually like to have a nice and clean event log, if for no other reason than when we filter for Critical and Error events we actually see issues that are pertinent to troubleshooting our farms, there needs to be a fix so we don’t end up training our support teams “Ignore SharePoint event alerts, it always does that.”  We’ve sort of missed the forest for the trees here.  The bigger picture should be how do we separate the real alerts from the ones we can safely ignore in a way that a support person can easily recognize.  In this case, the solution is to just add the proc_UpdateStatisticsNVP to the other databases so our SharePoint farm doesn’t alert on the issue and our event log stays cleaner and hopefully only shows relevant Critical and Error events.

Issue:
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 15/07/2011 12:00:31 AM
Event ID: 5586
Task Category: Database
Level: Error
Keywords:
User: ray
Computer: SPS2010
Description:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure ‘proc_UpdateStatisticsNVP’.

<Date and Time>T00:00:13.143: Starting
<Date and Time>T00:00:13.143: Dropping automatically created stats on user tables
<Date and Time>T00:00:13.143: Updating statistics on user indices
<Date and Time>T00:00:13.150: Statistics for dbo.IX_Dependencies_ObjectId are now being updated with default sampling.
<Date and Time>T00:00:13.150: Rebuilding
<Date and Time>T00:00:13.153: Done
<Date and Time>T00:00:13.153: Statistics for dbo.IX_Objects_Version are now being updated with default sampling.

Cause:
The SharePoint 2010 Health Analyzer consists of a rule with the Title "Databases used by SharePoint have outdated index statistics". This health rule runs on a daily schedule and checks the SharePoint databases for outdated index statistics. If databases index statistics are found to be outdated, the rule automatically attempts to update the statistics on the databases. The error is generated because the health rule attempts to run ‘proc_UpdateStatisticsNVP’ stored procedure against the configuration database, however, this stored procedure does not exist in the configuration database.

A quick search through \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SQL showed [dbo].[proc_UpdateStatistics] was calling this proc.
So I assume wherever I find [dbo].[proc_UpdateStatistics] I should also find [dbo].[proc_UpdateStatisticsNVP]

Fix:
The fix requires that each DB that has [dbo].[proc_UpdateStatistics] also have [dbo].[proc_UpdateStatisticsNVP].  The following SQL Query will help you identify the DB’s:

exec sp_msforeachdb ‘if exists(select name from [?].sys.objects where type = ”P” and name like ”%proc_UpdateStatistics%”) AND not exists(select name from [?].sys.objects where type = ”P” and name like ”%proc_UpdateStatisticsNVP%”) select ”?”’

This will give you the DB’s that have [dbo].[proc_UpdateStatistics] but don’t have [dbo].[proc_UpdateStatisticsNVP] then all you need to do is create the proc using an existing one.  You can do this by finding the proc_UpdateStatisticsNVP, scripting it as a Create to the new query editor window and then running the create script against each database that is identified from the script above.

Now, supposedly this will have no effect on the performance of the farm, except of course KEEP IT FROM LOGGING IRRELEVANT ERRORS.  Long term this means our support people will hopefully actually trust SharePoint event logs and pay attention when an Error or Critical is actually logged.

SharePoint 2010 – Language Pack IDs

Just a quick informational post for myself to keep track of the Language Pack IDs for SharePoint

Language

Language ID
Arabic 1025
Basque 1069
Bulgarian 1026
Catalan 1027
Chinese (Simplified) 2052
Chinese (Traditional) 1028
Croatian 1050
Czech 1029
Danish 1030
Dutch 1043
English 1033
Estonian 1061
Finnish 1035
French 1036
Galician 1110
German 1031
Greek 1032
Hebrew 1037
Hindi 1081
Hungarian 1038
Italian 1040
Japanese 1041
Kazakh 1087
Korean 1042
Latvian 1062
Lithuanian 1063
Norwegian (Bokmål) 1044
Polish 1045
Portuguese (Brazil) 1046
Portuguese (Portugal) 2070
Romanian 1048
Russian 1049
Serbian (Latin) 2074
Slovak 1051
Slovenian 1060
Spanish 3082
Swedish 1053
Thai 1054
Turkish 1055
Ukrainian 1058

SharePoint 2010–Indexing columns in a SharePoint List

Indexing columns in a SharePoint list can substantially improve the performance of various query operations, such as queries that use the indexed column, join operations, and ordering operations. In any list, you can either index a single column or define a composite index on two columns.

How It Works

SharePoint index columns on a list are somewhat similar to indexing columns in a database table.  But in the case of SharePoint these indexes are maintained in the NameValuePair table by SharePoint instead of SQL Server.  Indexed columns are actually implemented by populating all values of the indexed columns into the NameValuePair table and that table is then joined with the AllUserData table once a query is executed that uses the indexed column as a filter.  As such, you need to carefully plan your indices for your application or feature, it should not be treated like a SQL index.  You can add huge amounts of overhead because each indexed column will be stored in the NameValuePair table, so the number of additional rows added to NameValuePair is: #IndexedColumns * #ItemsInList

For example, if you have a list with 20K rows, you add 3 indices, you have an additional 60K rows in the NameValuePair table.

Enforcing Unique Values

The ability to enforce unique column values is also a new addition to SharePoint 2010.  The unique value constraint applies only at the list instance level. So while uniqueness can be defined at the site column level, it is enforced within each list.  Because of the way the unique value constraint works, you must index any columns that enforce uniqueness. You can apply the unique value constraint to a column in three ways

  • interactively through the user interface
  • declaratively by setting the EnforceUniqueValues attribute in the column definition
  • programmatically through the SPField class.

User Interface

image

When the user selects Enforce unique values and clicks OK, a warning dialog is displayed if the column is not already indexed;

image

The user is then given the option to automatically index the column. After a column has been set to enforce uniqueness, indexing cannot be turned off for that column.

image

However, you can turn off indexing for that column if you first disable enforcement of uniqueness constraints.

Declaratively in the column definition

When you are defining the column

<Field

...

EnforceUniqueValues = "TRUE" | "FALSE" 

...

></Field>

Programmatically

Use the EnforceUniqueValues property on the SPField object to set a column to require unique values. The property gets and sets a Boolean value to specify whether duplicate values are allowed; the default is to allow duplicate values, so you must explicitly set a column field property to true.

SPSite site = new SPSite("http://localhost");
SPWeb web = site.OpenWeb();

SPList custList = web.Lists["Customers"];
SPField custPhone = custList.Fields["Phone Number"];

custPhone.Indexed = true;
custPhone.EnforceUniqueValues = true;

/// You must call the Update() method 
/// when you change the EnforceUniqueValues property
custPhone.Update();

 

Query and Index Performance

If you are querying on a field that is not indexed, and the resulting scan encounters more items in the list than the query threshold, the query will be blocked. Set SPQuery.RowLimit to a value that is less than the query threshold. The value of ContentIterator.MaxItemsPerQuery is ensured to be less than or equal to the threshold, which is the recommended value for SharePoint Server 2010.

Composite indexes can enable you to speed up queries across related values. However, like with database indices, list indexing does incur a performance overhead. Maintaining the index adds processing to creating, updating, or deleting items from a list, and the index itself requires storage space.

A list instance only supports a maximum of 20 indices;

image

However, Indexing more than one column may not get the boost in performance you are expecting when using more than one indexed column in a query. Always be aware of the additional resources that are involved in indexed columns. As stated above, for every indexed column you get an additional row in NameValuePair for every list item. Additionally – modifying list items also causes the index table to be modified. You’ll want to use indexed columns sparingly because some SharePoint features require indices and cannot be enabled on a list where there is no index slot remaining (Two common ones are SharePoint 2010 eDiscovery and Information Management Policy Retention). You should choose your indexed columns carefully to maximize query performance while avoiding unnecessary overhead; Generally you only want one index column per view/query.

You can only apply unique value constraints to columns with certain data types, because some data types cannot be indexed.

Supported Column Types
  • Single line of text
  • Choice field (but not multichoice)
  • Number
  • Currency
  • Date/ Time
  • Lookup (but not multivalue)
  • Person or Group (but not multivalue)
  • Title (but not in a document library)
Unsupported Column Types
  • Multiple lines of text
  • Hyperlink/Picture
  • Custom Field Types
  • Calculated Field
  • Boolean (yes/no)
  • UI version
  • Checked out to
  • Content type ID