SQL Server Performance Troubleshooting - Day 2 Notes

gistfile1.md

SQL Server Performance Tuning Class - Day 2

Denver 2015

Finding Queries and Bottlenecks

• RECOMPILE hints will cause the sproc to not show up in the DMV (Dynamic Management Views)
• DMVs and profiler will only catch perf issues for a window of time. They are passive tools that have to be running when the issue occurs
• sp_Blitz will look across a wider window of time.
• ClearTrace as scriptable (and more powerful) version of SQL Server Profiler.
  • Recommended practice is to create a Tools database to store scripts like ClearTrace and the BrentOzar scripts. May shops will not allow anything to be installed to master. Plus, master won't be restored typically during a rebuild
• Extended events
  • Can save to Disk, Activity Counters, or Histograms
  • Very flexible filtering mechanism and supports random sampling
  • Extended events are lighter weight than profiler
  • However, extended events only works (well) on 2012+. Sorry 2008R2
• DMV
  • Collect data since the sql server started up
  • Metrics are very coarse. Anything finer requires active sampling
  • sys.dm_exec_procedure_stats
  • sys.dm_exec_trigger_stats
  • sys.dm_exec_query_stats
  • CROSS APPLY is an inner join to a function
• sp_BlitzCache - Batteries for DMVs
  • Does not have sampling, so it's looking across all time.
  • For 2008R2 and earlier you'll need to set a flag to pull back trigger stats b/c there's a bug in the way microsoft maintains the counters
• Profiler and Server Side Trace are really the only tools availalbe for 2008R2 and earlier. ClearTrace would be the preferred way to work with this trace info in these environments.
• Activity Monitor can conflict with SQL Server internals. Avoid.
  • Also shows a limited set of information

Solve a Performance Crisis with Plan Guides

• SqlServer:SqlStatistics:Batch Requests/sec
• Processor:% Processor Time
• SqlServer:Sql Statistics:SQL Compilations/sec
• SET PARAMETERIZATION FORCED WITH NO WAIT database level switch which will force parameterization across all simple queries even if the queries aren't using parameters. SIMPLE would be the normal option.
• Template plan guides can force queries to act as if they have parameters
• Hint plan guides allow you to force OPTIONS or Hints
• Freezing plan guides. Forces a plan in cache to be used. SQL engine will have to do exactly what the frozen version indicates.

Watch Brent Tune Queries

• Read metrics - look at logical reads, CPU time, duration, and query cost
• Identify the biggest problem: reads, CPU, or duration?
• Run sp_Blitz
• End user requirements gathering
  • What's the target
• CREEPY Method
  • Capture query metrics
  • Read the metrics and plan
  • Experiment with query cost:
    • Remove the ORDER BY
    • Change the list of fields in SELECT to just be SELECT 1
    • This makes a big difference in how the query optimizer will look at indexes
    • Switch table variables to temp tables
    • Question any non-INNER joins
  • Execution plan review
    • Look at big differences between estimated and actual row counts
    • Tune off the actual execution plan rather than the estimated
      • Are stats out of date
      • Parameter sniffing issues?
    • Identify implicit conversions or SARGability issues
    • Split query into temp tables and replace joins
  • Parallelism opportunities
  • Index improvements
• Always question ORDER BY clauses
• Focus on logical reads first and get that number down. Stats will give you this information. Plug into statisticsparser.com to help with output.
• Statistics get updated by SQL Server when the amount changes by ~20%
• GROUP BY can perform better than COUNT(DISTINCT(x))
• 4 metrics to measure performance improvements (show % change of each)
  • Logical reads
  • Duration
  • CPU time
  • CXPACKET
• SET STATISTICS TIME, IO ON this will dump run stats to the messages window. You can then pipe this to the statisticsparser.com site for easier to read details
• SQL Server always thinks that 1 record will come back from a table variable, UNLESS you use OPTION (RECOMPILE). However, that spikes CPU. This is why temp tables are preferred over table variables.
  • You'll get better statistics if you use temp tables
  • CTEs can give good statistics, however they will get rerun every time they're referenced

High Performance Reporting Services Tuning