Technical: Microsoft – SharePoint – v2100 – List – Management – Deletion

Technical: Microsoft – SharePoint – v2010 – List – Management – Deletion

Introduction

I have a little assignment; and the assignment is to copy a SharePoint List from an old server to a new one.

Knowing me,  I will likely make a lot of false starts and so let me sure I will be able to remove half-way created lists.

Steps

Navigate to the Site Home

Let us navigate to our Site’s home.

Thank goodness that SharePoint has Navigational panel for sojourning back to the Site’s home page.

Here is our top panel.

TopPanel

At the top of the page we have the little folder Navigation icon folderNavigation

We will use that icon to take us to the Site’s Home page.

Access List Management

Once on the Site’s home page, we will access the List’s management tools.

Pasted below is our Site’s home page.

HomePage

The List’s page is accessible by moving our glance to the left sub-panel and clicking on the Lists hyperlink.

Select List

Here is the list of our currently established lists:

List - StandardView - Lists (include friends)

Because of the Site Template we chose when creating our “Site Collection”, we have a few standard lists:

  • Announcements
  • Calendar
  • Links
  • Tasks

The one list that we created is the Friends List.

And, so that is the one we will offer up.

Navigation Tools

More on Navigational Tools.  I think it is important to know where one is in SharePoint. For the sake of consistency and uniformity most of the screens have a familiar and standard look.

So to know where you really are, I will suggest two ploys:

  • Keeping an eye on your URL box
  • Making note of the middle panel

When on the List View panel, our URL reads http://<sharepointserver>:13201/_layouts/viewlsts.aspx.  The important notifier is the document tag that reads viewlsts.aspx.

And, the middle panel reads:

AllSiteContent

Which to me says that the screen is displaying all sub-sites, lists, and libraries.

List – All Items

Once we chose to go with the Friend’s List, here is where we ended up.

List - Friends - AllItems

Our guide is:

In case we have contents for our lists, they will be noted and listed on our right panel.

List – All Items – Management

To be able to manage the settings on our list, we have to click on the “List Tools/List” icon.

Once we do so, we will see a screen that resembles the screen shot below:

List - Friends - AllItems - Management

List Settings

To manage our List Settings, we will aptly click on the “List Settings” icon.

Upon clicking on the List Settings icon, we are taken to the List Settings page.

List - Friends - ListSettings

 

And, I think this is where we want to be:

We see familiar options, such as:

  • Title, description, and navigation
  • Versioning Settings
  • Advanced Settings
  • Validation Settings
  • Delete this list
  • Save list as template
  • Permissions for the list
  • Workflow Settings
  • RSS Settings

Obviously, our choice it to go with the “Delete this list” choice.

Delete List

Once the “Delete this List” choice is made, the confirmation box is offered:

Are you sure you want to send this list to the site Recyle Bin?

 AreYouSureYouWantToSendThisListToTheSiteRecycleBin

And, we affirmative say OK.

All Site Content

Once the List is removed, we are returned to the “All Site Content” page.

List - Friends - AllSiteContent

Conclusion

Yes, should have read the manual.  But, unlike my last few posted and best left private posts, did not pay bitterly for not reading the manual.

Now on to reviewing the steps for exporting and importing SharePoint Lists.

References

Technical: Microsoft – SharePoint 2010 Administration Toolkit – SharePoint Diagnostic Studio – Error – Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime

Technical: Microsoft – SharePoint 2010 Administration Toolkit – SharePoint Diagnostic Studio – Error – Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime

Background

A week ago or so, I downloaded and installed a fresh copy of Microsoft SharePoint 2010 Foundation Services.

Upon installation everything worked well. But, it appears that as I played around with stsadm, I possibly messed up my install.

Introduction

As I tried to recover from the problem stated above, one of the tools that I discovered on the Net is Microsoft SharePoint 2010 Administration Toolkit; and the main component from that kit is SharePoint Diagnostic Studio.

In this post, I will list a specific problem we ran into upon trying to use that tool on an MS Windows 2012 box.

Error Message

Upon targeting the SharePoint Diagnostic Studio against my local MS Windows 2012 box, I am getting the message stated below:


Microsoft SharePoint is not supported with version 4.0.30319.18051 of the Microsoft .Net Runtime.

So it seems the that I have to get the Application to target a lower level version of Microsoft .Net:

Possible Fixes

Possible Fixes – Application Configuration File

To do so, I tried creating configuration files:

The configuration files will be placed in our target folder for “SharePoint 2010 Administration ToolKit”.  In our case, the target folder is “E:\Program Files\Microsoft\SharePoint 2010 Administration Toolkit”.

So we create a file that looks like the one below.  Our targeted file is named SPDIAG.EXE, and we will be adding .CONFIG to it and so we end up with SPDIAG.EXE.CONFIG.

The contents of our App Config file is shown below:

AppConfig

The key elements are that we un-checked v4.0, and list v2.0x as the lone supported and required runtime version.

But, still getting the same error. And, so I am getting a bit un-convinced that I am on the right path.  And, I really in need of a miracle here.

SysInternals – Process Explorer

Let us try out SysInternal’s Process Explorer and see what it says about our Application’s running environment.

Please start Process Explorer as a Administrator

