Optimizing SQL for SharePoint 2013

Setting	Recommendation	Notes
TempDB	Increase its initial size. Try starting with 500 MB Set to simple recovery mode Set Autogrowth  to use MB, not % Autogrowth  value should be larger, not smaller. Something like 500 MB should be a good starting value. Put on the fastest available non-system drive. If you separate it, calculate the number of cores you have on the server	Use rule of 1 file/CPU core Ensure Data files have same initial size and Autogrowth  settings!
Data Files	Use Multiple Data Files for Content and Search DB's Distribute Equally-Sized Data Files (.NDF) Across Separate Disks Number of Data Files Should Be <= Number of Processor Cores Multiple Data Files Not Supported for Other DB's	http://www.peters.com/Blogs/Lists/Posts/Post.aspx?ID=61
Auto growth settings - don't use %	Do not keep the default value which is 1 MB To avoid performance issues and reduce fragmented data files, you should set the autogrowth  value to a fixed number of megabytes. Recommendation is 1024 MB for data files and 256 MB for log files	- Use a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be: From <http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx> - For a managed production system, consider autogrowth  to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth  to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size: From <http://technet.microsoft.com/en-us/library/hh292622(v=office.15).aspx>
Initial DB and Log files		ALTER DATABASE MAXSIZE property The initial size is the size of the Databases when they will be created. The default is only set at 3MB. That means that when your content Databases are created, they only take up 10MB. This means every time you do something in SharePoint, such as a adding a document, the Database will have to grow (more info later) before being able to write that data. This is an operation that can be avoided by correctly setting a Database size up front. It is a best practice to set your initial size to how much data you expect to have per content database in a year. Yes, it will take more space on your disks initially, however your performance will be far better Furthermore, the log should be at about  25% of the database initial size http://www.stuartroberts.net/wp-content/uploads/2014/07/Maximizing-SQL-2012-Performance-for-SharePoint-2013-Final.pdf
Avoid shrinking DB files	Set Auto Shrink to FALSE	Never shrink. Will increase fragmentation and, thus, negatively impact performance
Set Auto-create statistics to FALSE	Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server. SharePoint Server configures the required settings upon provisioning and upgrade. Auto-create statistics can significantly change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, SharePoint Server provides coded hints for queries as needed to provide the best performance across all scenarios
Config DB Recovery Model	Switch the configuration database to the simple recovery model to restrict growth of the log file
Tools for Evaluating Databases	DBCC CHECKDB is a good SQL transaction for regularly evaluating SharePoint DB's. Using arguments with it is not supported, however. DBCC SHOWCONTIG is a good SQL transaction for showing the level of fragmentation that a DB has.
MAXMEM	Leave 4GB/10% for OS Factor in requirements of other instances	The Maximum Memory must be set on all SQL instances (even those unrelated to SharePoint) on a SQL Server if it is hosting SharePoint. This is because, even if you set the maximum on the SharePoint instance, the other non-SharePoint instances may be set to maximum and they may take up all the available memory
MINMEM	set it at 25% of the Max server memory for a farm where data requested by SharePoint changes a lot, and to 60% of the Max server memory when the data requested by SharePoint is almost always the same.	SQL Server is memory hungry – set a reasonable minimum and maximum memory. Example: 25GB minimum and 30GB maximum. In the rare cases that SQL Server releases the memory you don’t drop below 25GB. But that does not mean it claims the 25GB upfront The Minimum Memory setting keeps the memory dedicated to SQL Server from dropping below a certain amount. You should set this too. Just set it a couple gigs less than the Maximum Memory setting
MAXDOP	1
Fill Factor	80%
Maintenance plans	Rebuild indices daily - perform a Reorganize when your fragmentation is between 10% and 30 % as well as a Rebuild index when your fragmentation is above 30%. Backup Log to \weeklyT.Lob.bak with INIT (this will stop config DB T log from growing too large. Can delete the log backup once done - it is only used to truncate the log	For rebuilding indexes and checking the logical and physical DB integrity
SQL Server service domain account requires read permission on the account used to create the farm database maintenance plans	Configured	http://alstechtips.blogspot.co.uk/2014/06/sql-server-2012-sqlserver-error-15404.html
Transfer Logins Job	Configured	JL: not going to use the SSIS stuff, just script the logins out and a batch file scheduled task to read them in on the other servers
SQL Agent running	Use separate Service account (Security)
Collation	SharePoint uses the Latin1_General_CI_AS_KS_WS collation because the Latin1_General_CI_AS_KS_WS collation most closely matches the Microsoft Windows NTFS file system collation – http://support.microsoft.com/kb/2008668	Not fatal - SharePoint creates the DBs using the collation Latin1_General_CI_AS_KS_WS. In future builds for SharePoint this collation should be used at SQL install
Instant file initialization - When SQL Server increases the size of a file, it must first initialize the new space before it can be used. This is a blocking operation that involves filling the new space with empty pages (zeroes). That means, before SQL can create or auto grow, SQL must first write the size required with zeroes, and then it can save the data. “Instant File Initialization” -  a feature that allows file allocation requests to skip zero initialization on creation. As a result, file allocation requests can occur instantly – no matter what the file size	Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.  no syntax change required – SQL Server will use it if it has access to it	Granting the permission "Perform Volume Maintenance Tasks" To use instant initialization, your SQL Server service must be running with an account that has the required privilege. If your SQL Server service is running as a local administrator this permission already exists. For a service account which is not a local administrator (again, recommended!), the necessary privilege to grant is Perform Volume Maintenance Tasks. This permission can be granted by an administrator through the Local Security Policy tool (Start, All Programs, Administrative Tools) and once granted, SQL Server automatically uses instant initialization. IMPORTANT NOTE: If this permission is given while SQL Server is running, then SQL Server must be stopped and restarted. However, once the server is restarted, no other syntax or permissions are needed http://www.stuartroberts.net/wp-content/uploads/2014/07/Maximizing-SQL-2012-Performance-for-SharePoint-2013-Final.pdf
SQLIO test	SQL storage should meet performance criteria per sqlio.exe	Planned? Post UAT
NTFS Cluster sizes (allocation unit sizes)	64K	Plan to change 12/11/2014
Defrag drives containing Content DBs
Use RAID 10	Use RAID 10