Microsoft SQL Server 2012 Performance Tuning Cookbook电子书

Microsoft SQL Server 2012 Performance Tuning Cookbook

Table of Contents

Microsoft SQL Server 2012 Performance Tuning Cookbook

Credits

About the Authors

Acknowledgement

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Mastering SQL Trace Using Profiler

Introduction

Creating a trace or workload

Getting ready

How to do it...

How it works...

There's more...

Some background of SQL Trace

SQL Trace terms and concepts

SQL Trace

SQL Server Profiler

Event

Event class

Event category

Data column

Trace

Trace properties and Trace definition

Filter

Trace file

Trace table

Trace template

Architecture of SQL Trace

Trace and workload

Commonly-used event classes

Commonly-used data columns

Filtering events

Getting ready

How to do it...

How it works...

There's more...

Detecting slow running and expensive queries

Getting ready

How to do it...

How it works...

There's more...

Trace templates

Creating trace with system stored procedures

Getting ready

How to do it...

How it works...

2. Tuning with Database Engine Tuning Advisor

Introduction

Analyzing queries using Database Engine Tuning Advisor

Getting ready

How to do it...

How it works...

Running Database Engine Tuning Advisor for workload

Getting ready

How to do it...

How it works...

There's more...

Executing Database Tuning Advisor from command prompt

Getting ready

How to do it...

How it works...

There's more...

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

Introduction

Monitoring system health using system statistical functions

Getting ready

How to do it...

How it works...

There's more...

Monitoring with system stored procedure

Getting ready

How to do it...

How it works...

There's more...

Monitoring log space usage statistics with DBCC command

Getting ready

How to do it...

How it works...

There's more...

4. Resource Monitor and Performance Monitor

Introduction

Monitoring of server performance

Getting ready

How to do it...

How it works...

There's more...

Monitoring CPU usage

Getting ready

How to do it...

How it works...

Monitoring memory (RAM) usage

Getting ready

How to do it...

How it works...

5. Monitoring with Execution Plans

Introduction

Working with estimated execution plan

Getting ready

How to do it...

How it works...

Working with actual execution plan

Getting ready

How to do it...

How it works...

There's more...

Monitoring performance of a query by SET SHOWPLAN_XML

Getting ready

How to do it...

How it works...

Monitoring performance of a query by SET STATISTICS XML

Getting ready

How to do it...

How it works...

Monitoring performance of a query by SET STATISTICS IO

Getting ready

How to do it...

How it works...

There's more...

Monitoring performance of a query by SET STATISTICS TIME

Getting ready

How to do it...

How it works...

There's more...

Including and understanding client statistics

Getting ready

How to do it...

How it works...

There's more...

6. Tuning with Execution Plans

Introduction

Understanding Hash, Merge, and Nested Loop Join strategies

Getting ready

How to do it...

How it works...

There's more...

Finding table/index scans in execution plan and fixing them

Getting ready

How to do it...

How it works...

There's more...

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

Getting ready

How to do it...

How it works...

There's more...

7. Dynamic Management Views and Dynamic Management Functions

Introduction

Monitoring current query execution statistics

Getting ready

How to do it......

How it works...

There's more...

sys.dm_exec_connections (DMV)

sys.dm_exec_sessions (DMV)

sys.dm_exec_requests (DMV)

sys.dm_exec_sql_text (DMF)

sys.dm_exec_query_plan (DMF)

sys.dm_exec_cursors (DMF)

Monitoring index performance

Getting ready

How to do it...

How it works...

There's more...

sys.dm_db_missing_index_details (DMV)

sys.dm_db_missing_index_groups (DMV)

sys.dm_db_missing_index_group_stats (DMV)

sys.dm_db_index_usage_stats (DMV)

sys.dm_db_index_physical_stats (DMF)

Monitoring performance of TempDB database

Getting ready

How to do it...

How it works...

There's more...

sys.dm_db_session_space_usage (DMV)

sys.dm_db_file_space_usage (DMV)

Monitoring disk I/O statistics

Getting ready

How to do it...

How it works...

There's more...

dm_io_virtual_file_stats (DMF)

dm_io_pending_io_requests (DMV)

8. SQL Server Cache and Stored Procedure Recompilations

Introduction

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

Getting ready

How to do it...

How it works...

Monitoring recompilations using SQL Server Profiler

Getting ready

How to do it...

How it works...

There's more...

9. Implementing Indexes

Introduction

Increasing performance by creating a clustered index

Getting ready

How to do it...

How it works...

There's more...

Heap

Table and Index Scan/Seek

Increasing performance by creating a non-clustered index

Getting ready

How to do it...

How it works...

There's more...

Increasing performance by covering index