Once SysInternal’s Process Explorer is up, look for the SPDIAG.EXE process.

Once you focus in on SPDIAG.EXE, you can transverse each tab and get specific sets of data:

SPDIAG.EXE – Properties – Image

ProcessExplorer-Image

SPDIAG.EXE – Properties – .Net Assemblies

ProcessExplorer-DotNetAssemblies

Explanation:

  • On the left tab, the Structure column shows that we are using CLR v2.0.50727
  • And, the right tab, specifically the Path column shows that the files are being sourced from “C:\Windows\assembly\GAC_64\mscorlib\2.000_ ” and “C:\Windows\assembly\NativeImages_v2.0.50727_64”

Conclusion:

So though SPDIAG is target CLR v2.x, we are still failing….

SysInternals – Process Explorer

Again running as an Administrator, launch Process Explorer and filter our tangential processes.

Here are pertinent events:

  • Operation:- RegOpenKey\
    Path :- HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\powershell.exe
  • Operation :- ProcessCreate
    Path :- C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
    Command line: powershell  -command “& {& ‘C:\Users\<computer-name>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ <computer-name>}”
  • Operation :- ProcessStart
    Path :-
    Command line:
    powershell  -command “& {& ‘C:\Users\<username>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ COMFORT}”
    Environment :

    
    =C:=C:\Windows\System32
    	=E:=E:\Program Files\Microsoft\SharePoint 2010 Administration Toolkit
    	ALLUSERSPROFILE=C:\ProgramData
    	APPDATA=C:\Users\<username>\AppData\Roaming
    	asl.log=Destination=file
    	CommonProgramFiles=C:\Program Files\Common Files
    	CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
    	CommonProgramW6432=C:\Program Files\Common Files
    	COMPUTERNAME=<computer-name>
    	ComSpec=C:\Windows\system32\cmd.exe
    	FP_NO_HOST_CHECK=NO
    	HOMEDRIVE=C:
    	HOMEPATH=\Users\<username>
    	LOCALAPPDATA=C:\Users\<username>\AppData\Local
    	LOGONSERVER=\\<AD Server>
    	NUMBER_OF_PROCESSORS=2
    	OS=Windows_NT
    
    

Here are the relevant screen-shots:

ImageFileExecutionOptions:

Looking to determine whether an ImageFileExecutionOptions are defined in the registry for PowerShell.exe

ImageFileExecutionOption

Process Create:

Reviewing what command line options are passed to Powershell.exe when the process is being created:

Here they are:

  •  -command “& {& ‘C:\Users\<username>\Documents\SharePoint Diagnostic Studio\Server Extensions\RemoteInstall-ExtendedDiagnosticProviders.ps1’ <computer-name>}”
  • So we have the name of the PowerShell script to invoke — RemoteInstall-ExtendedDiagnosticProviders.ps1

Process Create – Tab – Event

ProcessCreate

Process Create – Tab – Process

ProcessCreate-Process

Process Start:

  • The environment options details all the environment options that are either created a-new or passed along from the invoking-parent’s process

ProcessStart

Conclusion:

From the screen shot above, we can tell that that the lone argument that is passed to PowerShell is the -command argument.

Our required/supported CLR version 2 is not being passed along.

 

Overall Conclusion

As  SharePoint Diagnostic Studio predates MS Windows 2012 it rightfully does not consider the new PowerShell v3.0’s graceful ability to expose PowerShell v2.0 via command line arguments.

In later posts will discuss other failed attempts!

Technical – PowerShell / Microsoft .Net CLR Version

Technical – PowerShell / Microsoft .Net CLR Version

 

Background

A week or so ago, I tried running Microsoft SharePoint 2010 Administration Toolkit on an MS Windows box.

I ran into problems due to the SharePoint diagnostic tool not being compatible with the default PowerShell/.Net Framework combo on the Windows 2012 box.

Here is the first in a series of post on the school yard beating I took.

Introduction

In this post, I will limit our discussion to ensuring that our pre-requisites are installed and functioning.   Those pre-requisites are Microsoft PowerShell and Microsoft .Net v4. and v3.5.

Confirm installation of Microsoft .Net 3.5 & PowerShell 2.0 Engine

