Advanced Tuning: Unconventional Solutions for Optimal SQL Server Performance

1. Advanced Tuning: Unconventional Solutions to Everyday Problems Robert L Davis

2. Who am I? @SQLSoldier 10+ www.sqlsoldier.com

3. Advanced Tuning: What is Tuning? • Finding poor performance and making it better. • Ensuring that the server is configured for optimal performance. • Making use of the tools available to you find hidden problems and resolve them.

4. Advanced Tuning: Tools • SQL Trace/Profiler (please don’t use Profiler in production) • Performance Monitor • SQLDiag/PSSDiag • SQLNexus • RML Utilities • PAL Tool • Database Engine Tuning Advisor (DTA) • SQLIO • DMV’s • SQL Error Log • Your creativity

5. Advanced Tuning: Tools • SQL Trace/Profiler – Captures SQL activity • Find out what is occurring internally in SQL Server • Output to a trace file • Profiler can correlate a trace file and a Performance Monitor file by time • http://msdn.microsoft.com/en-us/library/ms191152.aspx • Performance Monitor – Captures server activity • Find out what is occurring at the server level • Output to a file • SQL counters can be captured via sys.dm_os_performance_counters

6. Advanced Tuning: Tools • SQLDiag/PSSDiag – Collects a variety of diagnostic data • Windows Performance Logs • Windows Event Logs • SQL Traces • SQL blocking info • SQL Configuration info • http://msdn.microsoft.com/en-us/library/ms162833.aspx • SQLNexus – Analyzes SQLDiag/PSSDiag data • Creates easy to interpret reports and graphs • Finds most expensive queries in trace files • Provides details on resource waits statistics • http://sqlnexus.codeplex.com/

7. Advanced Tuning: Unconventional Solutions to Everyday Problems DEMO

8. Advanced Tuning: Tools • RML Utilities – Diagnoses SQL Server Performance data • ReadTrace– consumes trace files • Reporter – provides easy to understand reports on trace data consumed using Readtrace • OStress – replays and stress tests queries • ORCA – Ostress replay control agent • http://blogs.msdn.com/b/psssql/archive/tags/rml+utilities/ • PAL Tool – Performance Analysis of Logs • Creates easy to read and understand graphs from Performance Monitor files • Color codes graphs based on known thresholds to easily identify possible bottlenecks • Requires Microsoft Chart Controls for .NET Framework 3.5 • http://pal.codeplex.com/

9. Advanced Tuning: Unconventional Solutions to Everyday Problems DEMO

10. Advanced Tuning: Tools • Database Engine Tuning Advisor (DTA) • Formerly Index Tuning Wizard • Performs in-depth index analysis • Can be based on a single query or a full trace file or work file • Can perform “What if?” analysis to verify recommendations • Limited in scope • SQLIO – Determines I/O capacity of storage • Should be used to verify I/O capabilities before deploying SQL to the storage • Validates storage I/O capabilities through stress testing • Not the simplest tool to learn • Great tutorial by Brent Ozar (@BrentO) on SQL Server Pedia: http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

11. Advanced Tuning: Tools • DMV’s – provides insight into the internal statistics and structures of SQL Server • Developed to make troubleshooting easier • SQL Team developers were challenged to try to fix bugs by only using data readily available to administrators • DMV’s for troubleshooting everyone should know: • sys.dm_os_wait_stats – overall wait statistics for the server. Most are cumulative • sys.dm_os_waiting_tasks – wait statistics for active tasks currently executing • sys.dm_os_performance_counters – all SQL Server performance counters available to Performance Monitor also found here • sys.dm_db_index_usage_stats – statistics on how your indexes are being used • sys.dm_exec_cached_plans – query plans in the plan cache • sys.dm_exec_sql_text – text of a query based on the sql handle. Joined to other DMV’s to get the exact text to which they are referring • sys.dm_os_buffer_descriptors – statistics on how the memory areas in the buffer pool are allocated • sys.dm_exec_query_memory_grants – statistics on how much memory is allocated to individual queries or what memory grants are pending • sys.dm_exec_requests – the current requests on the server • sys.dm_exec_sessions – the current sessions on the server • Glenn Berry’s DMV a day series: http://www.sqlservercentral.com/blogs/glennberry/archive/2010/05/03/recap-of-april-2010-dmv-a-day-series.aspx

12. Advanced Tuning: Tools • SQL Error Log – error and other important information • Wealth of information about alerts and errors occurring in SQL Server that may not be reported through any other means • Very useful for capturing deadlock information via trace flags 1204 and 1222 • Reports I/O freezing and excessive waits for I/O requests • Reports when a torn page is recovered from a mirroring partner • Your creativity – don’t be afraid to think out of the box

13. Unconventional Solutionto Everyday Problems • Scenario: CPU utilization spikes • No pattern to when they occur • Are short term (< 10 min.) and disappear before operations personnel can react • Performance critical production server • Users are affected by the CPU spikes • Solution: run a custom SQL trace automatically as soon as a CPU spike is detected and capture top 50 CPU consuming queries. • This solution can be adapted to respond to any performance criteria that you can measure

14. Advanced Tuning: Unconventional Solutions to Everyday Problems CODE

15. Unconventional Solutionto Everyday Problems • Scenario: How to measure replication latency without tracer tokens • Replication latency can spike to high latency at times • Tracer tokens not effective when latency is high • Data freshness critical replication servers • Monetary decisions based on replicated data • Solution: query the Replication Monitor tracking tables and the replication system tables to determine current latency

16. Advanced Tuning: Unconventional Solutions to Everyday Problems CODE

17. Advanced Tuning: Unconventional Solutions to Everyday Problems Q&A

18. Advanced Tuning: Unconventional Solutions to Everyday Problems Thank You! • The PowerPoint slide-deck and the SQL code will be posted on my blog tonight: • http://www.sqlsoldier.com