/
SQL  Server Optimization for SQL  Server Optimization for

SQL Server Optimization for - PowerPoint Presentation

pamella-moone
pamella-moone . @pamella-moone
Follow
343 views
Uploaded On 2019-02-25

SQL Server Optimization for - PPT Presentation

Developers Anil Desai AnilAnilDesainet httpAnilDesainet Austin NET Users Group 04142014 Presentation Overview Database Performance Goals and Challenges Monitoring and Optimizing Performance ID: 753777

sql data performance database data sql database performance query queries tuning index indexes server azure sec practices transaction profiler

Share:

Link:

Embed:

Download Presentation from below link

Download Presentation The PPT/PDF document "SQL Server Optimization for" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.


Presentation Transcript

Slide1

SQL Server Optimization for Developers

Anil Desai (

Anil@AnilDesai.net

|

http://AnilDesai.net

)

Austin .NET User’s Group, 04/14/2014Slide2

Presentation Overview

Database Performance Goals and Challenges

Monitoring and Optimizing Performance

Understanding indexes

SQL Profiler and Database Engine Tuning Advisor

Tuning Database Queries

Understanding the Query Optimizer and Execution Plans

Seeing the effects of indexes

Application Design Best PracticesSlide3

Presentation and Demo Notes

For Developers, not database administrators

No focus on backups, fault-tolerance, data protection, replication, security, etc.

Demo Script

Run SQL Load Simulator with various queries (need to write queries)

Build WAIT FOR statements into the script

Disable indexes on Demo schema tables?

Use Profiler to capture trace data

Use Database Tuning Advisor to test workloadsSlide4

Best Practices:

Optimize for real-world workloads

Monitor/review performance regularly

Focus on specific issues

Performance Monitoring

ProcessSlide5

Monitoring and Troubleshooting ScenariosSlide6

Common

Datbaase

QuestionsSlide7

SQL Server EditionsSlide8

Database Performance ToolsSlide9

Backup Device

Device throughput Bytes/sec

Buffer Manager

Buffer cache hit ratio

Page reads / sec

Buffer Partition

Free Pages

Cache ManagerCache Hit RatioDatabasesActive Transactions

Data File Size

Log Growths

Percent Log Used

Transactions / sec

9

Useful SQL Server Performance Counters

General Statistics

Logins | logouts / sec

User Connections

Latches

Latch waits / sec

Locks

Lock requests / sec

Lock waits / sec

Memory Manager

Everything!

Replication

Depends on configuration

SQL Statistics

Batch requests / sec

SQL compilations / secSlide10

Database File Structure

SQL Server database file structure:

Primary data file (.

mdf

)

Secondary data files (*.

ndf)Transaction log files (*.ldf)

File groupsMoving database files:Attaching and detaching databasesStorage Goals:Reduce disk contention

Optimize read/write performanceSlide11

Database Design Issues

Transaction processing (OLTP)

Favors normalized schema

Many tables, each with fewer columns

Optimized for write (transactional) activity

Reporting and Analysis

Centralized, consistent storage of required dataFavored by denormalized schema

Fewer tables with many columns in eachData is aggregated from multiple sources into a data mart or data warehouseMay store aggregates in warehouseSlide12

Understanding Indexes

Index types

Clustered Index

Non-Clustered Indexes

Columnstore

indexes

Indexing strategiesGoal is ideal index coverageIndex maintenance can slow-down write operations (Insert, Update, Delete)

Referential IntegrityPrimary Key (default = clustered index)Foreign Key referencesConstraintsStatistics (manual vs. automatic)Slide13

General Index Tuning Best Practices

Make tuning a part of your development process

Dev: Use synthetic workloads and test cases

Test: Use real-world databases, whenever possible

Production: Capture real usage statistics for analysis

Collect a representative workload, whenever possible

Consider all applications and workloads when tuning a database

Use naming conventions for indexes and related objectsUse query hints sparingly (NOLOCK)Slide14

Purpose / Features: GUI for managing SQL Trace

Monitor important events

Capture performance data / resource usage

Replaying of workloads / transactions

Identifying performance bottlenecks

Correlation of data with System Monitor

Workloads for Database Tuning AdvisorExamples:Generate a list of the 100 slowest queries

Monitor all failed logins (Security)Using SQL ProfilerSlide15

Database Engine Tuning Advisor

Automatic workload analysis for Physical Design Structures (PDS)

Data Source:

File (Profiler Trace or .SQL files)

Table

Plan Cache

Tuning OptionsKeep existing PDS

Advanced Features: Partitioning, indexed views, etc.Slide16

Demo: Optimizing Indexes

Generate sample queries / tables

View query execution plans

View the effects of indexes on common queries

Capture Performance Data with SQL Profiler

SQL Profiler traces, events, and filters

Using SQL Load Generator to generate database loadCapturing and storing Analyzing and optimizing with Database Tuning Advisor

Analyzing index usage reportsSaving and applying index recommendationsSlide17

Tuning Individual Queries

Query Analyzer Features

Execution Plan (estimated and actual)

Include Client Statistics (multiple trials)

Analyze in Database Engine Tuning Advisor (single query)

Trace query in SQL Profiler (single query)

Keep query logic close to the databaseFilter returned data at the database layer

Minimize the size of result setsMinimize round-trips to the serverUse standard (inner) joins, where possibleConsider strategic

