Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors

Hello All,

After many performance issue investigations,  we have released at 10th of October 2015  following kb article for about “Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors”
https://support.microsoft.com/en-us/kb/3103194

In this article scope we make availability and  some flexiblity for database maintenance operations about  preventing “outdated update statistics” for DBAs , and now you are not depending just only SharePoint Daily Timer job which responsible update database statistics by using the proc_updatestatistics SQL procedure anymore.

Our TechNet article “Best practices for SQL Server in a SharePoint Server farm” has now been updated with the same guidance and cross referencing the new KB article.

Do not enable auto-create statistics on a server that hosts SQL Server and SharePoint Server. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. We recommend updating the SharePoint content database statistics daily using the FULLSCAN option from SQL Server. Although SharePoint does have a timer job to update statistics by calling proc_updatestatistics, we strongly recommend implementing a scheduled maintenance plan from SQL Server to ensure database statistics are reliably updated on a daily basis. For more information, see Outdated database statistics.

Best practices for SQL Server in a SharePoint Server farm
https://technet.microsoft.com/en-us/library/hh292622.aspx

Now ; to prevent  potential service outages, SQL Server maintenance plans can be implemented to keep SharePoint content database statistics updated by using the FULLSCAN option and it can be done manually by DBAs

When implementing the SQL Server maintenance plan to update the statistics on your SharePoint databases, it is not required to disable the job from SharePoint. However, because these maintenance tasks perform similar functions from both locations, it is permissible to disable the timer job from the SharePoint farm.

Advertisements

SharePoint 2013 and SQL Server 2014 Supportability

We will support SQL Server 2014 with SharePoint Server 2013 but we need April 2014 CU and also SP1 installed.

RTM of SQL Server 2014 and when SharePoint 2013 will be supported: http://blogs.technet.com/b/wbaer/archive/2014/03/21/sql-server-2014-and-sharepoint-server-2013.aspx

Using SQL Server 2014 Reporting Server (SSRS) features together with SharePoint 2013? Check more here: http://technet.microsoft.com/en-us/library/gg492257(v=sql.120).aspx

Without an April 2014 full-server package for SharePoint Server 2013 we cannot say yet that it will be supported with all available features. We also need updated information in our TechNet that we now support SQL Server 2014, but it should be available as soon all information are collected.

Resources:
http://blogs.msdn.com/b/joerg_sinemus/archive/2014/04/09/sharepoint-2013-and-09-april-2014-status.aspx

Does SharePoint supports SQL Server 2014 RTM ?

It was announced that the RTM release of SQL 2014 will be generally available April 1st
http://blogs.technet.com/b/microsoft_blog/archive/2014/03/18/sql-server-2014-released-to-manufacturers-will-be-generally-available-april-1.aspx

Currently we dont have any change to our official article for that but i can say unofficially YES , Starting with SharePoint 2013 SP1 + April 2014 CU will be supported.
You can follow the article that would be updated soon.
http://technet.microsoft.com/en-us/library/cc262485(v=office.15).aspx

Changing Content Database Collation for SharePoint with Supported Way.

The following TechNet Article talks about the SQL Server collation for the SharePoint databases http://technet.microsoft.com/en-us/library/cc288970.aspx#section1

“The SQL Server collation must be configured for case-insensitive. The SQL Server database collation must be configured for case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. This is to ensure file name uniqueness consistent with the Windows operating system.”

However, we do not support changing the default collation (Latin1_General_CI_AS_KS_WS) for SharePoint databases to any other collations (CI, AS, KS, WS).

We support any CI collation for the SQL instance (for master, tempdb databases). However we recommend using Latin1_General_CI_AS_KS_WS as the instance default collation (master, tempdb databases).

http://support.microsoft.com/kb/2008668

So  what if you have a different collation and you are in an unspported situation. This article will provide you to change Database Collation to supported  collation of “Latin1_General_CI_AS_KS_WS”

