Troubleshooting SQL Server Performance: Tips &Tools Amit Khandelwal

1. Troubleshooting SQL Server Performance: Tips &Tools Amit Khandelwal

2. DISCLAIMER The opinions expressed in this presentation and on the following slides are solely those of the presenter and not necessarily those of Microsoft. Microsoft does not guarantee the accuracy or reliability of the information provided herein.

3. About me: • I am currently working as Microsoft SQL Server Support Escalation Engineer. Worked with Beta Teams for SQL Server release and masters SQL Performance, HR-DR technologies and Replication and Engine technologies. Have Authored several internal training materials. Have been speaker at many programs like SQL Server Talks, SQL Server Asia level conference and many others. Current interests in learning and working on SQL Server Azure and other cloud products of Microsoft.

4. Agenda • Performance Troubleshooting Tools • SQLDIAG\PSSDIAG • SQL Nexus • SQL Nexus Analysis • Waits analysis • CPU\Spinlocks Slow I\O Troubleshooting Common Mysteries & Important Trace flags Old School Tricks!!!

5. Performance troubleshooting TOOLS SQLDIAG\PSSDIAG SQL Nexus

6. SQLDIAG– Data collection utility Windows Event Logs (Application, System, and Security) in .CSV files System Monitor (aka Perfmon) performance counters in .BLG file/s SQL Server Profiler traces in .TRC file/s SQL Server error logs, configuration, point-in-time snapshots of several DMVs in a .OUT file System information via the Msinfo32 utility Custom scripts for collecting blocking specific data All configuration parameters are stored in SQLDiag.xml file and can be changed

7. PSSDIAG – Data collection utility Similar to SQLDIAG utility except for the user interface • CPU spikes on the SQL Server box • Performance degradation during specific hours (say between 4:00 PM and 6:00 PM daily) • Blocking on SQL Server • Overall performance bottlenecks in SQL Server using wait statistics

8. SQL Nexus – Data analysis tool Helpful in post-mortem analysis of performance issues Uses RML utilities (Readtrace) – It’s a pre-requisite • Tool Highlights: • Trace aggregation to show TOP N most expensive queries • Wait stats analysis for visualizing blocking and other resource contention issues • Visualize loaded data via Reports – uses SSRS client side report viewer • PSSDIAG and SQL Nexus: • Nexus analyses data when SQLDiag is clubbed with SQLxxxxPerfStats script • SQLxxxxPerfStats scripts captures DMVs output including blocking information

9. PSSDIAG AND SQL NEXUS DEMO

10. Slow I\O troubleshooting

11. I/O Troubleshooting 2015-28-08 00:00:00.00 spid1 SQL Server has encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:\SQLServer\Testdb.mdf] in database [Testdb] (7). The OS file handle is 0x00000000000074D4. The offset of the latest long IO is: 0x00000000022000. The message indicates that SQL Server has been waiting on at least one I/O for 15 seconds or longer This represents Stuck\Stalled IO requests on SQL Server

12. I/O Troubleshooting Tools and Techniques • Performance Monitor Counters (Perfmon) sys.dm_io_pending_io_requests Look out for “io_pending” column of this DMV. The io_pending column indicates 1 if the I/O is still pending within the kernel. PhysicalDisk\% Idle Time (~100%) PhysicalDisk\Disk Bytes/sec Process\IO Data Bytes/sec( sqlservr) PhysicalDisk\Avg. Disk sec/Transfer (<0.015) PhysicalDisk\ Avg. Disk Queue Length (<2 per spindle)

14. COMMON MYSTERIES

15. Common Mysteries SQL Server is using high CPU, but PSSDIAG does not show CPU getting accounted to the queries running in SQL Server!!! How does Rebuild\reorg indexes affect statistics? SQL Server consumes all the memory that I give!! What is minimum Server memory? Can SQL take more than Max server memory?

16. Important TIPS

17. Important Trace Flags T4199 This trace flag is a combination of a lot of optimizer related fixes released under different trace flags. T1117 This trace flag applies to the entire SQL Server instance, not just to one DB, and it affects all files in the same file group in a database. T1118 This trace flag forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points T8048 This trace flag will upgrade NODE based partitioning to CPU based partitioning. T1236 Enable this trace flag when you see elevated values occur for LOCK_HASH spinlock count

18. Power plan setting* Max Server Memory Max Degree of Parallelism Client Side Tracing Upgrade SQL to latest Hotfixes\SPs Missing Indexes DMV & Database Tuning Advisor

19. References PSSDIAG http://diagmanager.codeplex.com/releases http://technet.microsoft.com/en-us/library/ms162833.aspx http://blogs.msdn.com/b/pamitt/archive/2011/02/25/how-to-use-the-sqldiag-the-sqlnexus-and-the-pal-tools-to-analyze-performance-issues-in-sql-server.aspx SQL Nexus http://sqlnexus.codeplex.com/ https://sqlnexus.codeplex.com/wikipage?title=GETTING_STARTED http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&referringTitle=Home https://sqlnexus.codeplex.com/wikipage?title=collecting%20data%20using%20batch%20files&referringTitle=GETTING_STARTED http://sqlnexus.codeplex.com/wikipage?title=readtrace&referringTitle=Sql2005PerfStatsScript Slow I\O Troubleshooting http://support.microsoft.com/kb/897284 http://blogs.msdn.com/b/karthick_pk/archive/2012/06/26/io_2d00_bottlenecks.aspx http://blogs.msdn.com/b/cindygross/archive/2010/03/17/what-do-those-io-requests-taking-longer-than-15-seconds-messages-on-my-sql-box-mean.aspx

20. References

21. Thank you for Attending Tech Unite!!!