performance tuning for pirates!

PERFORMANCE TUNING FOR

PIRATES!

HTTP://JOHNSTERRETT.COM/GO/PERFORMANCE

John Sterrett (@JohnSterrett)

ABOUT John…

I am a Remote DBA Expert • Responsible 100+ Instances of SQL Server

• Senior DBA on Integration Team

Mid-Atlantic Region Mentor

Founder of WVPASS (SQL UG)

Host of SQL Saturday #36 and #80

I reside in Wheeling, WV (Pittsburgh, PA)

Pirates and Yankees

Photos: http://www.flickr.com/photos/jaubele1/4299542394/ and http://www.flickr.com/photos/wenzday01/2537854102

Objectives

• Eliminate the Obvious

• Know the process

• Know the common bottlenecks

• Know your Tools – Wait Stats

– Performance Monitor & PAL (WMI Counters)

– DMV’s and scripts

– SQL Server Side Trace (SQL Profiler)

– Clear Trace

– SQL Diag

– SQL Nexus

– RML Utilities

• ReadTrace

• Ostress

• Know where to go for help...

Eliminate the Obvious

Eliminate the OBVIOUS

http://theiphonefever.blogspot.com/2010/10/movies-you-should-watch-office-space.html

Know the process!

Generate workload

Capture workload

Analyze captured workload

Make Changes

12 Steps to workload Tuning

1. Backup Production Database

2. Capture Production Workload

3. Analyze Workload and Script Changes (Don’t

Implement)

*** Done with Production ***

Implement)

4. Restore Backup on Test Server

Implement)

5. Prepare to capture Workload on Test Server

Implement)

6. Replay Production Workload on Test Server

Implement)

7. Analyze to Establish Baseline

Implement)

9. Make Changes to Improve Performance on Test Server

3. Analyze Workload and Script Changes (Don’t Implement)

10. Prepare to Capture Production Workload on Test Server

11. Replay Workload on Test Server

12. Analyze and Compare Workload with Baseline

*** Repeat Steps 8 to 12 as needed ***

Where can things Go wrong?

Meet the Bottlenecks

Photos: http://www.flickr.com/photos/kaiban/5159888367/lightbox/ and http://www.flickr.com/photos/moodog/504456253 and http://www.flickr.com/photos/marksze/4231115464 and http://www.flickr.com/photos/vvvracer/5264339383 and http://www.flickr.com/photos/iphil_photos/4962369046

MEMORY DISK (SPINDLES) CPU

NETWORK LOCKING, BLOCKING & DEADLOCKS

3 Options To Resolve bottlenecks

1. Reduce Workload

2. Tune Workload to reduce bottleneck resource

1. Reduce Workload

2. Tune Workload to reduce bottleneck resource

3. Add additional Hardware

Viewing Wait Stats

• Sys.dm_os_wait_stats

• Wait stats can be cleared manually or during restart.

– DBCC SQLPERF(‘sys.dm_os_wait_stats’, CLEAR)

• Focus on waits dedicated to bottlenecks

Common WAIT STATS

• ASYNC_NETWORK_IO

• LCK_M_*

• IO_COMPLETION

• PAGELATCH_* or PAGEIOLATCH_*

• WRITELOG

• CX_PACKET

• SOS_SCHEDULER_YIELD

DEMO!!!!

• Review Wait Statistics

Memory Performance counters

Object Counter

You Want Description

Memory Available Mbytes

> 256 MB Unused physical memory

Memory Pages Input/sec

< 10 Reads from hard disk per second to memory

SQL Server: Buffer Manager Page Life Expectancy

> 300 On average, how many seconds SQL Server expects page to say in cache (memory)

SQL Server: Memory Manager Total Server Memory (KB)

Shows amount of memory SQL Server is currently using.

SQL Server: Memory Manager Target Server Memory (KB)

Amount of memory that SQL Server wants to use based on configured max Server Memory setting

SQL Server: Memory Manager Memory Grants Pending

0 Total number of processess pers second waiting for workspace memory grants

SQL Server: Buffer Manager Free Pages

> 640 Total number of pages avaiable across all free lists. A value under 640

SQL Server: Buffer Manager LazyWrites/Sec

< 20 Number of times per second that the lazywritter process moves dirty page from buffer to disk.

Disk Performance Counters

Object

Counter

Physical Disk Avg Disk Sec/Read

< 25ms

Avg. Disk sec/Read is the average time, in seconds, of a read of data from the disk.This

analysis determines if any of the physical disks are responding slowly.

Physical Disk Avg Disk Sec/Write

< 25ms

Avg. Disk sec/Write is the average time, in seconds, of a write of data to the disk.

Physical Disk Disk Reads/sec

Disk Reads/sec is the rate of read operations on the disk.

Physical Disk Disk Writes/sec

Disk Writes/sec is the rate of write operations on the disk.

• The sys.dm_io_virtual_file_stats DMV can be used to get disk latency for individual sql server files.

CPU Performance Counters

Object

Counter

Process (sqlservr) %Processor Time

< 80% Percentage of processor time spent on SQL Server process threads.

Processor %Processor Time

< 80% Percentage of elapsed time the processor spends executing non-idle threads.

