Disk defragmenting SharePoint on Virtual Machines: Performance tweak or myth?

Written by Keith Tuomi on . Posted in Performance, SharePoint 2010, SharePoint 2013, SQL Server 2012. Leave a Comment

I previously posted about the effectiveness of defragmenting guest machines (SharePoint servers) in virtualized environments here:
SharePoint on Virtual Machines – Is Disk Defragmenting necessary for Performance?

Today the ever-helpful President of PerfectDisk, Bob Nolan, wrote me to share a very interesting tidbit that he found on VMware’s VSphere documentation:

“Last week I was researching something and I found an interesting piece of documentation that might be helpful to you down the road.  The VMware 5.1 doc has a section enumerating 12 things to do when you have disk latency or I/O contention issues.  “Defragment the file system of all the guests” is the 2nd recommendation on the list http://pubs.vmware.com/vsphere-51/index.jsp?topic=%2Fcom.vmware.vsphere.monitoring.doc%2FGUID-174326D5-238B-48CA-B030-02009E388523.html”

sharepoint-defragment-vmware

Also:

“You may want to look at this too. Cormac Hogan is a VMware storage architect and this is a blog he did on how Storage I/O Control (SIOC) balances fairness and performance http://blogs.vmware.com/vsphere/2013/04/virtual-machine-io-fairness-versus-performance.html .  The gist of the blog is that VMware Kernel settings reduce the number of outstanding I/O requests any VM can have when multiple VMs are sharing a LUN.  SIOC basically throttles performance to improve latency.  Further, if you do sequential I/O, VMware will grant you more I/O requests.

In response I wrote this paper which says if VMware is going limit your I/O requests then get the most from the ones you have by doing larger I/O. When you do larger I/O you tend to do sequential I/O so you can also get the additional requests VMware is willing to grant http://www.raxco.com/user_data/white_papers/Getting_More_VMware_Performance_from_Fewer_IO_Requests.pdf”

This reinforces the original conclusion: This data combined with my decade-plus experience of seeing direct noticeable performance improvements on IIS boxes after full defragmentation and implementation of regular defrags, located on physical or virtual disks, leads to me to stand by my guns: I will continue to recommend disk defragging.  Would love to see if someone can change my mind..

T-SQL to get SQL Transaction Log Sizes

Written by Keith Tuomi on . Posted in SharePoint 2010, SharePoint 2013, SQL, SQL, SQL Server 2012. Leave a Comment

When dealing with the slew of SharePoint MS SQL databases that can be found in a typical install, it’s a bit of a time suck to check transaction log sizes manually. I put together the following T-SQL script to quickly show which transaction logs out of all the logs in the particular SQL instance, are above 299 MB in size. Adjust that threshold to your tastes.

declare @LogSpace table
(
DatabaseName varchar(255),
[Log Size (MB)] float,
[Log Space Used (%)] float,
[Status] int)
insert into @LogSpace
execute('dbcc sqlperf(''LogSpace'')')
 
select * from @LogSpace 
where [Log Size (MB)] > 299 
order by [Log Size (MB)] desc 
--order by [Log Space Used (%)] desc

SQL Server PowerShell & SharePoint – Set Autogrowth on Content DB’s

Written by Keith Tuomi on . Posted in PowerShell, SharePoint 2013, SQL Server 2012. Leave a Comment

​The SQL Server provider for Windows PowerShell exposes the hierarchy of SQL Server objects in paths similar to file system paths. You can use the paths to locate an object, and then use methods from the SQL Server Management Object (SMO) models to perform actions on the objects.

For the TechNet reference, check here: http://msdn.microsoft.com/en-us/library/cc281947.aspx

For a video walkthrough and great summary of it, check here: http://sqlserverpedia.com/blog/sql-server-bloggers/scripting-db-objects-in-powershell-video-transcript/

In this scenario, we’re going to use it for a repetitious task in most  SharePoint installations, which is, to assign more sensible Autogrowth settings to your Content DB’s.

For the full rundown on why we need to modify SQL Server settings (and can’t just do it from SharePoint directly), check around the 20 minute mark on this video: http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-02-Best-Practices-for-SQL-Server-Database-Settings#time=20m24s

1. Open up a PowerShell instance on your SQL Server.

2. Since our task is to set the FileGrowth properties of all the content DB’s, let’s first check out the FileGrowthType Enumeration: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.filegrowthtype.aspx
Your options are:

​Member ​Description
​KB ​File growth occurs in the specified number of KB.
​Percent ​File growth occurs as an amount that is a specified percentage of the existing file size
​None ​The file will not grow automatically.

The FileGrowthType enumeration class is served by the (datafile) GrowthType property and the (logfile) GrowthType property.

Modify your server instance names and autogrowth preferences in the following script, and execute! The filter displayed on it filters to apply only to databases and logfiles with “Content” in the name – the typical naming scheme to indicate SharePoint content databases. Modify to taste:

$Server="SQLSRV2012"      
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$databases = $SMOserver.Databases;
foreach ($DB in $databases | where{$_.Name -like '*Content*'}) {
    #Set Log File growth
 foreach ($DBLF in $DB.logfiles) {
  $DBLF.set_GrowthType("KB"); 
  $DBLF.set_Growth("51200"); #50mb 
  $DBLF.Alter();
        } 
 #set File Growth 
 $DBFG = $DB.FileGroups;
    foreach ($DBF in $DBFG.Files) {
  $DBF.set_GrowthType("KB"); 
  $DBF.set_Growth("102400"); #100mb 
  $DBF.Alter();
        }
    }