SQL Server Best Practices - Anil Desai

SQL Server Best Practices - Anil Desai

SQL Server Optimization for Developers ANIL DESAI ([email protected] | HTTP://ANILDESAI.NET) AUSTIN .NET USERS GROUP, 04/14/2014 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 Practices Performance Monitoring Process Best Practices: Optimize for realworld workloads Monitor/review performance regularly Focus on specific issues

Monitoring and Troubleshooting Scenarios Common Datbaase Questions Database Performance Tools 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 data Favored by denormalized schema Fewer tables with many columns in each Data is aggregated from multiple sources into a data mart or data warehouse May store aggregates in warehouse Understanding Indexes

Index types Clustered Index Non-Clustered Indexes Columnstore indexes Indexing strategies Goal is ideal index coverage Index maintenance can slow-down write operations (Insert, Update, Delete) Referential Integrity Primary Key (default = clustered index)

Foreign Key references Constraints Statistics (manual vs. automatic) 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 objects Use query hints sparingly (NOLOCK) Using SQL Profiler 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 Advisor Examples: Generate a list of the 100 slowest queries

Monitor all failed logins (Security) Database Engine Tuning Advisor Automatic workload analysis for Physical Design Structures (PDS) Data Source: File (Profiler Trace or .SQL files) Table Plan Cache Tuning Options

Keep existing PDS Advanced Features: Partitioning, indexed views, etc. 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 load Capturing and storing Analyzing and optimizing with Database Tuning Advisor Analyzing index usage reports Saving and applying index recommendations 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 database Filter returned data at the database layer Minimize the size of result sets Minimize round-trips to the server

Use standard (inner) joins, where possible Consider strategic denormalization for core sets of data 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 caching

Relational engine vs. storage engine Execution Plan output Save as .sqlplan file for later analysis Output in graphical, text, and XML formats Can store and export plans using SQL Profiler (ShowPlan XML event) Can use query hints 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 Usage Covering indexes Join conditions Execution Plan Example Execution Plans in Windows Azure

Client Statistics Example Application Design Best Practices Create an abstraction layer between business and database objects ADO.NET Microsoft Enterprise Library NHibernate Entity Framework Use caching wherever possible

Server-side (web services) Application-level (middle tier) Client-side (desktop or mobile apps) Minimize transaction times Dev Best Practices: Application and Data Architecture Architecture Standards-based communications Loosely-coupled tiers and components Development / Testing Test harness Unit tests that use data Query performance monitoring

Performance and Monitoring Windows PerfMon Counters Instrumentation and Logging Alerts / pro-active corrections Auto-scaling Reliability Error and event handling Transaction retry (random interval); Deadlock management User notifications (responsive UI / cancel and retry options) Windows Azure and Cloud Databases Practical cloud benefits Data redundancy and geographic distribution

Lower management overhead Potential issues Keeping data close to applications and services Data synchronization Network performance issues Data security, legal issues, and regulatory compliance Determine where/how to use cloud-based services SaaS vs. PaaS vs. IaaS 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 SPs) Other Services Azure Virtual Machines (SQL Server templates) Azure Web Sites (with gallery templates) Azure HDInsight, Cache Service

Azure Backup and Recovery Manager SQL Azure Reporting Network, Active Directory, Service Bus, etc. Managing SQL Azure Instances 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 queries Potential Solutions Make sure entity relationships are correct Can use views or stored procedures to improve performance in some cases Bypass the ORM for some types of operations 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 loss Enable at database-level; use with BEGIN ATOMIC COMMIT Resource Governor storage I/O limits Updateable Clustered ColumnStore indexes Primarily for data warehousing; supports data index compression

Azure storage for SQL Server data/log files Backup to Azure; Backup encryption Dev Best Practices: Managing Data Large UPDATE or DELETE operations: Large INSERT operations Disable indexes and triggers (if present) Use BULK INSERT, bcp, SSIS, or DTS

