47,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
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 & 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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 518
Veröffentlichungsjahr: 2012
Ähnliche
Table of Contents
Microsoft SQL Server 2012 Performance Tuning Cookbook
Microsoft SQL Server 2012 Performance Tuning Cookbook
Copyright © 2012 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: July 2012
Production Reference: 1160712
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK..
ISBN 978-1-84968-574-0
www.packtpub.com
Cover Image by Asher Wishkerman (<[email protected]>)
Credits
Authors
Ritesh Shah
Bihag Thaker
Reviewers
Satya SK Jayanty
Maria Zakourdaev
Michael Zilberstein
Acquisition Editor
Dhwani Devater
Lead Technical Editor
Kedar Bhat
Technical Editors
Apoorva Bolar
Madhuri Das
Merin Jose
Copy Editor
Brandt D'Mello
Project Coordinator
Sai Gamare
Proofreader
Lesley Harrison
Indexer
Monica Ajmera Mehta
Graphics
Manu Joseph
Valentina Dsilva
Production Coordinator
Shantanu Zagade
Cover Work
Shantanu Zagade
About the Authors
Ritesh Shah is a data professional with over 10 years of experience with using Microsoft technology, from SQL Server 2000 to the latest version. He has worked with various technologies, from Visual Basic 6.0 to .NET Framework 4.0. He has deployed many medium-scale as well as large-scale projects, using Microsoft technology.
He shares his knowledge on his blog, SQLHub.com, and also helps the community, using different portals, such as BeyondRelational.com, Experts-Exchange.com, and Asp.Net forum.
Acknowledgement
It is really truer than ever that this is not an individual effort. The Packt team worked with me the whole time, so a really big thanks goes to them, especially Sai, Kedar, Apoorva, Madhuri, and many more. I cannot forget to mention Dhwani from the Packt team, as she is the one who presented the idea of this book to me. Seriously, I wouldn't have been able to author this book alone, so thanks should go to Mr. Bihag Thaker, as well, as he agreed to co-author this book with me and has worked even harder on it than I have myself.
I am really honored to have Satya, Michael, and Maria as the technical reviewers for this book. They are all well-known personalities in the world of SQL Server.
Apart from the team that worked on this book, I would also like to thank, on a personal note, two well-known personalities in the SQL Server community, who always inspire me to do more. In fact, they were the ones who diverted my interest from .NET technology to SQL Server. They are:
Most important of all, my deepest gratitude goes to my parents, Mr. Ashwin Shah and Mrs. Divya Shah. It is because of their hard work, inspiration, and motivation that a small-town boy like me, who has grown up with very limited resources, has progressed so much in life, which in itself proves where there's a will there's a way. I would also like to thank my one-and-a-half-year-old son, Teerth, who used to often start crying at midnight, because of which I would lose my sleep and, not being able to get it back, started researching more on the subjects that helped me write this book. Finally, I would like to thank my wife, Alka Shah.
Bihag Thaker is a SQL Server enthusiast, an MCTS (SQL Server 2005), and an MCITP (SQL Server 2008), who has been working with SQL Server technology for the past few years. Initially he was into .Net technology, but his keen interest for SQL Server led him to be a database specialist.
He is currently working as a database administrator. He has worked on numerous performance tuning assignments and executed large-scale database migrations. He likes to share his knowledge and enjoys helping the SQL Server community. You will find him talking about SQL Server on his blog MsSQLBlog.com.
Acknowledgement
I had never thought that the dream of writing my first book on SQL Server would come true so early, and I must give full credit for this to Mr. Ritesh Shah and Packt Publishing.
I would sincerely like to thank Packt Publishing, for showing their confidence in me and providing the invaluable opportunity of being a part of this book. Individuals at Packt whom I am deeply grateful to, are Kedar Bhat, Sai Gamare, Madhuri Das, Ashwin Shetty, Apoorva Bolar, and Dhwani Devater. They have been very co-operative and supportive at all the stages of this book. I am extremely thankful to Michael Zilberstein and Maria Zakourdaev, the technical reviewers, for their excellent work of getting the accuracy of the technical details of the book in perfect shape.
I find it difficult to express, in words, my gratitude, to Ritesh, who has shared the priceless gift of writing this book with me. This was not at all attainable without his continuous support. Apart from being a TechMate, Ritesh is an all-time great friend of mine, who is always willing to help the SQL Server community.
Two individuals to whom I am indebted and whose disciple I have always been, are Mr. Paresh Vora and Mr. Mukesh Devmurari. I have learnt a lot from them, and they are the reason I'm part of the IT community today.
Without my family support, a task such as writing a book would not have been achievable. I would like to heartily thank my parents, Mr. Kanaiyalal Thaker and Mrs. Hema Thaker. It is because of them that I exist, and I cherish their blessings, which are always with me. I am very thankful to my wife, Khyati, who has always stood by me, helped me at all times, and has even smilingly got me cups of coffee during my sleepless nights of writing!
Last but not the least, I would like to thank my friends who helped me directly or indirectly by giving me moral support.
About the Reviewers
Satya SK Jayanty is a SQL Server MVP and Subject Matter Expert with consulting and technical expertise for D Bi A Solutions INc. Limited, with over 20 years of experience. His work experience includes a wide range of industries, including the stock exchange, insurance, tele-communications, financial, retail, and manufacturing sectors, among others.
He is a regular speaker and SME volunteer at major technology conferences such as Microsoft Tech-Ed (Europe, India, and North America), and SQL PASS (Europe and North America), SQL Bits - UK, and manages the Scottish Area SQL Server user group based in Scotland. He is also a moderator in a majority of web-based SQL Server forums (Microsoft Technet and www.sql-server-performance.com), writer, and contributing editor, and blogs at www.sqlserver-qa.net, www.sql-server-performance.comand, www.beyondrelational.com websites.
He is the author of Microsoft SQL Server 2008 R2 Administration Cookbook, Packt Publishing, and co-author of SQL Server MVP Deep Dives, Volume 2, Manning Publications.
Maria Zakourdaev has more than 10 years of experience with SQL Server. She is currently working with one of the most successful Israeli startup companies, called Conduit. She has extensive knowledge of Microsoft replication solutions, table partitioning, and advanced, query tuning techniques. Prior to Conduit she had worked with different companies, benchmarking different SQL Server features and flows, such as partitioning, data import, index impact on DML flows, star transformations in RDBMS, hierarchic queries, and custom OLAP-like aggregations. She was a speaker in Microsoft Teched (Israel) on the SQL Server track and is an active member of the Israel SQL Server Group.
Michael Zilberstein has more than 10 years of experience in the IT industry and database world, working with all the SQL Server versions from 6.5 to 2012 and with different Oracle versions as well. After working with several start-up companies during the first few years of his career, in 2007 Michael founded DBArt Ltd SQL Server, a consulting services company.
Two of Michael's most distinctive interests (besides rappelling, homebrewing, playing chess, and reading history books) are performance tuning and architecture of large-scale systems. The biggest professional satisfaction for him is to take a young start-up company and build its product from schemas in scrapbook and Visio to a working and scalable terabyte-size system.
Michael is a frequent speaker at Israeli SQL Server Usergroup (ISUG) and other SQL Server events in Israel. He also writes a blog—http://sqlblog.com/blogs/michael_zilberstein/default.aspx.
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at <[email protected]> for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
Preface
Microsoft SQL Server 2012 Performance Tuning Cookbook is divided into three major parts—Performance Monitoring, Performance Tuning, and Performance Management—that are mandatory for dealing with performance in any capacity.
Microsoft 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 precautions 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 functions, SPs with DBCC commands, Resource Monitor, Reliability and Performance Monitor, and execution plans.
The second part of the book offers execution plan, dynamic management views and dynamic management functions, SQL Server Cache, stored procedure recompilations, indexes, important ways to write effective T-SQL, 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 about performance management with the help of policy based management and management with Resource Governor.
What this book covers
Chapter 1, SQL Server Profiler, teaches you how to create and start your first SQL Trace, limit the trace data and capture only the events which are of interest, detect slow running and expensive queries, and create a trace with system stored procedures.
Chapter 2, Tuning with Database Engine Tuning Advisor, covers how to analyze queries using Database Engine Tuning Advisor, how to run Database Engine Tuning Advisor for Workload, and how to execute Database Tuning Advisor from the command prompt.
Chapter 3, System Statistical Functions, System Stored Procedures, and DBCC SQLPERF Command, starts with the monitoring of system health using system statistical functions and later on covers the monitoring of SQL Server processes and sessions with system stored procedures, and log space usage statistics with the DBCC SQLPERF command.
Chapter 4, Resource Monitor and Performance Monitor, teaches you how to do quick monitoring of server performance, followed by monitoring of CPU and memory (RAM) usage.
Chapter 5, Monitoring with Execution Plans, includes recipes for working with Estimated Execution Plan and Actual Execution Plan, monitoring the performance of queries by SET SHOWPLAN_XML, SET STATISTICS XML, and SET STATISTICS IO, finding the execution time of a query by SET STATISTICS TIME, and including and understanding Client Statistics.
Chapter 6, Tuning with Execution Plans, explains the Hash, Merge, and Nested Loop Join strategies, teaches how to find table/index scans in execution plans and how to fix them, introduces Key Lookups, and explains how to find them in execution plans and resolve them.
Chapter 7, Dynamic Management Views and Dynamic Management Functions, includes recipes to monitor current query execution statistics, manage and monitor index performance, monitor the TempDB database's performance with database-related dynamic management views, and monitor disk I/O statistics.
Chapter 8, SQL Server Cache and Stored Procedure Recompilations, covers monitoring of compilations and recompilations at instance level, using Reliability and Performance Monitor, and monitoring of recompilations using SQL Server Profiler.
Chapter 9, Implementing Indexes, explains how to improve performance by creating a clustered index, by creating a non-clustered index, by covering index, by including columns in an index, by a filtered index, and by a columnstore index.
Chapter 10, Maintaining Indexes, includes recipes to find fragmentation, to enhance index efficiency by using the REBUILD and REORGANIZE index, to find missing and unused indexes, to enhance performance by creating indexed views and creating an index on Computed Columns, and to determine disk space consumed by indexes.
Chapter 11, Points to Consider While Writing Query, covers how to improve performance by limiting the number of columns and rows and by using sargable conditions, how to use arithmetic operators wisely in predicate to improve performance, how to improve query performance by not using functions on predicate columns, how to improve performance by Declarative Referential Integrity (DRI), and how to gain performance by trusting your foreign key.
Chapter 12, Statistics in SQL Server, explains how to create and update statistics, effects of statistics on non-key columns, how to find out-of-date statistics and correct them, and effects of statistics on a filtered index.
Chapter 13, Table and Index Partitioning, covers partitioning of table with RANGE LEFT and RANGE RIGHT, and deleting and loading of bulk data by splitting, merging, and switching partitions (sliding window).
Chapter 14, Implementing Physical Database Structure, includes recipes for configuring a data file and log file on multiple physical disks, using files and filegroups, moving an existing large table to a separate physical disk, moving non-clustered indexes to a separate physical disk, and configuring the TempDB database on a separate physical disk.
Chapter 15, Advanced Query Tuning: Hints and Plan Guides, includes recipes for using the NOLOCK table query hint, using the FORCESEEK and INDEX table hints, optimizing a query using an object plan guide, and implementing a fixed execution plan using a SQL plan guide.
Chapter 16, Dealing with Locking, Blocking, and Deadlocking, covers determining long-running transactions, detecting blocked and blocking queries, detecting deadlocks with SQL Server Profiler, and detecting deadlocks with Trace Flag 1204.
Chapter 17, Configuring SQL Server for Optimization, includes recipes for configuring SQL Server to use more processing power, configuring memory in 32-bit versus 64-bit, configuring "Optimize for Ad hoc Workloads", and optimizing SQL Server instance configuration.
Chapter 18, Policy Based Management, explains how to evaluate database properties and restrict database objects.
Chapter 19, Management with Resource Governor, includes recipes for configuring Resource Governor with SQL Server Management Studio and T-SQL script, and monitoring Resource Governor.
What you need for this book
To work with the examples given in the book, you must have the following infrastructure:
Who this book is for
Microsoft SQL Server 2012 Performance Tuning Cookbook is aimed at SQL Server Database Developers, DBAs, and Database Architects who are working in any capacity to achieve optimal performance. Basic knowledge of SQL Server is expected, and professionals who want to get hands-on with performance tuning and have not worked on tuning the SQL Server for performance will find this book helpful.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text are shown as follows: "You may notice some TextData appearing multiple times in a trace for a single execution of a T-SQL statement."
A block of code is set as follows:
Any command-line input or output is written as follows:
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Connect object explorer with server and move to Management | Policy Management | Policies".
Note
Warnings or important notes appear in a box like this.
Tip
Tips and tricks appear like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to <[email protected]>, and mention the book title through the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website, or added to any list of existing errata, under the Errata section of that title.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at <[email protected]> if you are having a problem with any aspect of the book, and we will do our best to address it.
Chapter 1. Mastering SQL Trace Using Profiler
In this chapter we will cover:
Introduction
Welcome to the world of Performance Monitoring and Tuning with SQL Server 2012!
Let's assume that you are a database administrator in your organization. What, if one day one of your colleagues from your IT department calls you right away and complains that the production database server has abruptly started to run very slowly and applications that are accessing the production database are not responding the way they should? The issue needs immediate attention and for that you are required to investigate the issue and fix it in timely manner. What will be your approach to look at the problem and solve it? How would you be able to analyze the situation and identify where the problem is? What actions would you take once a particular problem is recognized in order to resolve it?
Installing and upgrading database servers, managing and maintaining database servers, managing database security, implementing disaster recovery plan, capacity planning, managing high-availability of databases, and performance tuning of databases and SQL server are some of the responsibilities of a DBA. Amongst these responsibilities, performance tuning of the database server is one of the prime responsibilities of DBA. The most common reason is, companies offering IT services are often engaged in signing Service Level Agreements (SLAs) and as per their SLAs they are committed to provide a certain level of services and up-time. Any additional down-time than what is allowed as per SLAs can cause them money loss or business loss. Even companies not engaged in SLAs might lose business because of their poor software systems caused by poor database systems. This is one of the reasons why skilled DBAs are required to keep the database performance up-to date by monitoring and tuning database performance.
In database centric application environment, it is very common for any DBA to face such database related performance issues at different levels. By means of different levels, it implies that performance problem can be found at query level, database level, server level or application level .There can be a number of reasons for a database centric application to be performing poorly. The troubleshooting skills and expertise in performance tuning of a DBA are tested out in recognizing such factors behind the performance degradation and taking the necessary corrective steps.
The first step towards performance tuning is monitoring. In data platform, monitoring something is the process of analyzing and identifying something. So, until you monitor something, you can't know for sure what and where the problem is. Until you know what and where the problem is, you can't analyze the problem. And until you can analyze the problem, you can't solve a problem! This also means that unless you understand performance monitoring, you cannot master performance tuning in a true sense. Thus, performance tuning always comes after performance monitoring. This is the reason why we have a few opening chapters that specifically concentrates on performance monitoring.
The troublesome situation that was just described earlier needs thorough monitoring and systematic analysis in order to identify the root problem accurately before a problem can be solved.
SQL Server Profiler is the most common but powerful tool for monitoring and auditing an instance of SQL server. By using this tool, a DBA is able to solve a large number of different types of database performance issues whether it is a query issue, index issue, locking issue or database, or server configuration issue. It is the tool that essentially any DBA must know. So, SQL Server Profiler will be the subject of this first chapter.
Filtering events
Running a trace which is configured to collect large number of events is not best practice. While collecting trace data, SQL Trace itself can introduce overhead and affect the performance of SQL Server if trace is configured to collect too much trace information. This also depends on whether the trace is server-side trace or client-side trace. If the trace is client-side using profiler, then the performance overhead can be greater.
Also, if large number of trace data is captured, the size of the trace file immediately grows very big and it becomes a difficult job for us to look for the right data in the trace. Therefore, any unnecessary or irrelevant trace data should not be collected.
This is the reason why we should consider limiting the resulting trace data and capturing only the events which are of our interest. For this, we should identify what trace data we need to look at and based upon that we should identify the filters that are applied to our trace.
Note
Collecting large amount of trace data can affect the performance of SQL Server. So, before creating a trace, we should identify the type of analysis we want to perform on trace information. A single trace should not be created for multiple types of analysis. For each analysis type, a separate trace should be created until and unless different types of analysis explicitly need to be combined into single trace for performing correlative analysis. For example, rather than creating a single trace that collects both scan events and lock events for index scan analysis and object locking analysis respectively, we should consider creating two separate traces; one for collecting only scan events and another for collecting lock events only.
Getting ready
In this recipe, we will see how to capture only those trace events that occurred for a specific database and from a specific SQL Server login.
Let's assume that sample database AdventureWorks2012 is our production database on our production server, which is hosting other databases also. One of the database users James complains that he faces some problems while running queries against database AdventureWorks2012. So, we want to trace his session only for database AdventureWorks2012. Because there are also other databases hosted on the same production server and many users are accessing AdventureWorks2012 database, we need to filter trace events based on session login name and database name in order to avoid any unwanted trace data from being collected.
To emulate this case practically, we need the following as prerequisites:
How to do it...
We will be performing three main actions in this example. These are as follows:
Because two SQL Server logins named James and Peter with permissions on AdventureWorks2012 database are required, create them by performing the following steps:
Note
Notice the new command syntax in this script introduced in SQL Server 2012 for adding members to a role.
Now, we will create a trace and capture only events that occur for AdventureWorks2012 database from James' session only. To do this, follow these steps:
Now, we will open two instances of SQL Server Management Studio one by one that connect to SQL Server with the logins James and Peter respectively and run a few queries.
