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 <= @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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s