SharePoint Dragons

Nikander & Margriet on SharePoint

Smartphone simulators

To test the mobile view functionality in SharePoint 2013, we needed some smartphone emulators. Here’s what we came up with…

This web-based iPhone simulator is quite nice: http://iphone4simulator.com/

For the iPhone, there’s also a commercial product which is easy to use and has a trial version. It can be found at http://www.electricplum.com/studio.aspx

For the Windows Phone, you need to download the SDK at http://dev.windows.com/en-us/develop/download-phone-sdk , which uses Visual Studio (2012) and the presence of Hyper-V. This emulator itself can’t run on a virtual machine (although we’ve seen posts that say VMWare 9 can do it).

For Android, check out http://developer.android.com/sdk/index.html

Browser shots

Great URL for browser compatibility testing: http://browsershots.org/.

Project Adam

Project Adam is a new deep-learning system modeled after the human brain that has greater image classification accuracy and is 50 times faster than other systems in the industry. Watch this video: http://research.microsoft.com/apps/video/default.aspx?id=220709. You should also take a look at: http://research.microsoft.com/en-us/news/features/dnnvision-071414.aspx.

SSIS Package In Visual Studio 2012: Designer View suddenly gone

Have you ever experienced that the Designer View of an SSIS package in Visual Studio is suddenly completely empty while all the underlying XML is all gone? It had us puzzled, but it was solved by closing all packages in VS (just click the X’s) and then re-opening the package.

Apparently the UI of SSIS is not all fine and dandy, we found this post to be of interest too: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3dc85f3c-59b7-4671-9894-e12ef2680d27/ssis-package-when-opened-in-designer-shows-nothing-because-it-is-scrolled-to-bottom-right-where?forum=sqlintegrationservices.

SPServices

An interesting library based on jQuery that makes working with SharePoint’s web services easier: http://spservices.codeplex.com/.

Wiki World Cup

Trying to follow the success of the Dutch soccer team, Margriet’s latest TechNet Wiki blog post proposes a new team line-up: http://blogs.technet.com/b/wikininjas/archive/2014/06/29/wiki-world-cup.aspx

Kill table lock

Found a table that was locked during an ETL process. Retrieved the culprit session via:

select * from sys.dm_exec_requests cross apply sys.dm_exec_sql_text([sql_handle]) where blocking_session_id = 0

Then, killed it like this:

KILL([session id])

And the ETL process is free to try again!

Setting another application pool for PowerPivot using PowerShell

The title says it all:

# Change application pool for specific service application
$appPool = Get-SPServiceApplicationPool | where { $_.Name -eq “SharePoint – PowerPivot Application Pool” }

$serviceApp = Get-SPServiceApplication -Name “ODH PowerPivot Service Application”
$serviceApp.ApplicationPool = $appPool
$serviceApp.Update()

Setting up a sharepoint 2013 dev environment

We’ve updated the Reporting Services (SSRS) section of this TechNet Wiki article: http://social.technet.microsoft.com/wiki/contents/articles/15289.sharepoint-2013-creating-a-development-environment.aspx , so check it out!

State of SharePoint Statistics

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:

clip_image001

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.

Follow

Get every new post delivered to your Inbox.

Join 629 other followers