SQL Server and SharePoint

1. SQL Server and SharePoint Best Frienemies Lisa Gardner Premier Field Engineer LisaG@microsoft.com http://blogs.msdn.com/sqlgardner @sqlgardner

2. Who am I ? What is PFE? • Lisa Gardner aka SQLGardner • Central Ohio native • Working with SQL since 6.5 • http://blogs.msdn.com/sqlgardner • @SQLGardner • Premier Field Engineering • Reactive and Proactive support for Premier customers • Architecture/Project Guidance • Team Mentoring • Deliver workshops • Troubleshooting

3. Table of Contents • SharePoint Overview • SharePoint Databases • Configuration, Setup, and Maintenance • What to Look Out For

4. SharePoint Overview Understanding the Application.

5. SharePoint Glossary • WFE • Web Application • Service Application • Site Collection • ULS Logs • Timer Jobs

6. SharePoint Web Architecture

7. Key Attributes

8. Service Applications • Provides granular pieces of functionality • Some can be tied to a specific server • Offers scalability, load balancing, fault tolerance for most services • Many to many relationship with web applications and service applications • Each web application can have a unique set of service applications

9. Timer Jobs • SharePoint equivalent to SQL Agent • OWSTimer - Windows service for SharePoint 2010 at a predefined schedule • Uses same logging infrastructure as web tier • Includes Correlation IDs • Jobs can be nested

10. SharePoint Internal Data Logging Database ULS Logs Correlation IDs

11. Logging Database • Stores all SharePoint usage and health data • ULS trace log data • Event log data • Blocking SQL Queries • Crawl and Query Statistics • Feature Usage • Page Requests • +More

12. Logging Database Table partitioning for ease of use and custom views Tables partitioned for performance Views for usability Custom reports through Excel or Excel web parts Some out-of-the-box reports use this data

13. ULS Logs Also referred to as trace logs Must be consistent location on all farm servers Over 260 categories of events Nine columns including: Timestamp, Process, Category, EventID, Level, Message, and Correlation

14. Correlation IDs • Generated for every request • Logged from the start of a request through to the end • Useful for troubleshooting and tracing • On error pages, ULS logs, Windows Logs, SQL Traces ULS ULS 7d25d051-ca73-43 … ~~~~~~~~~~~~~ ~~~~~~~~~~~~~ 7d25d051-ca73-43 … 7d25d051-ca73-43… 7d25d051-ca73-43 … Application Server Web Front-End Server

15. SharePoint Databases So Many Databases, So Little Time

16. Configuration and Admin Content Databases • Farm Configuration Store • Objects Table – Serialized Objects • Binaries Table – Farm Solution Store • SiteMap Table – Links a site into the configuration • Content Database for Central Admin is a Content DB with very specific templates - considered to be an extension of the configuration database • Backup and Recovery • It is Supported to back up this database • It is Not Supported to restore unless the farm is fully stopped when the backup is taken

17. Configuration and Admin Content Databases • General Recommendations • Default recovery model is Full but in most cases this database should be run in simple recovery mode • Initial Data File Size: 2GB is appropriate for most situations • Config databases are typically smaller and do not get much load • Mirroring • Supported to mirror within the farm (partner on same network as primary) • Not Supported to mirror asynchronously or to log ship over WAN

18. Content Database • Stores all site data in a site collection • Site Metadata • Web Part Pages • Files uploaded to document libraries • List Items • Security • Solutions • It is supported to Mirror in Farm for High Availability • It is supported to Mirror Asynchronously or Log Ship over WAN for disaster recovery • General Recommendations • Run in Full recovery mode only if the site data requires point in time restores

19. Content Database Schema Why SharePoint seems so crazy. Container Tables Sites Webs AllLists Namespace Table Url WebId LeafName DirName DoclibRowId Other Metadata ListId Id SiteId Userdata table 1…64 1...32 1..8 1..16 1..12 1..8 1…16 ~35 float int sql_variant Other metadata datetime bit nvarchar ntext

20. Content Database Layout • Can contain 1-2000 site collections • Scale out at the db level and the instance level. • Sizing Guidance <200GB • Maintenance tasks stay manageable • Makes db movement and DR easier • Plan for 2 IOPs per GB data • Can have 200GB-4TB if .25 IOPs per GB • Size and load depends on the sites they contain • Separate very active sites into different site collections/content dbs • Can have 32,767 dbs per instance, but recommend 200 per instance as manageability can be an issue • 300 DBs per Web Application

21. Service Application DBs

22. Service Application Databases Performance Considerations • The different service application dbs have a wide variety of performance/sizing considerations. • Write-Intensive dbs • Usage and Health Data Collection database (Logging) • Web Analytics Reporting database (during load) • Search service application Crawl database (during crawls) • Search service application Property database • User Profile service application Synchronization database