Courtesy of (http://technet.microsoft.com/en-us/library/hh849675.aspx), here are the install\confirmation steps:

Add Microsoft .Net 3.5

  • In Server Manager, from the manage menu, select the Add Roles and Features
  • Select the Server and click on the “Add Roles and Features” option
  • On the Installation Type page, select “Role-based or feature-based” installation
  • On the features page, expand the .Net 3.5 Framework features and select .Net Framework 3.5 (includes .Net 2.0 and 3.0)

WindowsDotNetFrameworkInstallation

To add the Windows PowerShell 2.0 Engine feature

  • In Server Manager, from the manage menu, select the Add Roles and Features
  • Select the Server and click on the “Add Roles and Features” option
  • On the Installation Type page, select “Role-based or feature-based” installation
  • On the features page, expand the “PowerShell (installed)” option and select the “Windows PowerShell 2.0 Engine” option

WindowsPowerShellInstallation

Get OS/CLR Version

To get OS/CLR Version within PowerShell, we can try the following commands:

  • [System.Environment]::OSVersion.Version
  • (Get-WmiObject -class Win32_OperatingSystem).Caption
  • $PSVersionTable

The most important for our current discussion is the $PSVersionTable command:


$PSVersionTable

Output:

On MS Windows 2008/R2 box, you will see:

versionInfoOS2008R2Standard

On MS Windows 2012, you will see:

window2012-default

On MS Windows 2012, if your application demands and requires Version 2, then based on the example documented below, add “-version 2” as a command line argument when starting Poweshell

Syntax Screen:

PowerShellCommandLineVariable

So we issue :


powershell -version 2

And, replay the Version and CLR Info we previously mentioned:

PowershellCLR2onMSWindows2012

Summary:

Here is a quick run-down of OS, PowerShell Version’s support along with Microsoft.Net CLR support and thereof backward compatibility.

OS PSVersion CLRVersion CLR Version Backward
Win2008-R2  2.0 2.0.50727.572 {1.0,2.0}
Win2012  3.0 4.0.30319.18051 {1.0,2.0,3.0}
Win2012 (-v2)  2.0 2.0.50727.6407 {1.0, 2.0}

System Info Tools

As said, PowerShell loads Microsoft.Net dll depending on the OS Version and/or command line parameters.

One popular tool for reviewing system information and activities are of-course SysInternals set of tools.

Windows 2012

In the screen-shot above, we initiated PowerShell on an MS Windows 2012, and did not pass in any command-line parameter:

SysInternals-PowerShell-Windows2012-Image

Here are the Microsoft .Net Dlls loaded:

SysInternals-PowerShell-Windows2012-DotNetAssemblies

From the screen-shot above, we can see the following:

  • on the left panel, we can see that we are on CLR v4.0
  • On the right panel, we have the listing on .Net 4.0 native assemblies loaded

Windows 2012 (passed in -version 2.0)

The screen below indicates what things look like when we pass in the “-version 2.0” option; please pay attention to the “command line” section.

SysInternals-PowerShell-Windows2012-with-v2-Image

And, here is CLR Version & DLL Listings

SysInternals-PowerShell-Windows2012-with-v2-DotNetAssemblies

So we are able to confirm that when we pass in the “-version 2.0” option, we get PowerShell to go back in time.

Conclusion

In follow-up posts, I will discuss the actual problems I ran into and the series of fruitless steps I tried to correct it.

References

References – Determine Version#

References – PowerShell 2.0 Engine

Microsoft – SharePoint (v2010) – SharePoint Foundation – Get List of Products Installed along with Version#

Microsoft – SharePoint (v2010) – SharePoint Foundation – Get List of Products Installed along with Version#

Which Products are installed?

Using GUI

  • Launch SharePoint 2010 Central Administration
  • Access the default screen
  • Under “upgrade and migration”, select “Check product and patch installation status”
  • The “Manage Patch Status” screen is displayed
  • On that screen you should be able to view products are installed and their corresponding version#

Using PowerShell

PowerShell Query:


get-SPFarm | select Servers, Name, DisplayName, Status, BuildVersion

PowerShell Output:

get-SPFarm

Explanation:

Or do one better and try out someone else’s code:

  • I like Matthias_Einig (RENCORE AB) code and it is available @ http://gallery.technet.microsoft.com/office/Get-cleartext-and-license-008ee875
  • Please download and extract the compressed file
  • Launch Terminal Mode (Command Line Shell – cmd.exe); preferable as an Administrator
  • Using cd <extracted-foldername>, Change directory to the folder where you placed your files
  • Launch Powershell (powershell.exe)
  • Unfortunately, Matthias’ code is not digitally signed and you will have to tell your system it is OK to run it
  • In Powershell, you indicate so by issuing “Set-ExecutionPolicy unrestricted”
  • Run Matthias’s script (note that you have to enter the file’s full name or if you in that folder, place “.\” prior to your file name
          
            .\PrintSPVersionAndproducts.ps1
            

In our case, we got

MatthiasCode

Conclusion

Yes, it is much easier to use the GUI.  But, you might want to get comfortable with the SharePoint Command-Line Tool (stsadm.exe) and the PowerShell Snapin (Microsoft.SharePoint.PowerShell Snap-In).

And, yes GUI’s quickly and readily convey information in easily accessible forms, but the backplane is where all the instrumentation and hard-lifting occurs.

References

Microsoft – SharePoint – Database – Top Queries

Introduction

Reviewing top N Queries for our SharePoint Databases and as we reviewed each Stored Procedure discovered that for some of the queries we were getting a warning that “column level statistics” are missing.

 

Query for Top Queries

Here is a quick query to identify the top N Queries.


declare @iNumberofQueries int

set @iNumberofQueries = 100

;with cteSummed
    (
        execution_count
      , total_worker_time
      , total_elapsed_time
    )
as
 
    (
       SELECT
          sum(qs.execution_count) as execution_count
        , sum(qs.total_worker_time) AS Total_CPU
        , sum(qs.total_elapsed_time) as total_elapsed_time
       FROM (
 
            select  
					--(@iNumberofQueries)
						qs.*
            from   sys.dm_exec_query_stats AS qs
            --order by qs.total_worker_time DESC
        ) qs    
 
    )
SELECT TOP (@iNumberofQueries)
 
      qs.execution_count
 
    , [executionCount%]
		= (
				(qs.execution_count * 100)
					 / (cteSummed.execution_count)
		  ) 

 
    , Total_CPU
		= qs.total_worker_time
 
    , [totalWorkerTime%]    
		= (
				  (qs.total_worker_time * 100) 
				/ (cteSummed.total_worker_time)
		  ) 

 
    , total_CPU_inSeconds
		 = qs.total_worker_time/1000000 
 
    , average_CPU_inSeconds =
         (qs.total_worker_time/1000000) / qs.execution_count
 
    , qs.total_elapsed_time
 
    , [totalElapsedTime%] 
		= (
				(qs.total_elapsed_time * 100) 
					/ (cteSummed.total_elapsed_time)
		  ) 

 
    , databaseName
		= db_name(st.[dbid])
 
    , [sqlText]
		= st.[text]
 
	, sql_statement
		= 
			(
				SELECT TOP 1 SUBSTRING
				(
					  st.[text]
					, qs.statement_start_offset / 2+1 
					, ( 
						(
							CASE WHEN qs.statement_end_offset = -1   
									THEN (
											LEN
											(
												CONVERT
												(
													  nvarchar(max)
													, st.[text]
												)
											) * 2
										)   
							 	 ELSE qs.statement_end_offset 
							END
						)  - qs.[statement_start_offset]
					) 
					/ 2+1
				)
			) 
    , qp.query_plan
 
FROM
    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
        cross join cteSummed
ORDER BY
    qs.total_worker_time DESC


Screen Shot

topQueries

Object Listing

Database Stored Procedure
Search_Service_Application_DB_xx proc_MSS_GetCurrentRegistryVersion
Bdc_Service_DB_xx proc_ar_ClearAllAccessControlEntriesForMetadataObject
Search_Service_Application_DB_xx proc_MSS_PropagationIndexerGetReadyQueryComponents
WSS_Facilities proc_GetChanges
 Bdc_Service_DB_xx proc_ar_GetMethodInstancesForDataClassWithCount
 WSS_Facilities proc_AppendSiteQuota
 Bdc_Service_DB_xx proc_ar_GetMethodInstancesForDataClassWithCount
 WSS_Facilities proc_SecGetDomainGroupMapData
 WSS_Facilities proc_LogChange
 WSS_Facilities proc_ProcessSiteQuotaForStorageMetricsChanges
 WSS_Facilities proc_ProcessSiteQuotaForStorageMetricsChanges
 WSS_Facilities proc_QMChangeSiteDiskUsedAndContentTimestamp
 WSS_Facilities proc_GetListMetaData
 WSS_Facilities proc_UpdateDiskUsed
 WSS_Facilities proc_CopyDir
 WSS_Facilities fn_IsOverQuotaOrWriteLocked
 WSS_Facilities proc_ListContentTypesInWebRecursive
 WSS_Facilities proc_MapFieldToContentType
 WSS_Facilities proc_MapFieldToContentType

Here is a quick rundown of the Identified Queries

 

Query – proc_MSS_GetCurrentRegistryVersion

  • Query is “SELECT @SqlVar=Value from MSSConfiguration where Name = @LikeKey”
  • The  MSSConfiguration is a small table; in our case 640 records; and so table scan is issued

Query – proc_ar_ClearAllAccessControlEntriesForMetadataObject

  • Index seek against AR_MetadataObject
  • Index seek against AR_MetadataObjectSecurity and Clustered Index Delete against same

Query – proc_GetChanges

  • Checks against Table Value Functions (TVFs) that are based on the EventCache table

Query – proc_LogChanges

  • Insert into the EventCache table

Query – dbo.proc_SecGetDomainGroupMapData

  • Columns With No Statistics: [WSS_Facilities].[dbo].[UserInfo].tp_ID

Query – dbo.proc_ProcessSiteQuotaForStorageMetricsChanges

  • Columns With No Statistics: [WSS_Facilities].[dbo].[StorageMetricsChanges].DocId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[StorageMetrics].DocId

Query – dbo.proc_GetListMetaData

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId

Query – dbo.proc_GetListWebPart

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllWebParts].tp_PageUrlID, [WSS_Facilities].[dbo].[AllWebParts].tp_UserID, [WSS_Facilities].[dbo].[AllWebParts].tp_Level, [WSS_Facilities].[dbo].[AllWebParts].tp_PageVersion, [WSS_Facilities].[dbo].[AllWebParts].tp_IsCurrentVersion
  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId

Query – dbo.proc_GetListWebPart

  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId, [WSS_Facilities].[dbo].[EventReceivers].SolutionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId, [WSS_Facilities].[dbo].[EventReceivers].HostType, [WSS_Facilities].[dbo].[EventReceivers].SolutionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[EventReceivers].WebId, [WSS_Facilities].[dbo].[EventReceivers].HostId

Query – dbo.proc_ListContentTypesInWebRecursive

  • Columns With No Statistics: [WSS_Facilities].[dbo].[AllDocs].DeleteTransactionId
  • Columns With No Statistics: [WSS_Facilities].[dbo].[ContentTypes].Class, [WSS_Facilities].[dbo].[ContentTypes].Scope

Background

MS publication per Best Practice for SharePoint is to turn off “Auto-Create Statistics” on SharePoint databases.

And, so this is to be expected.

MSFT reasoning is that they already have the best indexes in place and so the system should not bother to create new statistics.

System Level Query to identify Queries and Database Tables missing column statistics

Let us quickly inspect our Cached Plan to see which queries and corresponding Database tables and columns are flagged as missing statistics.

Here is one I stole from the .Net but it took me a lot of time to understand and customize for my use:

Instance Level


-- Querying the plan cache for plans that have warnings
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), 
    WarningSearch AS (

			SELECT 
				  qp.query_plan
				, cp.usecounts
				, cp.objtype
				, wn.query('.') AS StmtSimple

			FROM   sys.dm_exec_cached_plans cp

			  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

			  CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(wn)

			WHERE wn.exist('//Warnings') = 1

			AND  wn.exist('//ColumnsWithNoStatistics') =1							
                        AND wn.exist('@QueryHash') = 1

                        )