First If you have also different collation for the SQL instance (for master, tempdb databases) and some of other databases which are not SharePoint related will require this you have to setup and install another SQL Server Instance with correct collation . After that you can use database attach method to transfer SharePoint Content Database to new SQL Server. Moving SharePoint Farm to another SQL Server instance is out of this article scope. In this article we talked about only one content database have different collation from supported Latin1_General_CI_AS_KS_WS and assume that for the SQL instance (for master, tempdb databases) you have correct configuration.

You wann check following articles
Setting and Changing the Server Collation
http://technet.microsoft.com/en-us/library/ms179254(v=SQL.100).aspx

 

1) Don’t Forget to backup your original Content Database for any further problems. and you need a maintenance time to make this operation .For minimize outage you may change the problematic Content Database in “Read Only” Mode. We always suggest to test this in a test enviroment before appling to any Production Farm.

2) Open your SharePoint Central Administration -> Application Management -> Manage Content Databases section.

3) Create an empty new Content Database for  any web Application and deattach this content database after you have created.

For more information about Add a content database (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/cc825314(v=office.14).aspx

Attach or detach content databases (SharePoint Server 2010) please check following article.
http://technet.microsoft.com/en-us/library/ff628582(v=office.14).aspx

4) Usually SharePoint 2010 creates new content databases with correct collation “Latin1_General_CI_AS_KS_WS” . If not, you need to rebuild another SharePoint Farm or create a content database on another SharePoint server which must be same build level with correct collation than move this content database to your actual farm by using your SQL knowledge.

We are doing this ; because we want to sure that we have a content database with correct Schema.
You can check Collation in SQL Server Management Studio by right clicking on Content Database and selecting “Properties”

pic9

5) Clear all table data in newly created content Database . You can use following script to generate Truncate Tables commands:

Use WSS_Content_New
Declare @TableName as nvarchar(max)
Declare tablecursor cursor for select name from sys.objects where type = ‘U’ order by name
open tablecursor
Fetch next from tablecursor into @TableName
WHILE @@FETCH_STATUS =0
BEGIN
Print ‘Truncate Table ‘ + @TableName
Fetch next from tablecursor into @TableName
END

6) Copy/Paste Generated commands to another SSMS window and run for newly created content database (In example we are using “WSS_Content_New”). If you do it correctly all the data in new content database should be cleared. (Don’t Delete Tables or Don’t try to delete and recreate tables by yourself. We need to clear data in tables only)

7) Click  Start-> All Programs -> Microsoft SQL Server 2008R2 -> “Import and Export Data (32 bit)”

8) Select your SQL Server and Old Content Database as Source

pic1

9) Select new content database as target.

pic2

10) Select “Copy data from one or more tables or views”
Pic3

11) Select each time for 5 tables until you complete all tables. It will provide you detect copy errors (you will get some.)
Do not select any VIEWS !!! only tables
pic4

12 ) For Each Selected Tables apply following . Mark the tables one by one and click Edit Bindings.

Pic5

13) Select “Delete Rows in destination table” and select “Enable identity insert” . (Dont forget to do this all selected 5 tables)

Pic6

14 ) Some of the tables has calculated columns. You need to do some extra operation for tables which contains “Computed Columns” . For Example “AllDocs” Table.
For more information about “Computed Columns” please check following article.
http://msdn.microsoft.com/en-us/library/ms191250(v=SQL.105).aspx

You can find these kind of tables by checking their “create” scripts.

pic8

For example : In AllDocs Table;

