Downloaded 16 times
Uploaded bySolarWinds
Find and fix SQL Server performance problems faster
This document discusses troubleshooting SQL Server performance issues, emphasizing the importance of using dynamic management views and understanding wait statistics. It outlines various techniques for performance tuning, including identifying bottlenecks and utilizing tools like Performance Monitor and execution plans. Additionally, it highlights the importance of query tuning, indexing, and leveraging data compression to enhance performance.
Find and fix SQL Server performance problems faster
- 1. THERE’S YOUR PROBLEM TROUBLESHOOTING SQLSERVER LIKE A PRO Joey D’Antoni 20 August 2015
- 2. THERE’S YOUR PROBLEM 2©2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 3. AGENDA Performance Wait Stats Indexes, Whenand Where Perfmon Bringing This All Together 3 M AKI NG THI NG S FAS TE R © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 4. FIRST THINGS FIRST DynamicManagement Views are Your Friends SSMS GUI offers some insight, but not nearly as much as queries and scripts Consider using tools at scale 4 G E T CO M FO RTABLE W I TH DM V S © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 5. PERFORMANCE TUNING SQLSERVER DBAs get paid to backup and restore databases Performance usually gets second priority due to uptime Let’s about some techniques to cut to the chase 5 LI KE A P RO © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 6. HOW I TUNEA SERVER Check Server Configuration Read Error Log Look at Wait Stats Look in Plan Cache Tune Queries 6 M Y M E THO DO LO G Y © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 7. PERFORMANCE BOTTLENECKS CPU Disk Memory Network Blocking/Locking 7 I STO LE THI S FRO M TO M © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 8. UNDERSTANDING WAIT STATS WheneverSQL Server is waiting on something it records it Knowing how to read wait stats is the key to understanding performance Paul Randal—Tell me where it hurts 8 THE S E CRE TS TO S Q L P E RFO RM ANCE © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 9. WAIT STATS DEMO 9 LET M E S E E HO W I T W O RKS © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 10. CXPACKET Check MaxDOP Understand Cost Threshold for Parallelism Understand your workload Dig intothe plan cache It may be natural 10 W HY AM I ALWAY S S E E I NG THI S © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 11. INDEXES 11© 2015 SOLARWINDSWORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 12. TOOLS OF THETRADE SET STATISTICS IO ON SET STATISTICS TIME ON WAIT STATS 12 Q UE RY TUNI NG
- 13. WAIT STATS ANDMISSING INDEXES Large Logical Reads PAGEIOLATCH_SH Storage Latency Can result in higher than usual CPU use SP_WHOISACTIVE--Adam Machanic 13
- 14. OTHER PERFORMANCE TRICKS DataCompression where appropriate Columnstore for analytical workloads Splitting workloads by disk device using filegroups Faster SAN/More RAM 14 M AKI NG DATA G O FAS TE R © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 15. DATA COMPRESSION Reduces IOsneeded Incurs a CPU cost Good for slower storage Good for workloads with high scan rate Impact is mainly on update 15© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 16. DATABASE TUNING ADVISOR 16 BEV E RY CARE FUL © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 17. IDENTIFYING WHICH QUERIESTO TUNE 17 DI G G I NG I N DE E P © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 18. Physical Query Tuning 18 DE M O © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 19. Execution Plans 19 CAP TURING AND UNDE RS TANDI NG E X E CUTI O N P LANS © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 20. POLL Do You KnowxQuery? Have you worked with xEvents? 20© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 21. CAPTURING EXECUTION PLANS Profileris marked as deprecated Extended Events ARE the path forward Profiler is still a lot easier to use IMO xEvents are more powerful 21 P RO FI LE R V S X E V E NTS © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 22. QUERY STORE 22 S QL S E RV E R 2 0 1 6 © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 23. WINDOWS PERFORMANCE MONITOR PowerfulTool for holistic view of environment Dedicated SQL Server counters Can integrate with profiler traces 23 AKA P E RFM O N © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 24. PERFMON COUNTERS Memory •Available Mbytes SQLServer:Buffer Manager •Lazy writes/sec •Page life expectancy •Page reads/sec •Page writes/sec SQL Server:MemoryManager •Total Server Memory (KB) •Target Server Memory (KB) Processor •%Processor Time •%Privileged Time Process (sqlservr.exe) •%Processor Time •%Privileged Time Physical Disk •Avg. Disk sec/Read •Avg. Disk Bytes/Read •Avg. Disk sec/Write •Avg. Disk Bytes/Write Paging File •%Usage SQL Server:Access Methods •Forwarded Records/sec •Full Scans/sec •Index Searches/sec System •Processor Queue Length 24 S Q L S E RV E R © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 25. PERFMON TIPS Always runaway of production server Every 15 seconds is fine unless finding something specific Use BLG files—can use relog to go to another format Include Perfmon 25© 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED.
- 26. QUESTIONS? © 2015 SOLARWINDSWORLDWIDE, LLC. ALL RIGHTS RESERVED. 26
- 27. FREE TRIAL • TryDatabase Performance Analyzer FREE for 14 days • Improve root cause of slow performance • Quickly identify root cause of issues that impact end-user response time • See historical trends over days, months, and years • Understand impact of VMware® performance • Agentless architecture, installs in minutes 27 RE S O LV E P E RFO RM ANCE I S S UE S Q UI CKLY © 2015 SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. www.solarwinds.com/dpa-download/
- 28. THANK YOU © 2015SOLARWINDS WORLDWIDE, LLC. ALL RIGHTS RESERVED. The SOLARWINDS and SOLARWINDS & Design marks are the exclusive property of SolarWinds Worldwide, LLC, are registered with the U.S. Patent and Trademark Office, and may be registered or pending registration in other countries. All other SolarWinds trademarks, service marks, and logos may be common law marks, registered or pending registration in the United States or in other countries. All other trademarks mentioned herein are used for identification purposes only and may be or are trademarks or registered trademarks of their respective companies. 28