Using Pivot Table to determine timer job durations in SharePoint 2010

Video length: 3:27

Video description: This video walks through generating a Pivot table in Excel that exposes timer job durations and presents them by server, web application, and timer job names from the WSS_Logging Database of a SharePoint 2010 Farm.

Documented video steps below:

To Create a Pivot Table from the Logging Database:

  • Launch Microsoft Excel
  • Switch to the Data tab
  • Under Get Exernal Data, select From Other Sources and select from SQL Server
  • Next to Server name type the host name of your SQL Server instance that houses your WSS_Logging Database
  • On the next screen, choose WSS_Logging under the Select the database that contains the data you want
  • From the grid that appears below with a list of all the tables, scroll a bit and select the TimerJobUsage_PartionXX table, where XX can be found by:
  • To retreive the partition table number that contains the data you want, open SQL Server Managment Sutio, browse to the WSS_logging database and type in the following query:
Use WSS_Logging
Select * from configuration where configname = ‘max partition id – TierJobUsage’ 
  • The ConfigValue number you get back from the query is the table partition number you need to choose from the Select Databases and Tables Data connection wizard back in Excel
  • Finish the wizard
  • On the Import Data popup screen, choose PivotTable Report and click OK
  • From the PivotTable Field list select the checkboxes next to “MachineName” then “WebApplicationName” and then “JobTitle”.
  • Also from the PivotTable Field List Drag the “Duration” field and drop it under Values
  • Review your results

Leave a Comment