CREATE TABLE [dbo].[AllDocs](

[Id] [uniqueidentifier] NOT NULL,

[SiteId] [uniqueidentifier] NOT NULL,

[DirName] [nvarchar](256) NOT NULL,

[LeafName] [nvarchar](128) NOT NULL,

[WebId] [uniqueidentifier] NOT NULL,

[ListId] [uniqueidentifier] NULL,

[DoclibRowId] [int] NULL,

[Type] [tinyint] NOT NULL,

[SortBehavior] [tinyint] NOT NULL,

[Size] [int] NULL,

[ETagVersion]  AS (case when [InternalVersion] IS NULL then NULL else ([InternalVersion]+[BumpVersion]*(256))/(256) end),

[EffectiveVersion]  AS (case when [InternalVersion] IS NULL then NULL else [InternalVersion]+[BumpVersion]*(256) end),

[InternalVersion] [int] NULL,

[BumpVersion] [tinyint] NOT NULL,

[UIVersion] [int] NOT NULL,

[Dirty] [bit] NULL,

[ListDataDirty] [bit] NOT NULL,

[CacheParseId] [uniqueidentifier] NULL,

[DocFlags] [int] NULL,

[ThicketFlag] [bit] NULL,

[CharSet] [int] NULL,

[ProgId] [nvarchar](255) NULL,

[TimeCreated] [datetime] NOT NULL,

[TimeLastModified] [datetime] NOT NULL,

[NextToLastTimeModified] [datetime] NULL,

[MetaInfoTimeLastModified] [datetime] NULL,

[TimeLastWritten] [datetime] NULL,

[DeleteTransactionId] [varbinary](16) NOT NULL,

[SetupPathVersion] [tinyint] NOT NULL,

[SetupPath] [nvarchar](255) NULL,

[SetupPathUser] [nvarchar](255) NULL,

[CheckoutUserId] [int] NULL,

[CheckoutDate] [datetime] NULL,

[CheckoutExpires] [datetime] NULL,

[VersionCreatedSinceSTCheckout] [bit] NOT NULL,

[LTCheckoutUserId]  AS (case when ([DocFlags]&(32))=(32) then [CheckoutUserId]  end),

[VirusVendorID] [int] NULL,

[VirusStatus] [int] NULL,

[VirusInfo] [nvarchar](255) NULL,

[MetaInfo] [dbo].[tCompressedBinary] NULL,

[MetaInfoSize] [int] NULL,

[MetaInfoVersion] [int] NOT NULL,

[UnVersionedMetaInfo] [dbo].[tCompressedBinary] NULL,

[UnVersionedMetaInfoSize] [int] NULL,

[UnVersionedMetaInfoVersion] [int] NULL,

[WelcomePageUrl] [nvarchar](260) NULL,

[WelcomePageParameters] [nvarchar](max) NULL,

[IsCurrentVersion] [bit] NOT NULL,

[Level] [tinyint] NOT NULL,

[CheckinComment] [nvarchar](1023) NULL,

[AuditFlags] [int] NULL,

[InheritAuditFlags] [int] NULL,

[DraftOwnerId] [int] NULL,

[UIVersionString]  AS ((CONVERT([nvarchar],[UIVersion]/(512),0)+’.’)+CONVERT([nvarchar],[UIVersion]%(512),0)),

[ParentId] [uniqueidentifier] NOT NULL,

[HasStream]  AS (case when [Type]=(0) AND ([DocFlags]&(256))=(256) AND [SetupPath] IS NULL OR [SetupPath] IS NOT NULL AND ([DocFlags]&(64))=(64) then (1) else (0) end),

[ScopeId] [uniqueidentifier] NOT NULL,

[BuildDependencySet] [varbinary](max) NULL,

[ParentVersion] [int] NULL,

[ParentVersionString]  AS ((CONVERT([nvarchar],[ParentVersion]/(512),0)+’.’)+CONVERT([nvarchar],[ParentVersion]%(512),0)),

[TransformerId] [uniqueidentifier] NULL,

[ParentLeafName] [nvarchar](128) NULL,

[IsCheckoutToLocal]  AS (case when ([DocFlags]&(512))=(512) then (1) else (0) end),

[CtoOffset] [smallint] NULL,

[Extension]  AS (case when charindex(N’.’,[LeafName] collate Latin1_General_BIN)>(0) then right([LeafName],charindex(N’.’,reverse([LeafName]) collate Latin1_General_BIN)-(1)) else N” end),

[ItemChildCount] [int] NOT NULL,

[FolderChildCount] [int] NOT NULL,

[FileFormatMetaInfo] [varbinary](max) NULL,

[FileFormatMetaInfoSize] [int] NOT NULL,

[FFMConsistent] [bit] NULL,

[ContentVersion] [int] NOT NULL,

[ListSchemaVersion] [int] NULL,

[ClientId] [varbinary](16) NULL,
The Bold Columns are Computed Columns for AllDocs table

  • ETagVersion
  • EffectiveVersion
  • LTCheckoutUserId
  • UIVersionString
  • HasStream
  • ParentVersionString
  • Extension

15) For Every Computed Column for every Table you have to select “Destination” value as “<ignore>”
Pic7

