1 extreme performance - part i

Extreme Performance for DBAs| ואלינורSQL Server MVPמאיר דודאי|

• All transaction isolation level• Differences between

Serializeable and Repateable Read

• RangeX-S locking vs RangeX-U• Deep dive into bookmark lookup • Page internals• Indexes on Sparse columns

• All transaction isolation level• Differences between

Serializeable and Repateable Read

• RangeX-S locking vs RangeX-U• Deep dive into bookmark lookup • Page internals• Indexes on Sparse columns

שנים...10לפני קצת יותר מ-

שנים...10לפני קצת יותר מ-

שנים...10לפני קצת יותר מ-

מטרות

P&Tהיכרות עם תהליך •

כלים חדשים ולא מוכרים •

Performanceלניהול

הבנת יישום נכון של •

Compression

Scale outהיכרות עם יכולות •

בגירסאות הנוכחיות

Denaliוב-

מי אני?

מאיר דודאי:•

–SQL Server MVP

יועץ בתחום בסיסי נתונים–

SQL שנים ב-10ניסיון של למעלה מ-–

Server

עורך האתר:–

ואלינורהחברה המובילה בישראל במתן שירותים מקצועיים •

SQL Server, Oracle, MySQLבתחום בסיסי נתונים: צוות יועצים מוביל המתמחה בכלל הרבדים של•

בסיסי הנתוניםOracle ו- Microsoftשותפים של •DBנציגי מוצרים מובילים בתחום ה-• לקוחות בכל המגזרים 300מעל •

My Assumptions About You

• You may be a part-time or full-time DBA• You are familiar with SQL Server in previous

versions• You would like to learn about new features in

SQL Server 2008&R2&2011 that can help you to manage and improve performance

• You are interested in good lunch, two days free from work, and some giveaways from advertisers

ארגז הכלים החדש שלכם

Agenda

• Utility Control Point• Detecting bottlenecks • New Performance Monitor in Windows

2008/R2 • Performance Data Collector

Agenda

• Utility Control Point• Detecting bottlenecks • New Performance Monitor in Windows

2008/R2 • Performance Data Collector

Database evolution

Number of database apps

Number of DBAs

1990 2000 2010

Hardware computing ca

pacityUnderutilized

hardware

Overburdened Administrators

Having fun with deployment

DAC Concepts

.dacpac = unit of deployment (data-tier application + developer intent)

Data-tier Application Component Improves collaboration

between developer and DBA Moves developers from a

procedural model to a declarative model

V1 is targeted at small apps

Data-tier Application Component

Schema

LOGICALTables, Views,

Procs, UDFs

PHYSICALUsers, Logins, Indexes

DAC Deployment ProfileDeployment Requirements,

Management Policies

Key Concept: Utility Control Point

• Provides unified view of an organization’s SQL Server usage

• Shows health of

• SQL Server instances

• Data-tier applications

• Database files, filegroups and volumes

• Provides resource monitoring

• CPU utilization

• Storage space

Management Studio

DBA

SQL0

2SQ

L03

SQL0

4

SQL0

1

Managed Instances

SQL0

5

UCP

DBA

SQL Server Management Studio

9

Managed Instances

HR

ControlPoint

SALE

S

DEVELOP DEPLOY MANAGE

Visual Studio 2010

Developer

FinAppProd DB

DBA

SQL Server Management Studio

FinAppDev DB1

Reverse Engineer

DAC

2

Create policies

3

Deploy /Upgrade

DAC

4

.dacpac

Compile+ Build

5

Hand-off to DBA

6

Deploy /Upgrade

DAC

7

Manage, Register, Uninstall, Extract,

Upgrade DAC

8

UTILITY CONTROL POINT

UCP

UCP

UCP Reports

UCP Reports

http://bit.ly/j8xMwH

UCP Reports

What’s missing?

• Supports only SQL Server 2008 R2 (or later)

• Better management• More counters can be

useful…• Monitors only DAC

– Or is it?

FAKE DAC

What’s next?

• Denali’s Contained Databases…

Agenda

• Utility Control Point• Detecting bottlenecks • New Performance Monitor in Windows

2008/R2 • Performance Data Collector

