Michael ODonovan Share Point Guy Gary Hope SQL

Michael O’Donovan Share. Point Guy Gary Hope SQL Guy

Expectations Every deployment is different Size Usage Patterns Fundamental understanding What you should be looking for Tools and techniques to help

Agenda Share. Point architecture basics Which Share. Point databases should concern me Hardware selection basics Share. Point database optimisation Monitoring my SQL Server(s)

Share. Point Architecture (simplified) ASP. NET Web Server with SQL Databases All content lives in SQL Databases

Key Concepts Web Application (IIS Site extended with WSS) Site Collection Site List Item

Tour around Share. Point Create a new content database

Performance Hot Spots Indexing Process Profile Import Timer Jobs (user sync from profile to user info) STSADM Backup & Restore Large List Operations

Which database has most disk IO demand?

Database Disk IO Demand Most Demand Medium Demand Low Demand Search Config *Content. . Temp +SSP Model Tlogs Master * Except during backup and Indexing + Except during Profile Import

SQL Hardware Selection x 64 Windows and x 64 SQL Only x 64 support in future versions Loads of RAM for SQL 4 GB … 16 GB (why more memory) Choosing server class hardware Scaling out always an option with Share. Point

Database Configuration Data File Characteristic: TLOG = Sequential Write IO DATA = Random Read and Write IO Separate TLog and Data onto separate Disks Separate Databases onto separate Disks

Disk I/O Optimization Partition Alignment Behavior prior to Windows Server 2008 create partition primary align=<Offset_in_KB> format fs=ntfs unit=64 K label="<label>" nowait Chose RAID Configuration appropriately Configure SAN appropriately

Share. Point DBs Content Max Size 100 GB Site Collections / Database Use RAID 5 for data (or even better RAID 10) Use RAID 10 for logs Usage is highly variable based on Share. Point usage Search Use RAID 10 logical units Usage is extremely read/write intensive

SQL Server Temp. DB Create additional secondary files of equal size Optimal Temp. DB data file sizes can be calculated using the following formula: [MAX DB SIZE (KB)] X [. 25] / [# CORES] = DATA FILE SIZE (KB) Rule of thumb: Calculation result (starting size) should be roughly equal to 25% of the largest content or search DB. Best managed by monitoring usage Trace Flag -T 1118

SQL Log and Data Locations Multiple Files for Temp. DB

Share. Point Database Admin Tasks Backup (and Restore) SQL Native Database and Log Backups System Center Data Protection Manager Database Consistency Checking DBCC Check. DB Index Maintenance ALTER INDEX REBUILD Managing Performance

Fragmentation Kills Performance Types of fragmentation Data file - Specify appropriate file size Log file – Specify appropriate file size Index – REORGANIZE or REBUILD ALTER INDEX REORGANIZE (> 5% and < = 30%) ALTER INDEX REBUILD (> 30%)

I/O Performance Measurement Performance Monitor Counters to watch: Disk Read/sec and Write/sec Compared with maximum achieved during test Typical systems will perform 100+ per spindle Average Disk sec/Read and sec/Write < 5 ms for log writes and <20 for data writes good > 100 ms you will have a performance problem Average Disk Queue Length Not the best measure in my opinion, compare to baseline Average Disk bytes/sec/Read and bytes/sec/Write Use sys. dm_io_virtual_file_stats to monitor files

Set initial size of database (auto grow) Fragmentation of IW content database Excel View of Database IO

Performance Monitoring Tools Performance Monitor SQL Performance Data Warehouse System Center Operations Manager Performance Testing & Tools Stress Testing SQLIO Visual Studio Sptdatapop (codeplex)

Using Database Aliases Allow content database to be moved to other instances with no config changes in Share. Point Can use DNS changes to “move” DBs to alternative instance in the case of database mirroring

What not to do Don’t do anything else with these d. Bs No new indexes No new tables No stored procedure changes Couple of well known exceptions Moving tables to filegroups to separate IO http: //blogs. msdn. com/enterprisesearch/archive/200 8/09/16/sql-file-groups-and-search. aspx

Newer SQL and Share. Point Versions Share. Point 2010 x 64 Only SQL 2005+ Only SQL 2008 benefits Backup Compression Transparent Database Encryption Database Mirroring Enhancement Policy Based Administration Performance Data Warehouse

5 Things to Take-Away Share. Point Database Characteristics How to monitor them in your world Log and Data file locations for Share. Point DBs Windows Server 2003 misalignment Fragmentation Set initial size of database files (auto grow)

White Papers Capacity Planning for Search Performance Recommendations Database Maintenance for Share. Point Working with large lists in Office Share. Point Server 2007

Related Content Breakout Sessions (session codes and titles) Whiteboard Sessions (session codes and titles) Hands-on Labs (session codes and titles)

10 pairs of MP 3 sunglasses to be won Complete a session evaluation and enter to win!

Resources Tech·Ed Africa 2009 sessions will be made available for download the week after the event from: www. tech-ed. co. za go. microsoft. com/fwlink/? Link. Id=95450 Microsoft Certification & Training Resources International Content & Community http: //microsoft. com/technet http: //microsoft. com/msdn Resources for IT Professionals Resources for Developers

© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.