This book captures a wealth of experience that can be used along with code snippets to immediately improve the performance of your databases. SQL Server is finding its way into an increasing number of businesses. Although most servers are conspicuous, some appear almost hidden, for example, SharePoint servers and Customer Relationship Management (CRM) servers. In addition, increasing amounts of data are getting stored within SQL Server. Both of these trends have a bearing on the performance of your SQL Server databases and queries. You can use the advice and code snippets given in this book to fight back and reclaim your high-performing SQL Server.
If you want to improve the performance of your SQL Server databases and the queries that run on them, you should buy this book.
Anyone who wants to ensure their SQL Server databases are running as efficiently as possible will find this book very useful. The following groups of people in particular will find this book valuable: database administrators (DBAs), developers working with SQL Server, and administrators of SharePoint servers, CRM systems, and similar servers.
When a new version of a software product appears, for example, Microsoft Word or SQL Server, new features are typically added to the existing core. Microsoft Word is still primarily used to enter and store text; this core functionality hasn’t changed, despite the numerous version releases. Similarly, although this book is written primarily for SQL Server 2005 and 2008, the core functionality of the DMVs is unlikely to change in future versions (for example, SQL Server 2011), and so it should be applicable to future versions too.
DBAs need to ensure the databases under their command are running as efficiently as possible. Running the code snippets provided in this book will identify any problem areas and help provide solutions to these problems.
Developers need to ensure their applications can retrieve and store data efficiently. Using the supplied code snippets, developers will be able to ensure appropriate indexes are being used, the data is being retrieved efficiently, and any changes are tested for defined improvement.
Increasingly, SharePoint servers, CRM servers, and similar servers that have SQL Server as their underlying database are being installed in organizations with little thought for ongoing maintenance. With time, the performance of these unattended servers can degrade. Applying the code snippets included in this book will identify areas where performance can be improved.
One final point: Often organizations install third-party applications on their SQL Servers. Although it’s usually not possible to change the code in these applications, it is possible to run the code snippets in this book against these databases, with a view to either applying any missing indexes (if this is allowed) or providing feedback to the third party to make the required changes.
This book contains 100-plus code snippets to help you investigate your SQL Server databases. In addition to identifying the problem areas, potential solutions are discussed.
The book is divided into two sections. The first section provides an overview of what DMVs are and how they can identify and solve problems easily and quickly. In addition, this section contains details of common patterns that are used throughout the rest of the book. The second section contains scripts and discussions for improving performance relating to indexes, queries, the operating system, the Common Language Runtime (CLR), transactions, space usage, and much more. Using the code snippets and advice given in this section will provide you with a more optimally performing SQL Server.
Chapter 1 provides an overview of the power of DMVs. It shows you what DMVs are and why they’re important. Various examples are given to get you investigating your performance problems in seconds. Structures such as indexes and statistics are discussed in the context of DMVs. Finally, DMVs are discussed in the context of other performance tools.
Chapter 2 discusses common patterns that are used throughout the book. Rather than describing these patterns everywhere, they are discussed once in this chapter and referenced in the rest of the book.
Chapter 3 looks at index-based DMVs. Indexes are a great tool for improving the performance of your SQL queries. However, unused or little-used indexes can have a detrimental effect on performance. The code snippets included in this chapter will help you improve your index usage, resulting in improved SQL queries.
Chapter 4 takes a look at DMVs that relate to your queries. Code snippets are provided to identify your slowest-running queries, queries that are blocked the most, queries that use the most CPU, and queries that use the most I/O. All these snippets allow you to investigate performance problems from differing viewpoints.
Chapter 5 is an extension of chapter 4, discussing further aspects of how to improve the performance of your queries.
Chapter 6 relates to operating system DMVs. It discusses why your queries, as a whole, are not able to run, what resources they’re waiting for, and how these resources can be improved to give faster queries. Windows performance counters are also examined in relation to these collective queries.
Chapter 7 focuses on the Common Language Runtime DMVs. The use of the CLR within SQL Server is illustrated with a CLR class that provides regular expression functionality for use within your own SQL queries.
Chapter 8 opens with a look at transactions, locking, blocking, and deadlocks. A small case study is provided to illustrate the transaction-based DMV code snippets. Ways of reducing both blocking and deadlocking are explored.
Chapter 9 discusses database-related DMVs. The first section discusses the importance of tempdb and shows how to examine its usage when space problems arise. The second section examines various aspects of index usage that can help you diagnose and improve your queries.
Chapter 10 contains code snippets that can be used to automatically improve the performance of your SQL Server databases. Snippets include intelligently updating statistics, recompiling slow routines, and implementing missing indexes.
Chapter 11 has useful snippets that don’t fit into any of the other chapters. The snippets include a generic test harness, estimating the finishing time of jobs, how memory is used by your database, and a simple lightweight DMV trace utility.
All source code in listings or set off from the text is in a fixed-width font like this to separate it from ordinary text. Code annotations accompany many of the listings, highlighting important concepts. In some cases, numbered bullets link to explanations that follow the listing.
The source code for all of the examples in the book is available from the publisher’s website at www.manning.com/SQLServerDMVsinAction.
The purchase of SQL Server DMVs in Action includes free access to a private forum run by Manning Publications where you can make comments about the book, ask technical questions, and receive help from the author and other users. You can access and subscribe to the forum at www.manning.com/SQLServerDMVsinAction. This page provides information on how to get on the forum once you’re registered, what kind of help is available, and the rules of conduct in the forum.
Manning’s commitment to our readers is to provide a venue where a meaningful dialogue between individual readers and between readers and the author can take place. It isn’t a commitment to any specific amount of participation on the part of the author, whose contributions to the book’s forum remain voluntary (and unpaid). We suggest you try asking the author some challenging questions, lest his interest stray!
The Author Online forum and the archives of previous discussions will be accessible from the publisher’s website as long as the book is in print.
I love to investigate and discover new things, play around with ideas, and just spend time in thought. The mind can be a wonderful playground. I’m lucky enough that my inquisitive nature has found a natural home among the problems in the software industry. As Churchill commented, “If you find a job you really love, you’ll never work again.” With this in mind, the boundary between work and play often dissolves.
As an example of my curiosity, I remember as a child examining a droplet of water on my fingertip and noticing that the droplet magnified the detail of my fingerprints. It made me wonder if an earlier civilization (such as the Romans, who had used glass) had also noticed this, and if they did, why they didn’t develop experiments that would have led to the earlier introduction of the study of optics and the advancement of science and civilization.
I’ve worked in the software industry since 1987, using a variety of platforms and programming languages. I’ve worked in a variety of business areas, including banking, insurance, health, telecoms, travel, finance, software, and consultancies. Since 1995 I’ve worked freelance.
My core competencies are primarily Microsoft-based technologies, with an emphasis on software performance, which naturally extends into database performance. I’m also interested in the developing mobile technologies.
In the course of my work I often create software utilities; when possible I author articles on these utilities to share with other developers. I feel it’s important to give something back to the industry that provides me with a living.
On a final note, I’m a freelance consultant, and I’m available to help improve the performance of your SQL Servers. You can contact me for availability and cost at ian_stirk@yahoo.com.
The figure on the cover of SQL Server DMVs in Action is captioned “Habit of Aurengzeeb” and is taken from the four-volume Collection of the Dresses of Different Nations by Thomas Jefferys, published in London between 1757 and 1772. The collection, which includes beautifully hand-colored copperplate engravings of costumes from around the world, has influenced theatrical costume design ever since it was published. Aurengzeb was the name given to the sixth Mughal Emperor of India, whose reign lasted from 1658 until his death in 1707. The name means “ornament of the throne.” He was a warrior and conqueror, greatly expanding the reach of his empire during his lifetime. His exploits were the topic of many poems, legends, and dramas.
The diversity of the drawings in the Collection of the Dresses of Different Nations speaks vividly of the richness of the costumes presented on the London stage over 200 years ago. The costumes, both historical and contemporaneous, offered a glimpse into the dress customs of people living in different times and in different countries, bringing them to life for London theater audiences.
Dress codes have changed in the last century and the diversity by region, so rich in the past, has faded away. It’s now often hard to tell the inhabitant of one continent from another. Perhaps, trying to view it optimistically, we’ve traded a cultural and visual diversity for a more varied personal life. Or a more varied and interesting intellectual and technical life.
We at Manning celebrate the inventiveness, the initiative, and the fun of the computer business with book covers based on the rich diversity of regional and historical costumes brought back to life by pictures from collections such as this one.