General Workload performance

counters

Object

Counter

SQL Server: SQL Statistics Batch Requests/Sec (SQL2005/08)

Number of SQL batch requests received by server. This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are.

SQL Server: SQL Statistics SQL Compilations/sec

< 10% of the number of batch request/sec

Number of SQL compilations.

SQL Server: SQL Statistics SQL Re-Compilations/sec

< 10% of the number of compilations/sec

Number of SQL re-compiles. This needs to be 0 as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.

SQL Server: SQL Statistics User Connections

SQL Server: Access Methods Full Scans/sec

1 Full scan for every 1000 index searches

This counter monitors the number of full scans on base tables or indexes. High values indicate that we may be having performance issues due to table / index page scans.

SQL Server: Access Methods Index Searches/sec

This counter measures the number of index searches per second. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index and table scans.

SQL Server: Access Methods Page Splits/sec

Number of page splits per second that occur as a result of overflowing index pages. Reduce non-sequential inserts or use fill factor and pad_index to leave more empty space per page.

Locking Performance Counters

Object

Counter

SQL Server: Locks Number of Deadlocks/sec

Number of lock requests per second

that resulted in deadlock.

SQL Server: Locks Table Lock Escalations/sec

Number of times SQL escalated

locks to table level. This can indicate

poorly coded transactions.

SQL Server: Locks Avg Wait Time (ms)

The average wait time for each lock

request that had to wait.

SQL Server: General Statistics Processes blocked

Number of currently blocked

processes.

DEMO!!!

• Review Perfmon

• Review PAL Tool – Collection Process

– Generating Reports

– Reviewing Reports

Execution DMV’s

• Sys.dm_exec_session – Returns one row per authenticated session, including user and

system processes.

• Sys.dm_exec_requests – Returns one row for each request executing within SQL Server,

doesn’t include code executed outside of SQL (distributed queries, extended stored procedures etc…)

• Sys.dm_exec_query_stats – Returns one row per query statement with cached plan, detailing

aggregated performance stats for cached plans.

• Sys.dm_exec_connections – Returns server information about a connection to SQL Server

including client network address, TCP Port etc..

Query details DMV’S

• Sys.dm_exec_sql_text

– Returns the text of the SQL Server batch that is identified by

specific sql handle.

• Sys.dm_exec_text_query_plan

– Returns the showplan output in text format.

• Sys.dm_query_plan

– Returns a given query’s showplan output in xml.

INDEX DMV’s

• Sys.dm_db_index_usage_stats

– Returns a count of index operations including seeks, scans, lookup

and updates

• Sys.dm_db_index_physical_stats

– Returns size and fragmentation information for data pages and

indexes.

• DMV’s to find recommended indexes

– Sys.dm_db_missing_index_columns

– Sys.dm_db_missing_index_details

– Sys.dm_db_missing_index_groups

– Sys.dm_db_missing_index_group_stats

SQLOS DMV’s

• Sys.dm_os_sys_memory

– Returns complete picture of memory at the os level including total and available memory.

• Sys.dm_os_tasks

– Returns a row for each OS task thais is active in the instance of SQL Server.

• Sys.dm_os_threads

– Returns a row for each SQLOS thread running under the SQL Server process.

• Sys.dm_os_performance_counters

– Returns a row for each SQL Server perfmon counter.

• Sys.dm_os_waiting_tasks

– Returns information about the wait queue of SQLOS. This would be tasks waiting on resources like blocking and latch contention etc..

DMV Queries (DEMO)

• What is Running?

• Probing the Proc Cache

http://www.flickr.com/photos/democonference/4458079104

SQL Profiler (DEMO!)

• Generate Server Side traces

– Tuning Columns and Events

– Remove the noise

• Read Trace with Clear Trace

SQL Diag (DEMO)

• Review SQL Diag

• Review PerStats Scripts

• Collect perfmon, sql trace, blocking and more…

SQL NEXUS (DEMO)

• Import SQLDiag

• Analysis collected data

RML Utilities (DEMO)

• Ostress (Replay)

• ReadTrace (Compare Workload to Baseline)

Help I need Some One!!!!

• Performance Virtual Chapter

– http://performance.sqlpass.org/

• Twitter (#sqlhelp)

• Message boards

– www.sqlservercentral.com

– www.msdn.com

• Blogs

• Consultants

Where to go next?

• HTTP://JOHNSTERRETT.COM/GO/PERFORMANCE

– Videos

– Reference Links

– Slides

Thank You to our Sponsors

performance tuning for pirates!

workload tuning1

tune workload

workload tuning8

backup production database2

test server5

test server6

test server7

capture production workload3

Technology

performance and tuning: monitoring and...

postgresql performance tuning

sql performance and tuning sql performance and tuning

mariadb training - downloads · mariadb performance tuning...

weblogic performance tuning training | weblogic performance...

sap performance tuning

informatica performance tuning

performance tuning php

cobol performance tuning

performance tuning mysql

bpc performance tuning

automatic tuning of parallel read and write...

tomcatx performance tuning

performance tuning settings

java performance tuning

performance tuning xen

tomcat performance tuning

tuning matlab for better performance · matlab's...

performance & tuning guide

performance tuning overview