SELECT
	  ws.query_plan
	, ws.query_plan.query('//Warnings') as Warning
	, ws.query_plan.query('//ColumnsWithNoStatistics') 
              as WarningColumnsWithNoStatistics
	, StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') 
             AS sqlText
	, StmtSimple.value('StmtSimple[1]/@StatementId', 'int') AS StatementId
	, c1.value('@NodeId','int') AS node_id
	, c1.value('@PhysicalOp','sysname') AS physical_op
	, c1.value('@LogicalOp','sysname') AS logical_op
        , ws.objtype
	, c3.value('@Database', 'sysname') as [DatabaseName]
	, c3.value('@Schema', 'sysname') as [Schema]
	, c3.value('@Table', 'sysname') as  [TableName]
	, c3.value('@Column', 'sysname') as [Column]
FROM WarningSearch ws
      CROSS APPLY StmtSimple.nodes('//RelOp') AS q1(c1)
      CROSS APPLY c1.nodes('./Warnings') AS q2(c2)
      CROSS APPLY c2.nodes('./ColumnsWithNoStatistics/ColumnReference') AS q3(c3)

Specific Object Name

It is a bit expensive to dig into the Query Plan and so you might want to be a bit selective regarding which objects you will like to inspect.


-- Querying the plan cache for plans that have warnings
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @DatabaseName sysname
declare @SchemaName sysname
declare @TableName sysname

