Franklin

Microsoft SQL Server 2012 Performance Tuning Cookbook.

Author/Creator:
Shah, Ritesh.
Publication:
Olton : Packt Publishing, Limited, 2012.
Format/Description:
Book
1 online resource (523 pages)
Edition:
1st ed.
Status/Location:
Loading...

Options
Location Notes Your Loan Policy

Details

Subjects:
Hematopoietic stem cells -- Transplantation.
Lymphomas -- Treatment.
Multiple myeloma -- Treatment.
Leukemia -- Treatment.
Form/Genre:
Electronic books.
Summary:
Over 80 recipes to help you tune SQL Server 2012 and achieve optimal performance.
Contents:
Intro
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.
Notes:
Description based on publisher supplied metadata and other sources.
Local notes:
Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2021. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.
Contributor:
Thaker, Bihag.
Other format:
Print version: Shah, Ritesh Microsoft SQL Server 2012 Performance Tuning Cookbook
ISBN:
9781849685757
9781849685740
OCLC:
808340926