Docstoc

Database Mirroring_ Maximizing Availability Through

Document Sample
Database Mirroring_ Maximizing Availability Through Powered By Docstoc
					     Advanced Tuning:
Unconventional Solutions to
    Everyday Problems
         Robert L Davis
Who am I?
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.
                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
          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
            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/
Advanced Tuning: Unconventional
 Solutions to Everyday Problems
            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/
Advanced Tuning: Unconventional
 Solutions to Everyday Problems
            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
                 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
          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
          Unconventional Solution
           to 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
Advanced Tuning: Unconventional
 Solutions to Everyday Problems
           Unconventional Solution
            to 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
Advanced Tuning: Unconventional
 Solutions to Everyday Problems
Advanced Tuning: Unconventional
 Solutions to Everyday Problems


          Q&A
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

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:2/25/2013
language:English
pages:18