book

Microsoft SQL Server 2012 Performance Tuning Cookbook

Name: Microsoft SQL Server 2012 Performance Tuning Cookbook
Author: Ritesh Shah
ISBN: 9781849685740

by Ritesh Shah

July 2012

Intermediate to advanced

478 pages

10h 3m

English

Packt Publishing

Read now

Unlock full access

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 moreWhy Subscribe?Free Access for Packt account holdersInstant 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 codeErrataPiracyQuestions
1. Mastering SQL Trace Using Profiler
Introduction
Creating a trace or workload
Getting readyHow to do it...How it works...There's more...Some background of SQL TraceSQL Trace terms and conceptsSQL TraceSQL Server ProfilerEventEvent classEvent categoryData columnTraceTrace properties and Trace definitionFilterTrace fileTrace tableTrace templateArchitecture of SQL TraceTrace and workloadCommonly-used event classesCommonly-used data columns
Filtering events
Getting readyHow to do it...How it works...There's more...
Detecting slow running and expensive queries
Getting readyHow to do it...How it works...There's more...Trace templates
Creating trace with system stored procedures
Getting readyHow to do it...How it works...
2. Tuning with Database Engine Tuning Advisor
Introduction
Analyzing queries using Database Engine Tuning Advisor
Getting readyHow to do it...How it works...
Running Database Engine Tuning Advisor for workload
Getting readyHow to do it...How it works...There's more...
Executing Database Tuning Advisor from command prompt
Getting readyHow 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 readyHow to do it...How it works...There's more...
Monitoring with system stored procedure
Getting readyHow to do it...How it works...There's more...
Monitoring log space usage statistics with DBCC command
Getting readyHow to do it...How it works...There's more...
4. Resource Monitor and Performance Monitor
Introduction
Monitoring of server performance
Getting readyHow to do it...How it works...There's more...
Monitoring CPU usage
Getting readyHow to do it...How it works...
Monitoring memory (RAM) usage
Getting readyHow to do it...How it works...
5. Monitoring with Execution Plans
Introduction
Working with estimated execution plan
Getting readyHow to do it...How it works...
Working with actual execution plan
Getting readyHow to do it...How it works...There's more...
Monitoring performance of a query by SET SHOWPLAN_XML
Getting readyHow to do it...How it works...
Monitoring performance of a query by SET STATISTICS XML
Getting readyHow to do it...How it works...
Monitoring performance of a query by SET STATISTICS IO
Getting readyHow to do it...How it works...There's more...
Monitoring performance of a query by SET STATISTICS TIME
Getting readyHow to do it...How it works...There's more...
Including and understanding client statistics
Getting readyHow to do it...How it works...There's more...
6. Tuning with Execution Plans
Introduction
Understanding Hash, Merge, and Nested Loop Join strategies
Getting readyHow to do it...How it works...There's more...
Finding table/index scans in execution plan and fixing them
Getting readyHow to do it...How it works...There's more...
Introducing Key Lookups, finding them in execution plans, and resolving them
Getting readyHow to do it...How it works...There's more...
7. Dynamic Management Views and Dynamic Management Functions
Introduction
Monitoring current query execution statistics
Getting readyHow 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 readyHow 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 readyHow 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 readyHow 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 readyHow to do it...How it works...
Monitoring recompilations using SQL Server Profiler
Getting readyHow to do it...How it works...There's more...
9. Implementing Indexes
Introduction
Increasing performance by creating a clustered index
Getting readyHow to do it...How it works...There's more...HeapTable and Index Scan/Seek
Increasing performance by creating a non-clustered index
Getting readyHow to do it...How it works...There's more...
Increasing performance by covering index
Getting readyHow to do it...How it works...There's more...
Increasing performance by including columns in an index
Getting readyHow to do it...How it works...
Improving performance by a filtered index
Getting readyHow to do it...How it works...There's more...
Improving performance by a columnstore index
Getting readyHow to do it...How it works...There's more...
10. Maintaining Indexes
Introduction
Finding fragmentation
Getting readyHow to do it...How it works...There's more...
Playing with Fill Factor
Getting readyHow to do it...How it works...There's more...
Enhance index efficiency by using the REBUILD index
Getting readyHow to do it...How it works...There's more...
Enhance index efficiency by using the REORGANIZE index
Getting readyHow to do it...How it works...There's more...
How to find missing indexes
Getting readyHow to do it...How it works...There's more...
How to find unused indexes
Getting readyHow to do it...How it works...There's more...
Enhancing performance by creating an indexed view
Getting readyHow to do it...How it works...There's more...
Enhancing performance with index on Computed Columns
Getting readyHow to do it...How it works...
Determining disk space consumed by indexes
Getting readyHow 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 readyHow to do it...How it works...See also
Improving performance by using sargable conditions
Getting readyHow to do it...How it works...
Using arithmetic operator wisely in predicate to improve performance
Getting readyHow to do it...How it works...
Improving query performance by not using functions on predicate columns
Getting readyHow to do it...How it works...
Improving performance by Declarative Referential Integrity (DRI)
Getting readyHow to do it...How it works...
"Trust" your foreign key to gain performance
Getting readyHow to do it...How it works...There's more...
12. Statistics in SQL Server
Introduction
Creating and updating statistics
Getting readyHow to do it...How it works...There's more...
Effects of statistics on non-key column
Getting readyHow to do it...How it works...
Find out-of-date statistics and get it correct
Getting readyHow to do it...How it works...There's more...
Effect of statistics on a filtered index
Getting readyHow to do it...How it works...
13. Table and Index Partitioning
Introduction
Partitioning a table with RANGE LEFT
Getting readyHow to do it...How it works...
Partitioning a table with RANGE RIGHT
Getting readyHow to do it...How it works...
Deleting and loading bulk data by splitting, merging, and switching partitions (sliding window)
Getting readyHow 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 readyHow to do it...How it works...
Using files and filegroups
Getting readyHow to do it...How it works...
Moving the existing large table to separate physical disk
Getting readyHow to do it...How it works...
Moving non-clustered indexes on separate physical disk
Getting readyHow to do it...How it works...
Configuring the tempdb database on separate physical disk
Getting readyHow to do it...How it works...
15. Advanced Query Tuning Hints and Plan Guides
Introduction
Using NOLOCK table query hint
Getting readyHow to do it...How it works...
Using FORCESEEK and INDEX table hint
Getting readyHow to do it...How it works...
Optimizing a query using an object plan guide
Getting readyHow to do it...How it works...
Implementing a fixed execution plan using SQL plan guide
Getting readyHow to do it...How it works...
16. Dealing with Locking, Blocking, and Deadlocking
Introduction
Determining long-running transactions
Getting readyHow to do it...How it works...
Detecting blocked and blocking queries
Getting readyHow to do it...How it works...
Detecting deadlocks with SQL Server Profiler
Getting readyHow to do it...How it works...
Detecting deadlocks with Trace Flag 1204
Getting readyHow to do it...How it works...
17. Configuring SQL Server for Optimization
Introduction
Configuring SQL Server to use more processing power
Getting readyHow to do it...How it works...There's more...
Configuring memory in 32 bit versus. 64 bit
Getting readyHow to do it...How it works...
Configuring "Optimize for Ad hoc Workloads"
Getting readyHow to do it...How it works...
Optimizing SQL Server instance configuration
Getting readyHow to do it...How it works...There's more...
18. Policy-based Management
Introduction
Evaluating database properties
Getting readyHow to do it...How it works...There's more...
Restricting database objects
Getting readyHow 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 readyHow to do it...How it works...There's more...
Configuring Resource Governor with T-SQL script
Getting readyHow to do it...How it works...There's more...
Monitoring Resource Governor
Getting readyHow to do it...How it works...There's more...
Index

Content preview from Microsoft SQL Server 2012 Performance Tuning Cookbook

Chapter 9. Implementing Indexes

In this chapter we will cover:

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

Introduction

Indexes are one of the most powerful objects in the RDBMS system, though the index itself is not a relational concept. It significantly reduces disk I/O and logical reads, to boost up the performance of the SELECT statement by locating proper data without even scanning the whole table. That is why it is mandatory to have a proper index on proper column(s) of the table. Missing indexes or ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

SQL Server 2012 Query Performance Tuning, Third Edition

Publisher Resources

ISBN: 9781849685740Other

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft SQL Server 2012 Performance Tuning Cookbook

by Ritesh Shah

Chapter 9. Implementing Indexes

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.