23. Service Application Databases Performance Considerations Cont’d • Read Intensive DB’s • Web Analytics Reporting database • Search service application Crawl database • User Profile service application Profile database • User Profile service application Synchronization database • User Profile service application Social Tagging database • Reporting database (Project Server)

24. Database Scale Out Guidance Search Content Content Content Content Content Content Logging Web Analytics Other Admin/ Content

25. Configuration, Setup, and Maintenance

26. Planning for SharePoint Setup • Allow the SharePoint installer to create databases • Modify file sizes and growth settings • Rename dbs to remove GUIDs • SharePoint setup and admin accounts required roles: • DB Creator • Security Admin • Can be removed for the setup account but will need to be added again for any further installs – not recommended • Patching/Service packs • Adding a new Service Application • Add Service Application account logins • Requires db_owner role in DB

27. Instance Configuration • Follow general Best Practices for SQL Configuration • Use Latin1_General_CI_AS_KS_WS collation • Configure for heavy TempDB usage • Multiple data files • Data and log files separated/isolated • Pre-size data files • Set max degree of parallelism to 1 • SharePoint overrides with MAXDOP • Set max server memory and use Lock Pages In Mem • Consider setting fill factor (%) to 80

28. Database Configuration • Do not use Auto Shrink • Set Auto Create Statistics OFF • Set Page Verify to Checksum • Set Auto Grow sizes to MB not Percent • Pre-size for growth • Monitor utilization and grow manually!

29. Index Maintenance • Index Maintenance is extremely important in SharePoint • DMV Sys.dm_db_index_physical_stats can be used to report index fragmentation • SharePoint 2007 by default would rebuild every index via a Timer Job • SharePoint 2010 does a much better job at keeping index fragmentation in check • It only rebuilds indexes that are fragmented • Updates statistics

30. Health Analyzer Rules • Index defragmentation and statistics maintenance address the following databases: • Configuration databases • Content databases • User Profile: Profile databases • User Profile: Social databases • Web Analytics Reporting databases • Web Analytics Staging databases • Word Automation Services databases • Search Property/Crawl databases • These databases contain proc_DefragmentIndices • Run daily

31. Health Analyzer Rules Cont’d • Search • Property database • Proc_MSS_DefragSearchIndexes • Run weekly • Crawl database • Proc_MSS_DefragGathererIndexes • Manual • Always report as fragmented • Execute this rule after the first full crawl

32. Statistics • Health Analyzer rules rebuild indexes and update statistics • AutoUpdate – off in SP 2010 by default • Update manually when: • Query execution times are slow • After maintenance operations such as table truncation or a large batch insert/update/delete

33. Why is Index/Stats Maintenance So Important? • GUIDs are used as clustered primary keys • Random values = unpredictable insert pattern • 16 bytes each • Heavy insert/update activity • These properties lead to rapid index fragmentation due to many page splits • Fillfactor helps delay the inevitable but increases space usage • SharePoint rebuilds indexes with fillfactor of 80

34. What to Look Out For Common issues

35. New Content Databases • Use DBA created content databases! • SharePoint hard codes small file size and growth settings • Automation Options: • Powershell is a great option to allow SP Administrators to create dbs! • Have a number of empty DBs already created • Must Do’s • Use Latin1_General_CI_AS_KS_WS collation • Set appropriate recovery model for your recovery needs • Add SP farm setup account and service account with db_owner role

36. Full Crawl Impact • When a full crawl is running – it is a very intensive operation that can have an impact on other dbs hosted on that instance – if asked about overall performance slowdown, ask if a crawl is running • It is common to see deadlocking in the Crawl database during this time. • If size rapidly grows: ask about the depth of crawling links in documents

37. Ensure Index Maintenance is Running • Health Analyzer Rule Definition • Databases used by SharePoint have fragmented indices • Databases used by SharePoint have outdated index statistics • Health Analysis Job in Logging DB • Details in ULS logs

38. Excessive Blocking • Common scenario: “The SQL Server is slow” • Ask for ULS Log info • Blocking/Deadlocks can be common in content DBs • Try a manual update stats • Inquire about large lists, dbs over threshold, and other capacity limitations being exceeded • Ask about list throttling and “happy hour” • Read Committed Snapshot Isolation is not supported

39. Others • ASYNC_NETWORK_IO_WAITS • Disk IO • TempDB Bottleneck • Very Large Queries • Logging is the ONLY DB to be queried directly

40. SP2013 Changes • Shredded storage to minimize storage needs with versioning • Sparse Columns to support wide lists • Web Analytics redesigned – more robust/scalable • Profile Sync – tests of a 3 week import for 300k users now only takes 7 hours • Stretch farms no longer supported – all databases must now reside in the same data center

41. Helpful Links • Know the Limits! • http://technet.microsoft.com/en-us/library/cc262787.aspx • More info on SharePoint DBs • http://technet.microsoft.com/en-us/library/cc678868.aspx • http://www.microsoft.com/en-us/download/details.aspx?id=3408

42. Questions?