Performance Tools

• DTA • SSMS

– Built-in Reports– Activity Monitor (or what’s

left of it)

• Profiler assistants:– ClearTrace– Trace Analyzer

• UCP• BPA• Extended Events

SSMS Activity Monitor

PERFORMANCE REPORTS

USING PROFILER TO DETECT BLOCKS

CLEAR TRACE & TRACE ANALYZER

Agenda

• Utility Control Point• Detecting bottlenecks • New Performance Monitor in Windows

2008/R2 • Performance Data Collector

Windows Performance Tools

• Performance Monitor

• Reliability Monitor• Resource Monitor• PAL (Performance

Analysis of Logs)

PERFORMANCE & RESOURCE MONITOR

New performance counters

• Database Mirroring – Compression stats– Transactions volume– Mirroring performance

• Custom counters

CUSTOM COUNTERS

Agenda

• Utility Control Point• Detecting bottlenecks • New Performance Monitor in Windows

2008/R2 • Performance Data Collector

Monitor with Insight

•Performance data collection•OS perf indicators•Performance data•Extensible collection

•Central repository•Consolidate information•Open interface

•Management reports•Canned reports•Extensible

OPEN YOUR MIND!

Agenda

Magic settings

– RCSI

– Partition-level lock escalation

Architectural design

– Bulk Load

– StreamInsight

– Caching

– Offloading Traffic

– Denali HADR

GreenRoad Case Study:RCSI

• GreenRoad is an Israeli startup improving driving behavior for fleets and consumers

• Works as SAAS• More than 400 inserts/sec• Users running queries and reports frequently• Method used to increase concurrency:

RCSI: Read Committed Snapshot Isolation

Row-1

Tran2 (Select)Tran1 (Update)

X-Lock S-Lock BlockedRow-1

Reader Writer Blocking

Data Page

Read Committed Snapshot

• New “flavor” of read committed– Turn ON/OFF on a database

• Readers see committed values as of beginning of statement

• Writers do not block Readers

• Readers do not block Writers

• Writers do block writers

• Can greatly reduce locking / deadlocking without changing applications

DEMO

Lock Escalation

HOBT

Page Page Page

Row Row Row

T1: IX

T1: IX

T1: XT1: X

T1: XT1: X

T1: XT1: X

T1: XT1: X

T1: X

Lock Escalation

Lock Escalation

• Converting finer-grain locks to coarse grain locks.– Row to Table– Page to Table.

• Benefits– Reduced locking overhead– Reduces Memory requirement

• Triggered when– Number of locks acquired on a rowset > 5000– Memory pressure

Partitioned Tables and Indexes

• SQL Server 2005 introduced partitioning, which some customers use to scale a query workload– Another common use is to streamline maintenance and enable

fast range inserts and removals from tables

FG1 FG2 FG3

PartitionedTable

Partition 1 Partition 2 Partition 3

Lock Escalation: The Problem

• Lock escalation on partitioned tables reduces concurrency as the table lock locks ALL partitions

• Only way to solve this in SQL Server 2005 is to disable lock escalation

IXX

FG1 FG2 FG3

PartitionedTable

Partition 1 Partition 2 Partition 3

Query 1ESCALATE

Query 2

update update

Lock Escalation: The Solution

• SQL Server 2008 allows lock escalation to the partition level, allowing concurrent access to other partitions

• Escalation to partition level does not block queries on other partitions

IX

XFG1 FG2 FG3

PartitionedTable

Partition 1 Partition 2 Partition 3

Query 1ESCALATE

Query 2

update update

DEMO

Filtered Indexes

• An index with a WHERE clause to specify a criteria

• Essentially index only a subset of a the table– Query optimizer most likely to use when WHERE

clause matches that of the filtered index

Using Filtered Indexes

• Advantages of Filtered Indexes– Improve query performance, partly by

enhancing execution plan quality– Smaller index maintenance costs– Less disk storage

Using Filtered Indexes

• Scenarios for Filtered Indexes– Sparse columns, where most data is null– Columns with categories of values– Columns with distinct ranges of values

FILTERED INDEXES

• New server option in SQL Server 2008

