Copyright © 2007 Quest Software

Brent Ozar, SQL Server Expert for Quest Software

How to Write a SQL Server Performance Review

http://flickr.com/photos/sean002/2510541359/Photo Licensed with Creative Commons

What The Heck Do I Know?• SQL Server Expert

for Quest Software• Former SQL DBA, SAN

admin, VMware admin• Recovering ASP coder• Specializes in

performance tuning• Came up through

management, not IT

Today’s Agenda• The Deliverable• Gathering The Data• Finding the Problems• Mitigating the Problems• Writing the Report• Resources and Q&A

The Deliverable: A Report

Key Parts of Our Review• Capture objective, definitive and

credible metrics• Analyze problems causing bad metrics• List of ways we could mitigate each

problem

Definitive Metrics• Start with Perfmon:

www.BrentOzar.com/perfmon• Gather for longest time possible• Check results daily to refine metrics

Tracing the Problems• Focus the trace on the problem

statistics (CPU, reads, writes)• Filter duration > 2000ms at first• Refine duration filter down over time• Put results in a different SQL Server

– Very fast server, faster than what you’re tracing– Write into a simple mode database, no backups

Querying the Trace Table

ORDER BY Duration DESC

Casting and Grouping

Correlate the Metrics & Queries• Demonstrate good investigation skills• Show a cause and effect relationship• Fields to mentally “join” on:

– Date/Time ranges– CPU– Reads/Writes– Duration

DO NOT FIX ANYTHING YET!• No matter how easy it looks• No matter whose fault it is• You have to show fruits from your labor• We want clear before & after snapshots• Worst thing we can tell our bosses:

“It’s OK now. I didn’t change much.”

Mitigations For Each Problem

Photo From:http://flickr.com/photos/sneakums/1345233680/

A Problem Has Many Mitigations• EVERY problem has multiple choices:

– Money– Time– Manpower

• Go out of your way to identify all of them

Explain Mitigations That Suck• Shows you know about the option• May come in handy for another problem• Explain why it doesn’t work in this case• Displays wise decision-making• The PM may know something you don’t

Be Willing To Implement Them

Photo From: http://flickr.com/photos/ninjapoodles/1130650313/

Sample Problem• Metric:

– Very high disk queue lengths on data file drive

• Trace tells us:– Report queries doing

table scans without indexes

– Many scheduled reports run simultaneously

Ways We Can Mitigate It• Add indexes• Run reports serially, not all at once• Add hard drives to the data file array• Add memory to cache scanned tables

Percent vs Order of Magnitude• Incremental (Percentage):

– Any improvement from 1% to 100%.

• Order of Magnitude:– Any improvement over 100% faster.

• How to explain it in your doc:– This mitigation should give an incremental

(percentage) improvement.– This mitigation should give an order of magnitude

improvement.

How To Explain It To Readers In this performance review, I classify mitigations as

giving us a Percentage Improvement (meaning 1-100% faster) or an Order of Magnitude Improvement (meaning over 100% faster).

In addition, because we’re talking about making multiple improvements simultaneously across different parts of the app and server, it’s hard to give exact numbers when one improvement might impact another area. A new index or server configuration change might improve performance for more than just one query or process.

This Is How DBAs Write:

Use A Different Way of Writing• Be friendly and upbeat – ideas at:

http://delicious.com/brento/powerpoint• Document like a consultant• Write like you’re an outsider looking in• Create clear, concise steps that

managers can assign to people

Don’t Point Fingers

Goal: Progress, Not Debating

Moving Your Career Forward

Your Target Reader…• Reads Fortune,

not SQL Magazine• Doesn’t understand

indexes or locks• Wants answers,

not questions• Only reads the

section headers

The Executive Summary

(Bunch of stuff here clipped out)

Wrapping It Up• Gather data with Profiler & Perfmon• Analyze the queries like it’s BI• Make a list of mitigation options• Write an upbeat, positive report• Aim for forwards, not replies• Save your reports for raise time!

My Resources, Your Questions• My blog about SQL and performance:

www.BrentOzar.comwww.BrentOzar.com/perfreport

• My bookmarks:Delicious.com/brento

• SQL Server community w/expert Q&A:SQLServerPedia.com

• Performance tools for a second opinion:www.Quest.com