Microsoft SQL Server 2012 Performance Tuning Cookbook

Microsoft SQL Server 2012 Performance Tuning Cookbook

by Ritesh Shah, Bihag Thaker
Released July 2012
Publisher(s): Packt Publishing
ISBN: 9781849685740

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

With this book you’ll learn all you need to know about performance monitoring, tuning, and management for SQL Server 2012. Includes a host of recipes and screenshots to help you say goodbye to slow running applications.

  • Learn about the performance tuning needs for SQL Server 2012 with this book and ebook
  • Diagnose problems when they arise and employ tricks to prevent them
  • Explore various aspects that affect performance by following the clear recipes

In Detail

As a DBA you must have encountered a slow running application on SQL Server, but there are various factors that could be affecting the performance. If you find yourself in this situation, don't wait, pick up this book and start working towards improving performance of your SQL Server 2012. SQL Server 2012 Performance Tuning Cookbook is divided into three major parts -- Performance Monitoring, Performance Tuning, and Performance Management--that are mandatory to deal with performance in any capacity.

SQL Server 2012 Performance Tuning Cookbook offers a great way to manage performance with effective, concise, and practical recipes. You will learn how to diagnose performance issues, fix them,and take precaution to avoid common mistakes.

Each recipe given in this book is an individual task that will address different performance aspects to take your SQL Server's Performance to a higher level.

The first part of this book covers Monitoring with SQL Server Profiler, DTA, System statistical function, SPs with DBCC commands, Resource Monitor and Reliability, and Performance Monitor and Execution Plan.

The second part of the book offers Execution Plan, Dynamic Management Views, and Dynamic Management Functions, SQL Server Cache and Stored Procedure Recompilations, Indexes, Important ways to write effective TSQL, Statistics, Table and Index Partitioning, Advanced Query tuning with Query Hints and Plan Guide, Dealing with Locking, Blocking and Deadlocking and Configuring SQL Server for optimization to boost performance.

The third and final part gives you knowledge of performance management with help of Policy Based Management and Management with Resource Governor.

"