Change transaction isolation level (if appropriate) Change recovery model Use SQL to generate SQL Use loops to minimize locking and transaction log growth Example: INSERT statements Schedule or delay non-critical operations 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 changes Use ALTER commands when possible Drop and recreate objects, as needed Make all scripts re-runnable Check before and after state of all objects 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 data Consider caching effects: Index maintenance (fragmentation) DBCC DropCleanBuffers

DBCC FreeProcCache Advanced Performance Approaches Database Federations Vertical and horizontal data partitioning Cross-Server queries Use Linked Servers to query across databases Potential performance issues Data compression (row- or page-level)

Resource governor SQL Server Analysis Services (SSAS) Pre-aggregation for performance Dependent on a denormalized schema (optimized for reporting) Links and References Presenter: http://AnilDesai.net | [email protected] Presentation slides and sample code Microsoft TechNet Virtual Labs

Sample Databases AdventureWorks Sample Databases (CodePlex) Microsoft Contoso BI Demo Dataset Database-related tools SQL Load Generator by David Darden (CodePlex) Glimpse Red Gate Software

Spotlight Summary and Conclusion

Recently Viewed Presentations

  • Native Sports Team Names

    Native Sports Team Names

    This includes roughly 214,000 non-status Indians and 451,000 Métis. These groups will now have the ability to negotiate with Ottawa over rights, treaties, services, and benefits . The Trudeau government set aside $8.4 billion of new funding in its first...
  • Operator Requirements for Infrastructure Management Steve Feldman NANOG

    Operator Requirements for Infrastructure Management Steve Feldman NANOG

    Introduction IETF Operations/Network Management area outreach to network operators Find out what operators really use and want Help set agenda for future IETF work Recommendations to vendors Meetings May 2001: NANOG - Scottsdale, AZ August 2001: IETF - London, UK...
  • Bayesian models of human inductive learning Josh Tenenbaum

    Bayesian models of human inductive learning Josh Tenenbaum

    Link observed in training Link observed in transfer test "blessing of abstraction" Charles Kemp Pat Shafto Vikash Mansinghka Amy Perfors Lauren Schmidt Chris Baker Noah Goodman Lab members Tom Griffiths (alum) Funding: AFOSR Cognition and Decision Program, AFOSR MURI, DARPA...
  • ERCOT Readiness Update

    ERCOT Readiness Update

    Mick Hanna. Settlements Analyst III. ... Joey Liao. Lead Planning Engineer/Analyst ... Arial Arial Black Wingdings Times New Roman Custom Design Microsoft Office Excel Chart Texas Nodal Program ERCOT Readiness Update Slide 2 Slide 3 Slide 4 Slide 5 Slide...
  • Medien- und Methodenkompetenz - WordPress.com

    Medien- und Methodenkompetenz - WordPress.com

    Medium und Quelle werden fälschlicherweise oft synonym verwendet. Medium ist der breitere Begriff. Kategorisierung der Medien nach Pandel und Schneider ... Visuelle Medien - z.B. Bild, Karikatur, Plakat, Comic, Film, Bildgeschichte, Graffiti. Akustische Medien - z.B. Musik, Oral History ...
  • 1 unlocking potential annual learning and teaching conference

    1 unlocking potential annual learning and teaching conference

    Data Delight collection of class data to support statistics teaching via StudyNet Diana Kornbrot and Rachel Msetfi Data Delight Aims Original Aims 2003 - 2004 A tool to collect student data in EXCEL Exercises, in workshops, student relevant, motivating Web...
  • Achievements, Challenges, and Future Directions - USGS Water ...

    Achievements, Challenges, and Future Directions - USGS Water ...

    It works well for freshwater conditions when the devices can be secured over the stream and looking straight down. But even minor amounts of salt or minerals imped penetration of the stream and greatly reduce the accuracy of the measurements....
  • The Great Gatsby: - WordPress.com

    The Great Gatsby: - WordPress.com

    James Gatz vs. Jay Gatsby Jay Gatsby was born James Gatz, and came from an immigrant family. He knew that to truly achieve the American Dream, he needed to be truly American, and that meant erasing his immigrant roots and...