denormalization

for core sets of dataSlide18

Query Optimizer Details

Goal: Find the most efficient method to return the data

Come up with a plan

quickly

Minimize CPU, memory, and I/O requirements

Use statistics and index details to improve plans

Query plan cachingRelational engine vs. storage engineExecution Plan

outputSave as .sqlplan file for later analysisOutput in graphical, text, and XML formats

Can store and export plans using SQL Profiler (

ShowPlan

XML event)

Can use

query hintsSlide19

Understanding Execution Plans

Optimizing individual queries

Rewrite query logic

Use other objects (views, stored, procedures, etc.)

Strategic demoralization

Data Retrieval: Table scan, index seek/scan

Index UsageCovering indexesJoin conditionsSlide20

Execution Plan ExampleSlide21

Execution Plans in Windows AzureSlide22

Client Statistics ExampleSlide23

Demo: Tuning Queries

Viewing the effects of indexes on specific queriesSlide24

Application Design Best Practices

Create an abstraction layer between business and database objects

ADO.NET

Microsoft Enterprise Library

NHibernate

Entity Framework

Use caching wherever possibleServer-side (web services)

Application-level (middle tier)Client-side (desktop or mobile apps)Minimize transaction timesSlide25

Dev Best Practices: Application and Data ArchitectureSlide26

Windows Azure and Cloud Databases

Practical cloud benefits

Data

redundancy and geographic distribution

Lower management overhead

Potential issues

Keeping data close to applications and servicesData synchronizationNetwork performance issues

Data security, legal issues, and regulatory complianceDetermine where/how to use cloud-based servicesSaaS vs.

PaaS

vs.

IaaSSlide27

Azure Database Services

SQL Azure Database

Cost-effective, managed database instances

Can be managed with standard tools (Visual Studio and SSMS)

Some limitations (CLR, Mirroring, Partitioning, Replication, Extended SP’s)

Other Services

Azure Virtual Machines (SQL Server templates)Azure Web Sites (with gallery templates)

Azure HDInsight, Cache ServiceAzure Backup and Recovery ManagerSQL Azure Reporting

Network, Active Directory, Service Bus, etc.Slide28

Managing SQL Azure InstancesSlide29

ORM Considerations

General issues

Development efficiency vs. hardware/software efficiency

Latency, query inefficiency (outer joins), platform-specific optimizations

Frequency and number of server round-trips

ORM-generated queries can be inefficient

Difficult to tune or modify individual queriesPotential Solutions

Make sure entity relationships are correctCan use views or stored procedures to improve performance in some casesBypass the ORM for some types of operationsSlide30

New Features in SQL Server 2014

Memory-optimized

tables (

In-Memory OLTP)

Buffer Pool

Extension (for SSD usage)

Delayed durability

Async log writes can result in data lossEnable at database-level; use with BEGIN ATOMIC … COMMITResource Governor

storage I/O

limits

Updateable Clustered

C

olumnStore

indexes

Primarily for data warehousing; supports data index compression

Azure storage for SQL Server data/log files

Backup to Azure; Backup encryptionSlide31

Dev Best Practices: Managing Data

Large UPDATE or DELETE operations:

Use loops to minimize locking and transaction log growth

Large INSERT operations

Disable indexes and triggers (if present)

Use BULK INSERT,

bcp, SSIS, or DTSChange transaction isolation level (if appropriate)

Change recovery modelUse SQL to generate SQLExample: INSERT statementsSchedule or delay non-critical operationsSlide32

Dev Best Practices: Schema Changes

Generate Scripts

Script specific objects using SQL Server Management Studio

Script the entire database using Generate Scripts

Can include schema and/or data

Schema

changesUse ALTER commands when possibleDrop and recreate objects, as

neededMake all scripts re-runnableCheck before and after state of all objectsSlide33

Balance of concurrency (performance) vs. consistency

Affects SELECT queries

SET TRANSACTION ISOLATION LEVEL

Transaction Isolation Levels

READ UNCOMMITTED

READ COMMITTED

(default)

REPEATABLE READSERIALIZABLE

SNAPSHOT

Row-Versioning:

ALLOW_SNAPSHOT_ISOLATION

READ_COMMITTED_SNAPSHOT

Transaction Isolation LevelsSlide34

Dev Best Practices: Performance Testing

Build performance testing/optimization into the

dev

process

Develop load tests or test “harnesses”

Using synthetic load generation tools

Use representative test dataConsider caching effects:Index maintenance (fragmentation)

DBCC DropCleanBuffersDBCC FreeProcCacheSlide35

Advanced Performance Approaches

Database Federations

Vertical and horizontal

data partitioning

Cross-Server queries

Use Linked Servers to query across databases

Potential performance issuesData compression (row- or page-level)Resource governor

SQL Server Analysis Services (SSAS)Pre-aggregation for performanceDependent on a denormalized schema (optimized for reporting)Slide36

Links and References

Presenter:

http://AnilDesai.net

|

Anil@AnilDesai.net

Presentation slides and sample code

Microsoft TechNet Virtual Labs

Sample DatabasesAdventureWorks Sample Databases (CodePlex

)

Microsoft Contoso BI Demo Dataset

Database-related tools

SQL Load Generator

by David Darden (

CodePlex

)

Glimpse

Red Gate Software

SpotlightSlide37

Summary and Conclusion