Table of contents

  1. Microsoft SQL Server 2012 Performance Tuning Cookbook
    1. Table of Contents
    2. Microsoft SQL Server 2012 Performance Tuning Cookbook
    3. Credits
    4. About the Authors
    5. Acknowledgement
    6. Acknowledgement
    7. About the Reviewers
    8. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    9. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    10. 1. Mastering SQL Trace Using Profiler
      1. Introduction
      2. Creating a trace or workload
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Some background of SQL Trace
            1. SQL Trace terms and concepts
              1. SQL Trace
              2. SQL Server Profiler
              3. Event
              4. Event class
              5. Event category
              6. Data column
              7. Trace
              8. Trace properties and Trace definition
              9. Filter
              10. Trace file
              11. Trace table
              12. Trace template
            2. Architecture of SQL Trace
              1. Trace and workload
          2. Commonly-used event classes
          3. Commonly-used data columns
      3. Filtering events
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Detecting slow running and expensive queries
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Trace templates
      5. Creating trace with system stored procedures
        1. Getting ready
        2. How to do it...
          1. How it works...
    11. 2. Tuning with Database Engine Tuning Advisor
      1. Introduction
      2. Analyzing queries using Database Engine Tuning Advisor
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Running Database Engine Tuning Advisor for workload
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Executing Database Tuning Advisor from command prompt
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    12. 3. System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command
      1. Introduction
      2. Monitoring system health using system statistical functions
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
      3. Monitoring with system stored procedure
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
      4. Monitoring log space usage statistics with DBCC command
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
    13. 4. Resource Monitor and Performance Monitor
      1. Introduction
      2. Monitoring of server performance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Monitoring CPU usage
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Monitoring memory (RAM) usage
        1. Getting ready
        2. How to do it...
        3. How it works...
    14. 5. Monitoring with Execution Plans
      1. Introduction
      2. Working with estimated execution plan
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Working with actual execution plan
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Monitoring performance of a query by SET SHOWPLAN_XML
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Monitoring performance of a query by SET STATISTICS XML
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Monitoring performance of a query by SET STATISTICS IO
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Monitoring performance of a query by SET STATISTICS TIME
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Including and understanding client statistics
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    15. 6. Tuning with Execution Plans
      1. Introduction
      2. Understanding Hash, Merge, and Nested Loop Join strategies
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Finding table/index scans in execution plan and fixing them
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Introducing Key Lookups, finding them in execution plans, and resolving them
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    16. 7. Dynamic Management Views and Dynamic Management Functions
      1. Introduction
      2. Monitoring current query execution statistics
        1. Getting ready
        2. How to do it......
          1. How it works...
          2. There's more...
            1. sys.dm_exec_connections (DMV)
            2. sys.dm_exec_sessions (DMV)
            3. sys.dm_exec_requests (DMV)
            4. sys.dm_exec_sql_text (DMF)
            5. sys.dm_exec_query_plan (DMF)
            6. sys.dm_exec_cursors (DMF)
      3. Monitoring index performance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. sys.dm_db_missing_index_details (DMV)
          2. sys.dm_db_missing_index_groups (DMV)
          3. sys.dm_db_missing_index_group_stats (DMV)
          4. sys.dm_db_index_usage_stats (DMV)
          5. sys.dm_db_index_physical_stats (DMF)
      4. Monitoring performance of TempDB database
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. sys.dm_db_session_space_usage (DMV)
          2. sys.dm_db_file_space_usage (DMV)
      5. Monitoring disk I/O statistics
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
            1. dm_io_virtual_file_stats (DMF)
            2. dm_io_pending_io_requests (DMV)
    17. 8. SQL Server Cache and Stored Procedure Recompilations
      1. Introduction
      2. Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Monitoring recompilations using SQL Server Profiler
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    18. 9. Implementing Indexes
      1. Introduction
      2. Increasing performance by creating a clustered index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
          1. Heap
          2. Table and Index Scan/Seek
      3. Increasing performance by creating a non-clustered index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Increasing performance by covering index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Increasing performance by including columns in an index
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Improving performance by a filtered index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. Improving performance by a columnstore index
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
    19. 10. Maintaining Indexes
      1. Introduction
      2. Finding fragmentation
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Playing with Fill Factor
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Enhance index efficiency by using the REBUILD index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Enhance index efficiency by using the REORGANIZE index
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      6. How to find missing indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      7. How to find unused indexes
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      8. Enhancing performance by creating an indexed view
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      9. Enhancing performance with index on Computed Columns
        1. Getting ready
        2. How to do it...
        3. How it works...
      10. Determining disk space consumed by indexes
        1. Getting ready
        2. How to do it...
          1. How it works...
    20. 11. Points to Consider While Writing Queries
      1. Introduction
      2. Improving performance by limiting the number of columns and rows
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. See also
      3. Improving performance by using sargable conditions
        1. Getting ready
        2. How to do it...
          1. How it works...
      4. Using arithmetic operator wisely in predicate to improve performance
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Improving query performance by not using functions on predicate columns
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Improving performance by Declarative Referential Integrity (DRI)
        1. Getting ready
        2. How to do it...
        3. How it works...
      7. "Trust" your foreign key to gain performance
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    21. 12. Statistics in SQL Server
      1. Introduction
      2. Creating and updating statistics
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
      3. Effects of statistics on non-key column
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Find out-of-date statistics and get it correct
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      5. Effect of statistics on a filtered index
        1. Getting ready
        2. How to do it...
        3. How it works...
    22. 13. Table and Index Partitioning
      1. Introduction
      2. Partitioning a table with RANGE LEFT
        1. Getting ready
        2. How to do it...
          1. How it works...
      3. Partitioning a table with RANGE RIGHT
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    23. 14. Implementing Physical Database Structure
      1. Introduction
      2. Configuring data file and log file on multiple physical disks
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Using files and filegroups
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Moving the existing large table to separate physical disk
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Moving non-clustered indexes on separate physical disk
        1. Getting ready
        2. How to do it...
        3. How it works...
      6. Configuring the tempdb database on separate physical disk
        1. Getting ready
        2. How to do it...
        3. How it works...
    24. 15. Advanced Query Tuning Hints and Plan Guides
      1. Introduction
      2. Using NOLOCK table query hint
        1. Getting ready
        2. How to do it...
        3. How it works...
      3. Using FORCESEEK and INDEX table hint
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Optimizing a query using an object plan guide
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Implementing a fixed execution plan using SQL plan guide
        1. Getting ready
        2. How to do it...
        3. How it works...
    25. 16. Dealing with Locking, Blocking, and Deadlocking
      1. Introduction
      2. Determining long-running transactions
        1. Getting ready
        2. How to do it...
          1. How it works...
      3. Detecting blocked and blocking queries
        1. Getting ready
        2. How to do it...
        3. How it works...
      4. Detecting deadlocks with SQL Server Profiler
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Detecting deadlocks with Trace Flag 1204
        1. Getting ready
        2. How to do it...
        3. How it works...
    26. 17. Configuring SQL Server for Optimization
      1. Introduction
      2. Configuring SQL Server to use more processing power
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Configuring memory in 32 bit versus. 64 bit
        1. Getting ready
        2. How to do it...
          1. How it works...
      4. Configuring "Optimize for Ad hoc Workloads"
        1. Getting ready
        2. How to do it...
        3. How it works...
      5. Optimizing SQL Server instance configuration
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    27. 18. Policy-based Management
      1. Introduction
      2. Evaluating database properties
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Restricting database objects
        1. Getting ready
        2. How to do it...
          1. How it works...
          2. There's more...
    28. 19. Resource Management with Resource Governor
      1. Introduction
      2. Configuring Resource Governor with SQL Server Management Studio
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      3. Configuring Resource Governor with T-SQL script
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
      4. Monitoring Resource Governor
        1. Getting ready
        2. How to do it...
        3. How it works...
        4. There's more...
    29. Index

Product information

  • Title: Microsoft SQL Server 2012 Performance Tuning Cookbook
  • Author(s): Ritesh Shah, Bihag Thaker
  • Release date: July 2012
  • Publisher(s): Packt Publishing
  • ISBN: 9781849685740