PacktLib: Microsoft SQL Server 2012 Performance Tuning Cookbook

Microsoft SQL Server 2012 Performance Tuning Cookbook

Credits

About the Authors

Acknowledgement

Acknowledgement

About the Reviewers

www.PacktPub.com

Preface

Mastering SQL Trace Using Profiler

Introduction

Creating a trace or workload

Filtering events

Detecting slow running and expensive queries

Creating trace with system stored procedures

Tuning with Database Engine Tuning Advisor

Introduction

Analyzing queries using Database Engine Tuning Advisor

Running Database Engine Tuning Advisor for workload

Executing Database Tuning Advisor from command prompt

System Statistical Functions, Stored Procedures, and the DBCC SQLPERF Command

Introduction

Monitoring system health using system statistical functions

Monitoring with system stored procedure

Monitoring log space usage statistics with DBCC command

Resource Monitor and Performance Monitor

Introduction

Monitoring of server performance

Monitoring CPU usage

Monitoring memory (RAM) usage

Monitoring with Execution Plans

Introduction

Working with estimated execution plan

Working with actual execution plan

Monitoring performance of a query by SET SHOWPLAN_XML

Monitoring performance of a query by SET STATISTICS XML

Monitoring performance of a query by SET STATISTICS IO

Monitoring performance of a query by SET STATISTICS TIME

Including and understanding client statistics

Tuning with Execution Plans

Introduction

Understanding Hash, Merge, and Nested Loop Join strategies

Finding table/index scans in execution plan and fixing them

Introducing Key Lookups, finding them in execution plans, and resolving them

Dynamic Management Views and Dynamic Management Functions

Introduction

Monitoring current query execution statistics

Monitoring index performance

Monitoring performance of TempDB database

Monitoring disk I/O statistics

SQL Server Cache and Stored Procedure Recompilations

Introduction

Monitoring compilations and recompilations at instance level using Reliability and Performance Monitor

Monitoring recompilations using SQL Server Profiler

Implementing Indexes

Introduction

Increasing performance by creating a clustered index

Increasing performance by creating a non-clustered index

Increasing performance by covering index

Increasing performance by including columns in an index

Improving performance by a filtered index

Improving performance by a columnstore index

Maintaining Indexes

Introduction

Finding fragmentation

Playing with Fill Factor

Enhance index efficiency by using the REBUILD index

Enhance index efficiency by using the REORGANIZE index

How to find missing indexes

How to find unused indexes

Enhancing performance by creating an indexed view

Enhancing performance with index on Computed Columns

Determining disk space consumed by indexes

Points to Consider While Writing Queries

Introduction

Improving performance by limiting the number of columns and rows

Improving performance by using sargable conditions

Using arithmetic operator wisely in predicate to improve performance

Improving query performance by not using functions on predicate columns

Improving performance by Declarative Referential Integrity (DRI)

"Trust" your foreign key to gain performance

Statistics in SQL Server

Introduction

Creating and updating statistics

Effects of statistics on non-key column

Find out-of-date statistics and get it correct

Effect of statistics on a filtered index

Table and Index Partitioning

Introduction

Partitioning a table with RANGE LEFT

Partitioning a table with RANGE RIGHT

Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)

Implementing Physical Database Structure

Introduction

Configuring data file and log file on multiple physical disks

Using files and filegroups

Moving the existing large table to separate physical disk

Moving non-clustered indexes on separate physical disk

Configuring the tempdb database on separate physical disk

Advanced Query Tuning Hints and Plan Guides

Introduction

Using NOLOCK table query hint

Using FORCESEEK and INDEX table hint

Optimizing a query using an object plan guide

Implementing a fixed execution plan using SQL plan guide

Dealing with Locking, Blocking, and Deadlocking

Introduction

Determining long-running transactions

Detecting blocked and blocking queries

Detecting deadlocks with SQL Server Profiler

Detecting deadlocks with Trace Flag 1204

Configuring SQL Server for Optimization

Introduction

Configuring SQL Server to use more processing power

Configuring memory in 32 bit versus. 64 bit

Configuring "Optimize for Ad hoc Workloads"

Optimizing SQL Server instance configuration

Policy-based Management

Introduction

Evaluating database properties

Restricting database objects

Resource Management with Resource Governor

Introduction

Configuring Resource Governor with SQL Server Management Studio

Configuring Resource Governor with T-SQL script

Monitoring Resource Governor