set @DatabaseName = '[Sharepoint]'
set @SchemaName = '[dbo]'
set @TableName = '[UserInfo]'

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'), 
    WarningSearch AS 
    (

	SELECT 
		  qp.query_plan
		, cp.usecounts
		, cp.objtype
		, wn.query('.') AS StmtSimple

	FROM   sys.dm_exec_cached_plans cp

		CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

		CROSS APPLY qp.query_plan.nodes('//StmtSimple') AS p(wn)

	WHERE wn.exist('//Warnings') = 1

	AND  wn.exist('//ColumnsWithNoStatistics') =1							

        AND wn.exist('@QueryHash') = 1

	and  qp.query_plan.exist
               ('//ColumnReference[@Database = sql:variable("@DatabaseName")]') = 1         

	and  qp.query_plan.exist
              ('//ColumnReference[@Schema = sql:variable("@SchemaName")]') = 1                                                        							
	and  qp.query_plan.exist
              ('//ColumnReference[@Table = sql:variable("@TableName")]') = 1                            

   )

SELECT top 1000
        ws.query_plan
      , ws.query_plan.query('//Warnings') as Warning
      , ws.query_plan.query('//ColumnsWithNoStatistics') 
              as WarningColumnsWithNoStatistics
      , StmtSimple.value('StmtSimple[1]/@StatementText', 'VARCHAR(4000)') AS sqlTtext
      , StmtSimple.value('StmtSimple[1]/@StatementId', 'int') AS StatementId
      , c1.value('@NodeId','int') AS node_id
      , c1.value('@PhysicalOp','sysname') AS physical_op
      , c1.value('@LogicalOp','sysname') AS logical_op
      , ws.objtype
      , c3.value('@Database', 'sysname') as [DatabaseName]
      , c3.value('@Schema', 'sysname') as [Schema]
      , c3.value('@Table', 'sysname') as  [TableName]
      , c3.value('@Column', 'sysname') as [Column]
FROM WarningSearch ws
       CROSS APPLY StmtSimple.nodes('//RelOp') AS q1(c1)
       CROSS APPLY c1.nodes('./Warnings') AS q2(c2)
       CROSS APPLY c2.nodes('./ColumnsWithNoStatistics/ColumnReference') AS q3(c3)
where  c3.exist('//ColumnReference[@Database = sql:variable("@DatabaseName")]') = 1
and  c3.exist('//ColumnReference[@Schema = sql:variable("@SchemaName")]') = 1
and  c3.exist('//ColumnReference[@Table = sql:variable("@TableName")]') = 1

Summary

Does the warning “Column with no statistics” matter?  Probably not in this case, as SharePoint tables, indexes, and queries are canned solutions heavily Q/Aed by the Vendor, Microsoft in this case.

For other systems and databases I will definitely look deeper; especially now that I have stolen\”preped” a code for seeing how rampant it is.

Placeholder

Will come back and credit the source for SQL Codes I per-used.

