spca2013 - hunting performance issues
TRANSCRIPT
SharePoint 2013 Hunting Performance Issues
Margriet BruggemanNikander Bruggeman
About Nikander…
• Experienced Web Developer and .NET Architect
• SharePoint Specialist/MVP• Author SharePoint books• Author SharePoint articles• Author MOC courseware• Speaker TechNet, HeliView• Manning Technical Reviewer
About Margriet…• Experienced Web Developer
and .NET Architect• SharePoint Specialist/MVP• Author 13 SharePoint books• Author SharePoint articles• Author MOC courseware• Speaker TechNet, SharePoint
Connections, MS Launch Event• Manning Technical Reviewer• TechNet Forum Moderator• TechNet Wiki Community Council
Agenda• Anti-Patterns• Optimizing SQL Server for SharePoint• The Mind Reader• SQL DMVs• Analyzing IIS• Performance Counters• Maxer• PressurePoint• What’s in the Performance Toolkit?• The Most Useless Slide of the Deck• Q&A
Anti-Patterns
• Look for the Magic button• Use SQL Server Profiler• Optimize configuration settings• Collect as much performance data as you can
Optimizing SQL Server for SharePoint
• Dedicated SQL Server Instance• Content Database size
(http://technet.microsoft.com/en-us/library/cc298801.aspx)• Shrinking Database
(http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/)• TempDB• I/O• Memory• MAXDOP
(http://technet.microsoft.com/en-us/library/cc298801.aspx)• Statistics
• http://www.microsoftvirtualacademy.com/training-courses/tuning-sql-server-2012-for-sharepoint-2013-jump-start#fbid=97fCQYgsi9z
The Mind Reader
• 2013: finish before release• The magic number 5• No memory problems for the WFE
SQL DMV Queries• Queries running in a specified
period of time• Finding Wait Causes• Finding Slowest Queries• Longest Running Queries• Longest Blocked Queries• Reads and Writes per Database• Queries that use the most I/O• Queries executed most often• State of Statistics• Find Current SQL Statements• DMV Snapshots
• Effect Queries on Performance Counters
• Relation between Performance Counters and Wait States
• SQL Queries, Performance Counters, and Wait States
• Queries that use the most CPU• Finding Missing Indexes• Queries with missing statistics• Find databases with most missing
indexes• Queries running slower than
normal
http://www.manning.com/stirk/
Finding Wait Causes• ASYNC_IO_COMPLETION• IO_COMPLETION• LATCH_x• PAGEIOLATCH_x• PAGELATCH_x• WRITELOG• CXPACKET• LCK_x• ASYNC_NETWORK_IO• SOS_SCHEDULER_YIELD• SQLTRACE_BUFFER_FLUSH
Relations wait types and performance counters
Wait Type Performance CountersASYNC_IO_COMPLETION Physical Disk: Avg. Disk Queue Length
Physical Disk: Current Disk Queue LengthMemory: Available BytesMemory: Pages/sec
IO_COMPLETION Physical Disk: Avg. Disk Queue LengthPhysical Disk: Current Disk Queue LengthMemory: Available BytesMemory: Pages/sec
LATCH_x Memory: Available BytesMemory: Pages/sec
PAGEIOLATCH_x Physical Disk: Avg. Disk Queue LengthPhysical Disk: Current Disk Queue Length
PAGELATCH_x Physical Disk: Avg. Disk Queue LengthPhysical Disk: Current Disk Queue Length
WRITELOG Physical Disk: Avg. Disk Queue LengthPhysical Disk: Current Disk Queue Length
Query Result
Don’t forget it’s Web Based!• Analyze IIS Log Files (http://
gallery.technet.microsoft.com/office/Set-IIS-Log-Fields-via-ee9c19b3) • SharePoint Flavored Weblog Reader
(SFWR, http://gallery.technet.microsoft.com/office/The-SharePoint-Flavored-5b03f323)
• Log Parser (http://www.microsoft.com/en-us/download/details.aspx?id=24659)
• Log Parser Studio (http://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765)
• Splitting IIS Log Files via Gsplit (http://www.gdgsoft.com/gsplit/)
SFWR• The max request time per URI• The min request time per URI • The average request time per InfoPath URI• The max request time per InfoPath URI• The min request time per InfoPath URI • The average request time per Report Server URI• The max request time per Report Server URI• The min request time per Report Server URI • Browser percentage• Dead links• Failed pages• Failed InfoPath pages• Most busy days of the week• Most requested pages• Requested pages per day• Percentage error page requests• Requests per hour per day• Requests per hour• Requests per user
• Requests per user per month• Requests per user per week• Slowest requests• Slowest failed requests• Slowest successful requests• Slowest requests per URI• Top requests per hour• Top visitors• Traffic per day in MB• Traffic per week in MB• Unique visitors• Unique visitors per day• Unique visitors per week• Unique visitors per month• Searches per user• Slowest requests for a specific user
Performance Counters
• http://gallery.technet.microsoft.com/office/PowerShell-script-for-59cf3f70
• http://sharepointdragons.com/2013/02/27/working-with-blg-files/
Boundaries Thou Shalt Not Cross
• Maxer
• http://gallery.technet.microsoft.com/office/PowerShell-Maxer-for-cd9e13d6
Performance Testing Tools
• PressurePoint Dragon for SharePoint 2013
• http://gallery.technet.microsoft.com/office/PressurePoint-Dragon-for-87572ee1
What is in your performance kit?• SQL DMVs• Fiddler - http://fiddler2.com/ • Maxer• PAL -
http://sharepointdragons.com/2011/12/30/when-you-have-performance-troubles-you-need-a-pal/
• Performance Counters• PressurePoint• SharePoint Flavored Weblog Reader (SFWR)• SharePoint 2010 Admin Toolkit - http://
www.microsoft.com/en-us/download/details.aspx?id=20022 • SharePoint Log Viewers• Set of test documents
The Most Useless Slide of the Deck
• Radar for SharePoint 2013
Contact infoMargriet [email protected] Nikander [email protected]
Lois & Clark IT ServicesWeb site: http://www.loisandclark.euBlog: http://www.sharepointdragons.com