16) After Mapping Configuration Click Next and Finish and if everything is ok SQL “Import and Export Tool” able to copy data between selected tables.
Note: It may give some warnings than you can ignore them . But if it is giving any error go back and check all configuration again.
Note: All Rows must me transfered dont forget to check row counts.Any missing row will make your System unstable and unsupported state.

17) Copy all tables’ data to new content database. Check all tables row counts after you finished the operation again.
Important Note:You have to be very Careful,  Any error , any missing row or any missing column data  happens that indicates you could not have a supported database !!!

18) Deattach original content database from Web Application
19) Attached newly created content database to Web Application and test that everything is working fine.
Attach or detach content databases (SharePoint Server 2010) please check following article.
http://technet.microsoft.com/en-us/library/ff628582(v=office.14).aspx

 

Important Note: The data have already been transfered from old database will not be changed. Thats mean until this operation old data will remain in old collation. New added Data will applied as the new collation format. So dont expect that the if you have some character problems causing by unsupported collation will not be fixed by this operation. And Always test your System  for a test environment before doing any action on Production.

To being on the safe side you may try create one more  content database and after you complete change collaction operation , and move your all site collections  by Move-SPSite Command.
http://technet.microsoft.com/en-us/library/ff607915.aspx

 

How to trim Audit Logs in SharePoint 2007&2010

Auditing enables administrators to keep a reliable log of what is happening with important content on a site collection. Administrators are able to retrieve the entire history of actions taken by a particular user and can also retrieve the entire history of actions taken during a particular date range.In SharePoint Content Database we have a table named  AuditData. This table  stores audit logs when “Auditing” enabled in Site or List Libraries. But once you enabled “Auditing” this table size will growth continously and  it will consume your storage space in your SQL Server quickly.At that point you’ll need to delete older audit logs which is stored in your content database

For SharePoint 2007 we have a STSADM command for clearing audit data for maintanence purporse (but it is depreciated in SharePoint 2010)

So clearing old auditlogs you may fallow

1)      Open a Command Prompt as Administrator Privilegdes in your one of SharePoint Server
2)      Change path to
cd c:\program files\common files\microsoft shared\web server extensions\12\Bin
3)  Run fallowing command change it as your content database name
stsadm –o trimauditlog –date 20120822 –databasename MyContentDatabaseName
Important: The audit entries before given date are permanently deleted after this operation has run

This operation is not done automatically by SharePoint 2007 (it is by design) .This responsibility has assigned to System Administrators for maintenance and shoud be done manually by periodically.For more information about trimauditlog you can check: http://technet.microsoft.com/en-us/library/cc706879.aspx

For SharePoint 2010  we have a dedicated TimerJob for doing this operation .Default schedule is set by monthly.

1)      Go to your Central Administration -> Monitoring -> Review Job definitions

2)      You can see in picture every site has own Audit Log Trimming Job. Select correct job for your actual site
3)      Click “Run Now” button.