References

References – Microsoft – SQL Server – Database – Auto/Create Statistics & Auto-Update Statistics

Microsoft – SQL Server Provisioning for MS SharePoint

Background

Goggled for things to do and not to do when setting up SharePoint Databases.  And, found a good list here.

I was spirited to script out SQL Scripts against some of the items listed.

Items Identified

Here is a quick list of items identified:

  • Set “max degree of parallelism” to 1
  • Set database files to auto-grow by actual values, rather than by percentile values
  • Make sure that you ‘re running Database Consistency against all databases
  • Ensure that ‘Auto Create Statistics’ is turned off at the Database Level
  • Make sure that you are successfully defragmenting indexes; especially dbo.AllDocs

Items Discussion

For SharePoint SQL Instance, Max degree of parallelism should be set at 1

  • Essentially, no parallelism
  • The reasons are probably due to the fact that Microsoft SharePoint is an OLTP Application and that the queries are likely well known and predictable enough in terms of Resource Requirement; and that comparative to less understood systems they are less likely to benefit from being broken into parallel tracks

Script to verify “Max degree of Parallelism”:


set nocount on
go

declare @runValueShowAdvancedOptions     int

declare @runValueMaxDegreeofParallelism  int
declare @runValueDatabaseBackupCompressionDefault  int
declare @runValueIndexFillFactor  int

declare @message   sysname

if OBJECT_ID('tempdb..#configure') is not null
begin

	print 'Dropped remnant table (#configure)'
	drop table #configure 

end

create table #configure 
(
	  [serverName]  sysname null default
                  cast(serverproperty('servername') as sysname)
	, [name]        sysname
	, [minimum]     int
	, [maximum]     int	
	, [configValue] int
	, [runValue]    int
)

insert into #configure 
(
	  [name]
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
)
exec sp_configure 'show advanced options'

select top 1 
	  @runValueShowAdvancedOptions = [runValue]
from   #configure 
where  [name] = 'show advanced options'

if (
	   (@runValueShowAdvancedOptions is null) 
	or (@runValueShowAdvancedOptions = 0)
   )
begin

	print 'set show advanced options ... '

	exec sp_configure 'show advanced options',1

	reconfigure with override

end

insert into #configure 
(
	  [name]
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
)
exec sp_configure 'max degree of parallelism'

insert into #configure 
(
	  [name]
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
)
exec sp_configure 'fill factor (%)'

insert into #configure 
(
	  [name]
	, [minimum]
	, [maximum]
	, [configValue]
	, [runValue] 
)
exec sp_configure 'backup compression default'

select *
from   #configure 

select top 1 
	@runValueMaxDegreeofParallelism = [runValue]
from   #configure 
where  [name] = 'max degree of parallelism'

select top 1 
	@runValueDatabaseBackupCompressionDefault = [runValue]
from   #configure 
where  [name] = 'backup compression default'

select top 1 
	@runValueIndexFillFactor = [runValue]
from   #configure 
where  [name] = 'fill factor (%)'

drop table #configure 

if (@runValueMaxDegreeofParallelism = 1) 
begin
       set @message = 'max degree of parallelism is good @ 1'
end
else		
begin
     set @message = 'max degree of parallelism is no good @ ' 
	AST(@runValueMaxDegreeofParallelism as sysname)
end

print @message

if (@runValueDatabaseBackupCompressionDefault = 1)
begin
	set @message = 'backup compression default @ 1'
end
else		
begin
	set @message = 'backup compression default is no good @ ' 
			+ CAST(@runValueDatabaseBackupCompressionDefault as sysname)
end

print @message

if (@runValueIndexFillFactor = 80)
begin
	set @message = 'Index Fill Factor % is good @ 80'
end
else		
begin
	set @message = 'Index Fill Factor % is no good @ ' 
			  + CAST(@runValueIndexFillFactor as sysname)
			  + case
				when @runValueIndexFillFactor = 0 then '/100'
				else ''
			    end
end

print @message

go

For SharePoint Databases, database files should be set to auto-growth by actual values and not percentile


select 
     tblMasterFile.name

   , case
	when  (db_name(tblMasterFile.database_id) 
                not  in ('master', 'tempdb', 'model', 'msdb') ) 
	      then 'Yes'
	else  'No'
     end as 'IsSharePointDB'	

   , tblMasterFile.growth  

  , case
	when  (tblMasterFile.is_percent_growth =1) then 'Yes'
	else  'No'
    end as 'IsPercentGrowth'

from   sys.master_files tblMasterFile

where  db_name(tblMasterFile.database_id) 
         not in ('master', 'tempdb', 'model', 'msdb')

order by database_id

For Sharepoint Databases, database should be configured as:

  • Auto-Create Statistics should be off
  • Database Collation Name should be set to Latin1_General_CI_AS_KS_WS

select 
       tblDatabase.name
     , case
	  when  (tblDatabase.name not in ('master', 'tempdb', 'model', 'msdb') )
                   then 'Yes'
	  else  'No'
       end as 'IsSharePointDB'

     , tblDatabase.is_auto_create_stats_on

     , case
	  when  (tblDatabase.is_auto_create_stats_on =1) then 'Yes'
		else  'No'
      end as 'IsCreateAutoStatisticsOn'		

    , tblDatabase.collation_name

   , case
 	when  (tblDatabase.collation_name ='Latin1_General_CI_AS_KS_WS') then 'Yes'
	else  'No'
    end as 'IsDBCollationSetProperly'		

