Sponsored by:

Professional Association for SQL Server

SQL Server Performance Tools

Cindy Gross, Microsoft Dedicated Support Engineer and Microsoft Certified Master (MCM) – SQL Server 2008

http://blogs.msdn.com/cindygross

Cindy Gross

Dedicated Support Engineer

Microsoft’s Premier Field Engineering Team

Boise, ID

Microsoft Certified Master : SQL Server

Cindy.Gross@Microsoft.com

http://blogs.msdn.com/cindygross

The Microsoft Certified Master program for SQL Server is the deepest technical certification available from Microsoft, validating and recognizing the top SQL Server talent in the world

To achieve this certification, candidates must pass the following two exams in the following sequence:

• Exam 88-970: “SQL Server 2008 Microsoft Certified Master: Knowledge Exam” (4 hours)• Exam 88-971: “SQL Server 2008 Microsoft Certified Master: Lab Exam” (6 hours)

Designed for the real-world demands of top-tier SQL Server professionals, find out more about the new program requirements for MCM: SQL Server 2008 by visiting:

http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx

Performance Tools - Agenda

• Methodology• SQLDiag / PSSDiag• SQLNexus• Other Tools• References

Methodology per SQL Server 2008 Internals and Troubleshooting• Know your SLAs• Define the problem and exit criteria• Understand baselines• Gather data• Analyze data• Test potential solutions• Post Mortem• Refine solution• Update standards/best practices, apply to other systems

Gather Data

Why Gather Data?

• Save baselines• Compare performance before and after coding, index, or

hardware changes• Save off SQL Server configuration information• Gather information to troubleshoot various problems –

SQLDiag is good for “something is slow” type issues

SQLDiag / PSSDiag Myths

• It’s too heavy• It doesn’t capture what I need• Just use the individual tools• SQLDiag/PSSDiag are totally different

SQLDiag

• Ships with SQL Server• Can be made into a service and/or scheduled• With /X gathers a snapshot: evt/err logs, configs• Else gathers snapshot + PerfMon + Profiler• Controlled by XML files – edit with true/false

<EventlogCollector enabled="false" startup="false" shutdown="true" />

PSSDiag

• Wrapper around SQLDiag + Custom Diags• Examples: DMV information, clone db, msdb backup,

PerfStats• Controlled by the same XML file as SQLDiag• Usually has custom collectors bundled with it

Custom Collectors

Example from PerfStats download: SQLDiagPerfStats_Detailed_Trace2008.XML

<CustomDiagnostics>

<CustomGroup name="SQL 2008 Perf Stats" enabled="true" />

<CustomTask enabled="true" groupname="SQL 2008 Perf Stats" taskname="SQL 2008 Perf Stats Script" type="TSQL_Script" point="Startup" wait="No" cmd="SQL_2008_Perf_Stats.sql" pollinginterval="0" />

SQLDiag Configuration Demo

• Open dos-prompt with “run as administrator”• Example location: C:\Program Files\Microsoft SQL

Server\100\Tools\Binn• Run with /X for snapshot and to generate XML files• Review SD_Detailed.XML• Edit and save to a new file, pass with /I to SQLDiag• SQLDiag creates a server-side Profiler trace

Best Practices – Gathering Data

Best Practices - Planning

• Know your goal• If collecting for a long time, collect fewer/less detailed

events• Be prepared for multiple iterations, get more detailed

once you narrow it down

Best Practices - Output

• Few (one) traces at a time.• Avoid the GUI, SQLDiag uses server side. 929728• Trace to local disk with write cache, never to a network

share.  307786• Never trace to UNC path, even if UNC points to local

disk.• Use fastest available volume not already used by data or

log files. Avoid RAID-5 (write intensive).• Whether the trace impacts instance perf can depend on

disk speed.  • Test in QA environment or during non-peak hours.

Best Practices - Collection

• Avoid high frequency events like Object:Opened, Lock:Acquired/Released, etc.

• Default to batch-level, not statement-level.• Filtering reduces file size and I/O cost of tracing but

increases CPU cost. Try to filter on integer column (dbid, duration, etc) instead of a text column (database name, textdata, etc) and only when filter removes >10% of events.

 

Best Practices - Collection

• No real value in eliminating rare but high-value events (e.g. Hash Warning, Exception, Data File Auto Grow, etc).

• Events that greatly impact trace size:      SQL:StmtCompleted SP:StmtCompleted       Degree of Parallelism       Lock:Timeout      Show Plan Statistics       SQL:BatchStarting       RPC:Starting  

Best Practices – Performance Events

• All the Showplan events have high overhead and should be used only for a short time

• Actual plans for most uses: Showplan Statistics Profile or Showplan XML Statistics Profile

• Estimated plans for when queries never seem to complete: Showplan All or Showplan XML

• Text versions are subsets: no rows, execs, etc.• Non-XML will be deprecated

PerfMon – Basic Counters

• Logical disk– Avg disk sec/read < 10-20ms, log sec/write < 3-5ms– Disk reads/sec, Disk writes/sec

• Memory - Available MBytes• MSSQL Buffer Manager - Page Life Expectancy• MSSQL Databases - Active Transactions, Backup/Restore

Throughput/sec, Repl. Pending Xacts, Repl. Trans. Rate, Transactions/sec

• MSSQL Memory Manager - Total Server Memory, Target Server Memory

• MSSQL Plan Cache - Cache Hit Ratio (all instances)• MSSQL Wait Statistics - (all)• Process - % Processor Time (all instances)• Processor - % Processor Time (all instances)

Analyze Data

SQL Nexus

• Download Nexus from Codeplex.com• Install RML tools• Optimize your load: Pre-size your Nexus db, use simple

recovery, set autogrowALTER DATABASE [NexusAdvWorks] SET RECOVERY SIMPLE WITH NO_WAIT

ALTER DATABASE [NexusAdvWorks] MODIFY FILE ( NAME = N'NexusAdvWorks', SIZE = 100MB , FILEGROWTH = 10MB )

ALTER DATABASE [NexusAdvWorks] MODIFY FILE ( NAME = N'NexusAdvWorks_log', SIZE = 100MB )

• Import your SQLDiag + PerfStats output• Analyze your Data• Repeat

SQL Nexus Demo

• Show import options• Review available reports• Narrow down the problem• Find your bottlenecks and worst performing queries• Gather more detailed data for problem areas

Additional Data Gathering

Profiler Demo

• Show Showplan output options• Show Export feature

XEvents

• New in 2008, SSMS GUI in Denali• Lighter weight than SQL Profiler / SQL Trace• Using

SQL Server Extended Events http://blogs.msdn.com/b/extended_events/

Other Tools

• Performance Analysis of Logs (PAL)• RML Utilities – Readtrace, Ostress, ORCA• SQLIO – disk subsystem performance• SQLIOSim – data correctness, ships with SQL 2008+• StorPort tracing enhancements

References

• Waits and Queues White Paper• PerfStats Download• SQLDiag Parameters/Usage

Thank you to our sponsor

Professional Association for SQL Server

May 11-13, Orlando, FL

Oct 11-14, Seattle, WA

Save 25%: Register by April 12th

www.sqlpass.org/sqlrally

Register by March31st: save 40% and have the chance to win a cruise to Alaska!

“24HR11” code gets you $100 off

www.sqlpass.org/summit