“The Day After Tomorrow”;why you need to baseline

Richard DouglasDell Software - Systems

Consultant

8th Movember 2013

2

Join Local Chapters

JOIN THE PASS COMMUNITY

Become a PASS member for free and join the world’s

biggest SQL Server Community.

Access to online training content

Access to events at discounted

rates

Join Virtual Chapters

Personalize your PASS website experience

Who’s this guy?

•Richard Douglas

•Editor in Chief – ToadWorld.com

•MCITPro

•Maidenhead PASS Chapter Leader

•Blog: http://SQL.RichardDouglas.co.uk

•Twitter: @SQLRich

•Email: Richard.Douglas@Software.Dell.com

Agenda

• What is a “baseline”?

• What is “benchmarking”?

• Where do we start?

• What should I capture?

• What should I capture it with?

What is a “baseline”?

•Typical state

•Average over a time period

•Multiple baselines

Why baseline?•Line in the sand

•Usage patterns

A measurement or calculation used as a basis for comparison.

What is “benchmarking”?

Allows you to make informed decisions

A level by which something can be measured or judged

Performance tuning lifecycle

Identify bottlenec

k

Create / update

baseline

Define goal

Deploy change

Develop / test

solution

Obligatory analogy

Analyse

Make a cup of coffee

Make the best

coffee

Make change

Decide on

change

It depends

When should I capture it?

•Consider different baselines for different business periods– Maintenance windows– Month/Quarter/Year end– Seasonal peaks

•After Windows and SQL Server patches

•After failovers / DR scenarios

•After any new project deployment

What should I capture?

System Configuratio

n

Windows OS Counters

SQL Server Counters

Wait statistics

System configuration

What should I capture?

•Infrastructure diagrams.

•Windows and SQL Server version information.

•Driver information

•IO Subsystem information

•System catalogue information– Sys.configurations– Sys.databases– Sys.master_files

Operating System / SQL Server Counters - Memory

What should I capture?

•Memory: Available Mbytes

•Paging File: %Usage

•SQL Server Memory Manager: Target Server Memory(KB)

•SQL Server Memory Manager: Total Server Memory(KB)

•SQL Server Memory Manager: Memory Grants Pending

•SQL Server Buffer Manager: Buffer cache hit ratioSQL Server Buffer Manager: Page Life Expectancy

•SQL Server Buffer Manager: Extension Page Unreferenced Time

•SQL Server Buffer Manager: Database Pages

•SQL Server Buffer Manager: Procedure Cache Pages

0

300

PLE * (16 or 32)

VMWare

Virtualisation Considerations

•Memory Limit (MB)

•Memory Reservation (MB)

•Memory Ballooned (MB)

•Memory Swapped (MB)

Read more about VMWare memory settings here:http://www.vmware.com/files/pdf/techpaper/vsp_41_perf_memory_mgmt.pdf

Operating System / SQL Server Counters - CPU

What should I capture?

•Processor: % Processor Time

•Process: % Processor Time (SQLServr)

•System: Processor Queue Length

•System: Context Switches/Sec

•SQL Server SQL Statistics: SQL Compilations/Sec

•SQL Server SQL Statistics: SQL ReCompilations/Sec

LT 80%

LT 80%

LT 12 goodideally LT 4

LT 3000 goodideally LT 1500

Operating System / SQL Server Counters - IO

What should I capture?

•Physical Disk: Current Disk Queue Length

•Physical Disk: Avg. Disk Sec/Read

•Physical Disk: Avg. Disk Sec/Write

•Physical Disk: Avg. Bytes/Read

•Physical Disk: Avg. Bytes/Write

?

LT 20ms

LT 10ms

SAN Considerations

•Virtualised storage– How is it connected?– How many spindles?– How many other servers share this?

•Dynamic storage– Your data may move!!!!!

•What’s a good way to test for consistency?– Baseline your maintenance window(s)

SQL Server Counters

What should I capture?

•SQL Server Access Method: Forwarded Records/Sec

•SQL Server Access Method: Page Splits/Sec

•SQL Server General Statistics: User Connections

•SQL Server SQL Statistics: Batch Requests/Sec

•SQL Server Buffer Manager: Page Reads/Sec

•SQL Server Buffer Manager: Page Writes/Sec

Ideally 0

It depends*

Beware pooling

LT 90

LT 90 **

*Page splits include “regular” new page allocations

** Cross reference this with Checkpoint and Lazy Writer counters

What should I capture?

•Query information– Understand the server workload– Consider exporting plans from the cache.

•Job information– Are my jobs taking longer?

•Wait statistics– What is SQL Server waiting on?

What free tools can I capture it with?•Performance Monitor (OS + SQL Server Counters)

•Your favourite T-SQL editor - SSMS or Toad for SQL Server freeware (Dynamic Management Objects)

•Profiler / Extended events (Query information)

How do I analyse?

•Import data into Microsoft Excel– http://

www.toadworld.com/platforms/sql-server/w/wiki/10421.performance-monitor.aspx

– http://bit.ly/YXOfZD - Brent Ozar at SQLBits

DEMO

Reactive

Proactive Analyze trends Set thresholds Predict problems Monitor end-user

response time Automate Mature problem,

configuration, change, asset and performance mgt processes

Fight fires Inventory Desktop sw

distribution Initiate

problem mgt process

Alert and event mgt

Monitor component availability

IT as a service provider

Define services, classes, pricing

Understand costs Guarantee SLAs Monitor and report

service availability Capacity

mgt

Service

Value IT as strategic

business partner IT and business

metric linkage IT/business

collaboration improves business process

Real-time infrastructure

Business planning

Level 1

Level 2

Level 3

Level 4

Chaotic

Ad hoc Undocumented Unpredictable Multiple help

desks Minimal IT

operations User call

notification

Level 0

Tool Leverage

Manage IT as a Business

Service Delivery Process Engineering

Operational Process Engineering

Service and Account Management

Summary

“The Day After Tomorrow”;why you need to baseline

•What a “baseline” is.

•What “benchmarking” means.

•How to plan your baseline.

•How to choose your measures.

•Native tools.

Any questions?

“The Day After Tomorrow”;why you need to baseline

Richard Douglas@SQLRichhttp://SQL.RichardDouglas.co.uk