Getting ready

How to do it...

How it works...

There's more...

Increasing performance by including columns in an index

Getting ready

How to do it...

How it works...

Improving performance by a filtered index

Getting ready

How to do it...

How it works...

There's more...

Improving performance by a columnstore index

Getting ready

How to do it...

How it works...

There's more...

10. Maintaining Indexes

Introduction

Finding fragmentation

Getting ready

How to do it...

How it works...

There's more...

Playing with Fill Factor

Getting ready

How to do it...

How it works...

There's more...

Enhance index efficiency by using the REBUILD index

Getting ready

How to do it...

How it works...

There's more...

Enhance index efficiency by using the REORGANIZE index

Getting ready

How to do it...

How it works...

There's more...

How to find missing indexes

Getting ready

How to do it...

How it works...

There's more...

How to find unused indexes

Getting ready

How to do it...

How it works...

There's more...

Enhancing performance by creating an indexed view

Getting ready

How to do it...

How it works...

There's more...

Enhancing performance with index on Computed Columns

Getting ready

How to do it...

How it works...

Determining disk space consumed by indexes

Getting ready

How to do it...

How it works...

11. Points to Consider While Writing Queries

Introduction

Improving performance by limiting the number of columns and rows

Getting ready

How to do it...

How it works...

See also

Improving performance by using sargable conditions

Getting ready

How to do it...

How it works...

Using arithmetic operator wisely in predicate to improve performance

Getting ready

How to do it...

How it works...

Improving query performance by not using functions on predicate columns

Getting ready

How to do it...

How it works...

Improving performance by Declarative Referential Integrity (DRI)

Getting ready

How to do it...

How it works...

"Trust" your foreign key to gain performance

Getting ready

How to do it...

How it works...

There's more...

12. Statistics in SQL Server

Introduction

Creating and updating statistics

Getting ready

How to do it...

How it works...

There's more...

Effects of statistics on non-key column

Getting ready

How to do it...

How it works...

Find out-of-date statistics and get it correct

Getting ready

How to do it...

How it works...

There's more...

Effect of statistics on a filtered index

Getting ready

How to do it...

How it works...

13. Table and Index Partitioning

Introduction

Partitioning a table with RANGE LEFT

Getting ready

How to do it...

How it works...

Partitioning a table with RANGE RIGHT

Getting ready

How to do it...

How it works...

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

Getting ready

How to do it...

How it works...

There's more...

14. Implementing Physical Database Structure

Introduction

Configuring data file and log file on multiple physical disks

Getting ready

How to do it...

How it works...

Using files and filegroups

Getting ready

How to do it...

How it works...

Moving the existing large table to separate physical disk

Getting ready

How to do it...

How it works...

Moving non-clustered indexes on separate physical disk

Getting ready

How to do it...

How it works...

Configuring the tempdb database on separate physical disk

Getting ready

How to do it...

How it works...

15. Advanced Query Tuning Hints and Plan Guides

Introduction

Using NOLOCK table query hint

Getting ready

How to do it...

How it works...

Using FORCESEEK and INDEX table hint

Getting ready

How to do it...

How it works...

Optimizing a query using an object plan guide

Getting ready

How to do it...

How it works...

Implementing a fixed execution plan using SQL plan guide

Getting ready

How to do it...

How it works...

16. Dealing with Locking, Blocking, and Deadlocking

Introduction

Determining long-running transactions

Getting ready

How to do it...

How it works...

Detecting blocked and blocking queries

Getting ready

How to do it...

How it works...

Detecting deadlocks with SQL Server Profiler

Getting ready

How to do it...

How it works...

Detecting deadlocks with Trace Flag 1204

Getting ready

How to do it...

How it works...

17. Configuring SQL Server for Optimization

Introduction

Configuring SQL Server to use more processing power

Getting ready

How to do it...

How it works...

There's more...

Configuring memory in 32 bit versus. 64 bit

Getting ready

How to do it...

How it works...

Configuring "Optimize for Ad hoc Workloads"

Getting ready

How to do it...

How it works...

Optimizing SQL Server instance configuration

Getting ready

How to do it...

How it works...

There's more...

18. Policy-based Management

Introduction

Evaluating database properties

Getting ready

How to do it...

How it works...

There's more...

Restricting database objects

Getting ready

How to do it...

How it works...

There's more...

19. Resource Management with Resource Governor

Introduction

Configuring Resource Governor with SQL Server Management Studio

Getting ready

How to do it...

How it works...

There's more...

Configuring Resource Governor with T-SQL script

Getting ready

How to do it...

How it works...

There's more...

Monitoring Resource Governor

Getting ready

How to do it...

How it works...

There's more...

Index