I would like to inform you about someting when you run this timer job it will use the value of retention (for example 3 )  which you set in Site Settings-> Site Collection Audit Settings .
Even you set the “Automatically trim the audit log for this site” yes and set retention for 3 days (like in example) . the logs will not be deleted from Content Database until “Audit Log Trimming” timer job is run.After timer job runs the logs until retention value ( 3 days in example) will be deleted.

What if you set “Automatically trim the audit log for this site” as No . How could you clear old logs ?

There is another way to do it by using PowerShell . you can able to give here a date as parameter like stsadm command.

1)      Run SharePoint 2010 Powershell Console by administrator priviledges .
2)      Type fallowing commands:
$site = Get-SPSite http://yoursitecollectionURL
$date = Get-Date “22/08/2012”
#(You need to check date format , type $date and press enter)
$date
#Result like:  22 August 2012 00:00:00
#(and check the date is correct because it can be changed by regional settings. if date is in correct format )
$site.Audit.DeleteEntries($date)

You can fallow whats happening in background by tracing ULS logs in real time. And you can learn how many records are deleted.

//See you next article .

Import ULS log files to SQL Server database for Sharepoint 2010

Sometime you may need to import ULS log files to SQL server . It can be done with several ways by using SQL Server import tools or import from an Excel file to SQL  server. Or you can use fallowing free open source windows application tool. With this tool it can be done with more quickly . By multy-threading and bulk insert architecture feature provides you that ULS logs contains total 1.500.000 row  can be upload in  apprx. 2 minutes. (as you know it depends on your configuration and hardware)

Than you can query SQL tables as you wish : Fallowing query will return execution time over 1 sec for SPMonitoring event records.

select * from WFE1
where [ExecutionTime] > 1000 and [Eventid] =’b4ly’
order by [ExecutionTime] desc

“All These source codes are freeware and is provided on an “as is” basis without warranties of any kind, whether express or implied, including without limitation warranties that the code is free of defect, fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of the code is with the end user

For Download  ULStoSQLImporter v1.0  for Sharepoint 2010:
http://spstools.codeplex.com/releases/view/93135

SharePoint 2010 Configuration Database has a huge LOG File size problem.

You detected that SharePoint 2010 configuration database has a big LOG file size and you have  a low disk space problem.

This is happening because SharePoint Configuration Database are set to full recovery mode by default. Full recovery mode does not auto shrink / truncate the log files on a full backup. In this mode System Admins are responsible to maintaining getting Transactional Log Backups and Shrinking Log files. If Simple recovery mode is selected when you get a full backup the log files will be shrinked. But Microsoft does not recommend changing to simple recovery mode in a production environment.

You can check the Recovery mode by using SQL Server Management Studio (SSMS) by selecting “SharePoint Config” database (Right Click)-> Properties -> Options.

Full recovery mode allows the SQL admins to backup the transcation logs incrementally, simple recovery mode does not allow this and only allows full or differential backups, no transaction log backups. And Full Recorvery mode has provide you to recovery at specific time manner.

For more information about SQL Server Recovery Models
http://msdn.microsoft.com/en-us/library/ms189275.aspx

SharePoint Configuration Database is the heart of a SharePoint farm and according to size of farm lots of transactions happens.Especially every Timer Job run can cause high transactions on SharePoint Configuration Database. And There are nothing to prevent or reduce this logs rate by making any changes on SharePoint Side.

So for production enviroments SQL Admin should make a recovery and maintenance plans for preventing unnecessary growth of log files. It should be done by getting frequently Transactional Log backups and shrinking transaction log files.

The frequency has changed  system by system. It could be done 30 min to 1 day frequency, according to your transaction log growth.To prevent this kind of problem, make automize or manuelly get your Transactional backups and shrink the log files for a defined period.

For Backup Transaction Log Please check fallowing artcile:
http://msdn.microsoft.com/en-us/library/ms179478.aspx

For Shrink Transaction Log file:
http://msdn.microsoft.com/en-us/library/ms190757.aspx