from   sys.databases tblDatabase

where  (tblDatabase.name not in ('master', 'tempdb', 'model', 'msdb') )

and  (

	   (tblDatabase.is_auto_create_stats_on = 1)

	or (tblDatabase.collation_name != 'Latin1_General_CI_AS_KS_WS')

    )	

order by 

	  tblDatabase.is_auto_create_stats_on desc
	, tblDatabase.database_id

SQL Maintenance

Follow Best Practice by scheduling, running, and reviewing Database Consistency Checks

I really like Sankar Reddy’s comment on Jonathan Kehayias blog post:

When did DBCC CHECKDB last run on my databases?
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/28/when-did-dbcc-checkdb-last-run-on-my-databases.aspx

 

And, I am publicly stealing it here:


CREATE TABLE #temp 
(
      Id INT IDENTITY(1,1),
      ParentObject VARCHAR(255),
      [Object] VARCHAR(255),
      Field VARCHAR(255),
      [Value] VARCHAR(255)
)

INSERT INTO #temp
EXECUTE SP_MSFOREACHDB'DBCC DBINFO ( ''?'') WITH TABLERESULTS';

;WITH CHECKDB1 AS
(

   SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 
   FROM #temp 
   WHERE Field IN ('dbi_dbname')
)

 ,CHECKDB2 AS 
  ( 
       SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 
       FROM #temp 
       WHERE Field IN ('dbi_dbccLastKnownGood')

)      

SELECT 
          CHECKDB1.Value AS DatabaseName
        , CHECKDB2.Value AS LastRanDBCCCHECKDB
FROM CHECKDB1 
       JOIN CHECKDB2
           ON rn1 =rn2

DROP TABLE #temp

Check for Index Fragmentation, especially against the dbo.AllDocs table


set nocount on;

DECLARE @dbName sysname
DECLARE @objectName sysname
DECLARE @objectNameFull sysname
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

DECLARE @id int;
DECLARE @iNumberofObjects INT;

if object_id('##tbData') is not null
begin
	drop table ##tbData 
end

create table ##tbData 
(
	id int not null identity(1,1),
	DatabaseName sysname,
	objectName sysname,
	ObjectKind sysname
)

EXEC sp_Msforeachdb "use [?];
       insert ##tbData select db_name(),so.name,so.xtype from sysobjects so where so.name = 'AllDocs' "

select * from ##tbdata

set @iNumberofObjects = (select COUNT(*) from ##tbdata)
set @id = 1

while (@id &lt;= @iNumberofObjects)
begin

	SELECT 
	          @dbName = tblData.DatabaseName
		, @objectName = tblData.objectName
		, @objectNameFull = tblData.DatabaseName
                                      + '.dbo.' + tblData.objectName
	from   ##tbdata tblData
	where  tblData.id = @id

	SET @db_id = DB_ID(@dbName);	
	SET @object_id = OBJECT_ID(@objectNameFull);

	IF @db_id IS NULL
	BEGIN;
		PRINT N'Invalid database' + isNull(@dbName, '');
	END;
	ELSE IF @object_id IS NULL
	BEGIN;
		PRINT N'Invalid object ' + isNull(@objectNameFull, '');
	END;
	ELSE
	BEGIN
	     SELECT 
		    DB_NAME(database_id) as databaseName
		  , object_name(tblStats.object_id, database_id) as objectName
		  , tblStats.index_id				
		  , tblStats.index_type_desc				
		  , tblStats.partition_number
		  , alloc_unit_type_desc
		  , avg_fragmentation_in_percent
		  , ghost_record_count
		 , forwarded_record_count
	   FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL 
                                     , 'DETAILED') tblStats

           --only look @ Clustered and Non Clustered Indexes
           --omit heaps
	   WHERE tblStats.index_type_desc IN 
                    ('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

           -- Index Leaf Levels
           AND   tblStats.index_level = 0
		;
	END;

	set @id = @id + 1

end

drop table ##tbData

GO

For code above, crediting:

 

Explanation:

  • Review your output and watch for Index Fragmentation levels over 30% or so

My Own Thoughts

Review percentile of Single Use Queries and determine whether “Optimize for Ad-hocs should be engaged”

From Kimberly Tripp’s public library, let us use:

Plan Cache and optimizing for ad-hoc workloads
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/


SELECT 
          objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) 
               as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) 
               AS [Total Plans - USE Count 1]

        , (sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) * 100) 
			/  sum(1) 
				as [% SingleUse Entries]

		, str(sum(cast( (CASE WHEN usecounts = 1 THEN size_in_bytes 
                         ELSE 0 END) as decimal(18,2))) * 100
			/ sum(cast( size_in_bytes as decimal(18,2) )), 12, 2)
				as [% SingleUse MemoryUse]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

go

Tabulated Output:

SingleUseCounts

Explanation:

  • Reviewing Cache Type = “Prepared”, we can see that single use counts make up 53% of the Prepared plans in the Cache and that they are taken up about 80% of the Memory
  • Will advise that SQL Server Instance configuration “Optimize for ad-hocs workloads” be engaged

