![Page 1: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/1.jpg)
Polish SQL Server User Group
Troubleshoot SQL Server performance problems like a Microsoft Engineer
![Page 2: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/2.jpg)
Polish SQL Server User Group
About the Author
Marek Maśko • Senior Database Analyst in
• Working with SQL Server for ~6 years
• SQL DBA, Dev & Architect
• Contact Information: Email: [email protected]
LinkedIn: https://pl.linkedin.com/in/marekmasko
Twitter: @MarekMasko
![Page 3: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/3.jpg)
Polish SQL Server User Group
Scenario
• Users report performance issue
• You know nothing about server and database
What you do?
![Page 4: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/4.jpg)
Polish SQL Server User Group
Microsoft Customer Support Service
• Users report performance issue
• You open the MS Support Case
• MS CSS gives you PSSdiag package and ask you to run it
• You run it and collect the data
• Than You upload collected data to the workspace
• MS CSS returns with: – huge report about your SQL Server state
– issue root cause explanation
– Solution or recommendation
How they do it?
![Page 5: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/5.jpg)
Polish SQL Server User Group
Tools
Data collection
• PSSDiag / SQLDiag
• Diag Manager
Data analysis
• SQLNexus – RML Utilities
– Relog
• PAL Tool
![Page 6: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/6.jpg)
Polish SQL Server User Group
PSSDIAG
![Page 7: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/7.jpg)
Polish SQL Server User Group
PSSdiag
• Developed as a side project at Microsoft
• Mainly used by CSS and PSS teams
• Tool wrapper on other MS tools
• Based on SQLDiag
• Two versions: – Internal
– External
• Dramaticaly simplifies performance data collection
![Page 8: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/8.jpg)
Polish SQL Server User Group
SQLDiag
• It’s a command line utility delivered with SQL Server
• Located in the installation Binn directory
• It’s main purpose is to collect diagnostic data
PSSdiag SQLDiag Collectors
![Page 9: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/9.jpg)
Polish SQL Server User Group
What does it collect?
• Event Logs
• Performance Monitor Counters
• Server Configuration
• Error Logs
• Profiler Trace
• Blocking information
• PerfStat Scripts
• Spinlocks, query statistics query hashes
...
• There is a possibility to add your own custom collectors!
![Page 10: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/10.jpg)
Polish SQL Server User Group
How to run it?
Security • Windows authentication is default
• SQL authentication – Prompt for password
• The account used to run it – Must be able to login into target instances
– Must be a member of the sysadmin role
– Must be a member of Administrators
Configuration • Requires an XML configuration file (specifies what to collect)
![Page 11: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/11.jpg)
Polish SQL Server User Group
Parameters
• PSSdiag /B 14:00:00
• PSSdiag /E +02:00:00
• PSSdiag /B 08:00:00 /E +09:00:00
• PSSdiag /E 02:00:00 /L /Q
• PSSdiag /R /A Myname
• PSSdiag START /A Myname
• PSSdiag /U /A Myname
All command line parameters:
http://msdn.microsoft.com/en-us/library/ms162833.aspx
![Page 12: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/12.jpg)
Polish SQL Server User Group
Resource hit
• PSSDiag itself is negligible
• Collectors are efficient
• Profiler tracing is expensive
• Select output folder carefully
• Do not capture more than you need
• Always test and monitor it!
![Page 13: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/13.jpg)
Polish SQL Server User Group
Extended periods
• Run as a service so logout doesn’t stop collection
• Profiler Trace dominates output file size
• Trace files must always be collected on the server
• Enables real-time NTFS compression (/C1)
• Enable the DeleteOldTraceFiles
![Page 14: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/14.jpg)
Polish SQL Server User Group
DIAG MANAGER
![Page 15: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/15.jpg)
Polish SQL Server User Group
Diag Manager
• GUI tool used to create configuration files
• Can be downloaded for free from: http://diagmanager.codeplex.com
![Page 16: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/16.jpg)
Polish SQL Server User Group
DEMO Lets take a look on PSSDiag
![Page 17: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/17.jpg)
Polish SQL Server User Group
SQLNEXUS
![Page 18: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/18.jpg)
Polish SQL Server User Group
SQL Nexus
• Created as a side project at SQL Support Escalation team
• Process the output of PSSDiag/SQLDiag into a database and runs Reporting Services reports on top of it
• Allows users to create own collections and reports
• Can be downloaded for free from: http://sqlnexus.codeplex.com/
![Page 19: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/19.jpg)
Polish SQL Server User Group
Key Features
• Fast data loading and processing
• SQL Trace data aggregation using advanced logic and pattern recognition for intelligent statement execution grouping
• Supports multiple databases to store several performance analysis data sets
• Visualize loaded data via reports
• Extensibility!
![Page 20: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/20.jpg)
Polish SQL Server User Group
Pre-requsites
• Windows 2008 or newer
• SQL Server 2008 or newer
• .NET framework 4.0
• RML Utilities
![Page 21: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/21.jpg)
Polish SQL Server User Group
SQL Nexus reports
• Built-in reports for: – Environment configuration
– Blocking
– Wait statistics
– Resource utilization
– Interesting events
– Missing indexes
– Data Statistics
– Virtual file stats
![Page 22: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/22.jpg)
Polish SQL Server User Group
Bottleneck Analysis
![Page 23: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/23.jpg)
Polish SQL Server User Group
Read Trace Reports
![Page 24: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/24.jpg)
Polish SQL Server User Group
Read Trace Reports
![Page 25: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/25.jpg)
Polish SQL Server User Group
RML Utilities
• Replay Markup Language (RML) Utilities for SQL Server
• Can be downloaded for free from: https://support.microsoft.com/en-us/kb/944837
• Package contains: – ReadTrace
– Reporter
– OStress
– OStress Replay Control Agent (ORCA)
![Page 26: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/26.jpg)
Polish SQL Server User Group
ReadTrace
• Command line tool for processing Profiler traces and Extended Event traces
• ReadTrace consumes as input trace (.TRC) or (.XEL) files and .CAB or .ZIP files which contain .TRC files.
• The outputs from ReadTrace can be .TRC files, .RML files and the Performance Analysis (PerfAnalysis) database.
• RML Utilities have great documentation
![Page 27: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/27.jpg)
Polish SQL Server User Group
Relog
• Command line tool for processig performance counter logs
• Possible formats: – Text-TSV (tab delimited text)
– Text-CSV (comma-delimited text)
– Binary-BIN
– SQL
• Has possibility to filter specified counters
https://technet.microsoft.com/pl-pl/library/bb490958.aspx
![Page 28: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/28.jpg)
Polish SQL Server User Group
DEMO Lets take a look on SQLNexus
![Page 29: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/29.jpg)
Polish SQL Server User Group
CUSTOM DATA COLLECTIONS
![Page 30: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/30.jpg)
Polish SQL Server User Group
Custom Data Collections
• Add your SQL scripts to the _MyCollectors DiagManager folder
• Make sure the resultsets have a tag that uniquely identifies them
![Page 31: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/31.jpg)
Polish SQL Server User Group
Custom Data Collections
• Custom Collections are added to the CustomDiag.XML file in the _MyCollectorsfolder
• It is quicker to modify this XML file than do it through the UI
![Page 32: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/32.jpg)
Polish SQL Server User Group
Custom Data Collections
• Uses a custom import process
• By modifying a XML configuration file, you can have SQL Nexus import your custom data collection from PSSDiag
• Add the name of the rowset to TextRowsetsCustom.xml
• Located where you installed SQL Nexus
![Page 33: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/33.jpg)
Polish SQL Server User Group
DEMO
![Page 34: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/34.jpg)
Polish SQL Server User Group
PAL TOOL
![Page 35: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/35.jpg)
Polish SQL Server User Group
PAL Tool
• PAL – Performance Analysis of Logs
• Can be downloaded for free from: http://pal.codeplex.com/
• Allows you to set custom thresholds or use threshold already configured for your workload
• Requires MS Chart Controls
![Page 36: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/36.jpg)
Polish SQL Server User Group
PAL Wizard
• Carefully choose options and answer questions
• Processing will take time
![Page 37: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/37.jpg)
Polish SQL Server User Group
PAL Output
• Graphs with thresholds
• Alerts summarized in time slices
![Page 38: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/38.jpg)
Polish SQL Server User Group
PAL Output
• The output is color coded to let you know the areas to focus on – You do have some control over this through the threshold files
– Not everything in red actually means something
• You must know what to look for
![Page 39: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/39.jpg)
Polish SQL Server User Group
DEMO Lets take a look on PAL Tool
![Page 40: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/40.jpg)
Polish SQL Server User Group
When to Use Which Tool?
PSSDIAG / SQLNexus • More targeted performance analysis
• Need to view SQL internal resources (waits, blocking chains, query plans)
• Short timespan for collection
PAL • Great for overall system performance
• Benchmark
• Long duration
![Page 41: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/41.jpg)
Polish SQL Server User Group
SQL SERVER PERFORMANCE DASHBOARD
![Page 42: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/42.jpg)
Polish SQL Server User Group
SQL Server Performance Dashboard Reports
• Set of SSRS performance reports that integrate into SSMS
• Can be downloaded for free from: https://www.microsoft.com/en-us/download/details.aspx?id=29063
• Custom reports made to make
performance monitoring easier. • Great for tracking down trickier
performance issues
![Page 43: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/43.jpg)
Polish SQL Server User Group
Installation
• Download from web page
• Run *.msi package
• Execute setup.sql scripts
• Add reports to SSMS
![Page 44: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/44.jpg)
Polish SQL Server User Group
DEMO
![Page 45: PLSSUG - Troubleshoot SQL Server performance problems like a Microsoft Engineer](https://reader031.vdocuments.us/reader031/viewer/2022030215/5889515a1a28abde5a8b74bf/html5/thumbnails/45.jpg)
Polish SQL Server User Group
QUESTIONS?