• Only a stub is cached on first execution

• Full plan cached after second execution

• SP_CONFIGURE 'show advanced options',1RECONFIGUREGO

• SP_CONFIGURE 'optimize for ad hoc workloads',1RECONFIGUREGO

Optimize for ad hoc workloads

• “I would not use it on some DWH systems”• “Why is this not on by default?”• “I can’t think of a reason why opt for adhoc

workloads would ever really be a problem”• “I definitely recommend it!”• “Best movie of 2011! Breathtaking!”• “But paintball guns are also not very pleasant!”

How good is it?

Codename Lyngby Case Study: Power of bulk inserts

• Security/audit system• 50GB of data inserted daily• Data is kept for one year and should be

available for queries and reports at any time• Method used to increase concurrency:

Bulk inserts

Benefits of Bulk Inserts

• Improved concurrency• Allow application-database decoupling• In Lyngby case – 50GB a day!

http://www.tapuz.co.il/blog/userBlog.asp?FolderName=Hygge

Bulk Inserts & RCSI: SQLCAT Benchmark

•What happens when you query a table while bulk inserts are running?

Bulk Inserts & RCSI: SQLCAT Benchmark

• And with RCSI?

• Bulk loading does not affect the size of the version store in tempdb under RCSI

• Keep in mind: RCSI adds 14 noncompressible bytes into every row in every table

• Bottom line: RCSI works extremely well with bulk inserts and achieves improved concurrency

• More details about this benchmark:

http://sqlcat.com/technicalnotes/archive/2009/04/06/bulk-loading-data-into-a-table-with-concurrent-queries.aspx

StreamInsight:Complex Event Processing

• SQL Server 2008 R2 feature• Processing and querying of event data streams• Data queried while “in flight”• May involve multiple concurrent event sources• Works with high data rates• Aims for near-zero latency

Isn’t This Just a Database Application?

Database CEP

Queries Ad hoc on stored data Continuous standing queries

Latency Seconds Milliseconds

Data Rate Hundreds per Second Tens of thousands per second

request

response

Eventoutput streaminput

stream

Caching

• Your database can get some rest...

Microsoft AppFabric

Other solutions

• Memcache– Requires application change

• 3rd party– Usually transparent

Tapuz Case Study: Offloading Traffic

• Tapuz is the leading user content based website in Israel– 1,200 forums– 74,000 blogs– 500,000 visitors each month

• More than 4000 queries/sec• Method used to increase concurrency:

Offloading using Transactional Replication

COMPRESSION

As data volume grows…

• Large databases =

• Storage Cost

• Workload Performance

• Manageability Cost

• Backup/Recovery

Solution – Compress Data Files?

• No benefit in-memory• Need to compress larger chunks• Does not scale for random access

Solution – SQL Server 2008 / R2

• Store data efficiently in the row/page

– (+) More data can fit in memory– (+) Better Performance for I/O

bound workload– (-) Performance degradation for

CPU bound workload

Data Compression

• SQL Server 2008 – ROW and PAGE

compression– Backup Compression

• SQL Server 2008 R2– Unicode compression

Enabling Compression Examples

•Latest partition uncompressed

Jan-Mar Apr-June July-Sept Oct-Dec

PAGE Compressed

Uncompressed

ROW Compressed

Should it be so complex?

In real life – usually compress the entire large tables using page compression…

Summary – Compression

• Can reduce size of database significantly• Lower total cost of ownership (TCO)• Easy to enable/disable• No application changes• Performance gains!

CONTROL YOUR RESOURCES

Resource Hogs

• What are some of the biggest resource hogs a DBA runs into?– Bulk Load Processes– Data Archiving– Poorly Coded Queries (Runaways)– Reporting…

Reporting in Production

• Is this a best practice???

Reporting In Production

• NO!

Reporting In Production

• NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO

Reporting In Production

OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!

Resource Governor

• Similar to a governor you would find on a car• It limits the “speed” that a SQL Server session

can run at

Resource Governor – Workloads

• Ability to differentiate workloads based on connection property– e.g. app_name, login,

user, db name• Per-request limits

– Max memory %– Max CPU time– Grant timeout– Max Requests