Developers Anil Desai AnilAnilDesainet httpAnilDesainet Austin NET Users Group 04142014 Presentation Overview Database Performance Goals and Challenges Monitoring and Optimizing Performance ID: 753777
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.
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