Determine whether forcing “Database Parameterization” will be sufficient

“Optimize for Ad-Hocs” impacts the entire SQL Instance, you might opt for forcing Database Parameterization on specific databases instead.

A few months ago I stole this piece of code that just lets use identify single used SQL.

The SQL is courtesy of MSFT’s Bart Duncan:


SELECT TOP 100
    query_hash, query_plan_hash,
    cached_plan_object_count,
    execution_count,
    total_cpu_time_ms,
/*    
    , total_elapsed_time_ms,
    total_logical_reads, total_logical_writes, total_physical_reads,
*/    
    sample_database_name, sample_object_name,
    sample_statement_text
FROM
(
    SELECT
        query_hash, query_plan_hash,
        COUNT (*) AS cached_plan_object_count,
        MAX (plan_handle) AS sample_plan_handle,
        SUM (execution_count) AS execution_count,
        SUM (total_worker_time)/1000 AS total_cpu_time_ms,
        SUM (total_elapsed_time)/1000 AS total_elapsed_time_ms,
        SUM (total_logical_reads) AS total_logical_reads,
        SUM (total_logical_writes) AS total_logical_writes,
        SUM (total_physical_reads) AS total_physical_reads
    FROM sys.dm_exec_query_stats
    GROUP BY query_hash, query_plan_hash
) AS plan_hash_stats
CROSS APPLY
(
    SELECT TOP 1
        qs.sql_handle AS sample_sql_handle,
        qs.statement_start_offset AS sample_statement_start_offset,
        qs.statement_end_offset AS sample_statement_end_offset,
        CASE
            WHEN [database_id].value = 32768 THEN 'ResourceDb'
            ELSE DB_NAME (CONVERT (int, [database_id].value))
        END AS sample_database_name,
        OBJECT_NAME (CONVERT (int, [object_id].value), CONVERT (int, [database_id].value)) AS sample_object_name,
        SUBSTRING (
            sql.[text],
            (qs.statement_start_offset/2) + 1,
            (
                (
                    CASE qs.statement_end_offset
                        WHEN -1 THEN DATALENGTH(sql.[text])
                        WHEN 0 THEN DATALENGTH(sql.[text])
                        ELSE qs.statement_end_offset
                    END
                    - qs.statement_start_offset
                )/2
            ) + 1
        ) AS sample_statement_text
    FROM sys.dm_exec_sql_text(plan_hash_stats.sample_plan_handle) AS sql 
    INNER JOIN sys.dm_exec_query_stats AS qs ON qs.plan_handle = plan_hash_stats.sample_plan_handle
    INNER JOIN sys.dm_exec_cached_plans as cachedPlans 
			ON qs.plan_handle = cachedPlans.plan_handle
			AND cachedPlans.usecounts = 1
    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [object_id]
    CROSS APPLY sys.dm_exec_plan_attributes (plan_hash_stats.sample_plan_handle) AS [database_id]
    WHERE [object_id].attribute = 'objectid'
        AND [database_id].attribute = 'dbid'
) AS sample_query_text
ORDER BY execution_count DESC;

Output Tabulated:

singleUseSQL

Explanation:

Automation Opportunities

Jeremy Taylor @ http://www.jeremytaylor.net/2012/04/01/sharepoint-2010-database-maintenance/ discusses the opportunity for using SharePoint’s own Health Analyzer to carry out some of the items listed above.

Jeremy Taylor also cites Database maintenance for SharePoint 2010 Products (http://technet.microsoft.com/en-us/library/cc262731.aspx).  A nice well article written by Bill Baer and Bryan Porter; and technically reviewed by Paul S. Randal (SQLskills.com).

Songs Playing

Watching YouTube last night and heard a song playing in the Background.  Tried to make out the lyrics and found out it is Peter Bradley Adams.  The particular song is Full Moon Song (http://www.youtube.com/watch?v=X2jEUrQ2Ggc).

Also, I liked his “The Longer I Run” track:

A bit of a melancholic tracks, and so you might prefer his “Keep Us” song  It is here

And, later on came back to add Emily Lyrics; which is inturn here.

 

References

Sharepoint 2013

Sharepoint 2010

SQL Scripts

MS SQL SERVER – OLE DB / ACE Provider – Unable to update existing records

MS SQL SERVER – OLE DB / ACE Provider – Unable to update existing records

The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LSERVER” could not UPDATE table “[Microsoft.ACE.OLEDB.12.0]”. OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LSERVER” returned message “You cannot update this field because the value you’re trying to apply is not valid or would break a data integrity rule.  Please correct and try again.”.

While trying to update MS Sharepoint list data from MS SQL Server using the ACE Provider, experienced the error pasted above.

Spent so much time trying to correct:

  1. Reviewed the columns I was updating
  2. In MS Sharepoint, checked that the columns, which were choice columns, were fully defined – that is they contained the corresponding “look-up” values

Checked numerous times, my fingers started to tingle from over-typing

What it ended up being is that though the new data was good, some previous data violated the rule and were now being checked as part of ‘commiting’ this new data.

Ended up relaxing the rules for all choice columns:

Allow ‘Fill-in’ choices: