Book Summary of SQL Wait Stats Joes 2 Pros
When you see the word “I” followed by a business or client example, it will be a first person experience account from author Pinal Dave during his vast experience consulting in the field. Some of the experiences of Rick Morelan will be added as concepts. Bear in mind that the study of wait types is a vast subject, and it would be difficult for one person to understand every wait type and its associated statistics. SQL Server 2008 R2 includes nearly 500 wait types, and this number will increase in the next version of SQL Server (codename “Denali”).
Wait types are not new to SQL Server but the techniques to interpret this feature are not widely available. Performance tuning has been a mainstream concern for SQL professionals for many years. There are many tactics which experts use to tune servers, queries, and systems; however, techniques for using wait types, wait queues, and wait statistics in performance tuning are understood by relatively few in our industry.
This book is a humble attempt to make these concepts more accessible to SQL developers. We consider this book a first version of the ongoing research in this area. Of the hundreds of wait types, this book covers the most visible ones. This initial volume doesn’t cover the wait statistics with less significance or ones seldom seen. Again, every wait type potentially has multiple solutions and we have attempted to cover the common solutions. While you read and implement suggestions, we highly recommend that you attempt them first on your development server before implementing them on your production server.
Please send us feedback as you try out various solutions based on the wait types. We will incorporate your experience in a future version of the book, which will include an expanded write up with more real world scenarios and many more wait types.
About the Author
Pinal works as a Technology Evangelist with Microsoft India. He has been a part of the IT industry for more than seven years. During his career he has worked both in India and the US, primarily with SQL Server Technology - right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He received his Master of Science from the University of Southern California and a Bachelors of Engineering from Gujarat University.
Additionally, he holds many Microsoft certificates. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet, and countless user groups.
Pinal writes frequently on his blog http://blog.SQLAuthority.com on various subjects concerning SQL Server technology and Business Intelligence. His passion for the community drives him to share his training and knowledge. Before joining Microsoft, he was awarded the Microsoft Most Valuable Professional (MVP) in SQL Server Technology for three continuous years for his outstanding community service. He was also awarded the Community Impact Award – Individual Contributor for Y2010. When he is not in front of a computer, he is usually travelling to explore hidden treasures in nature with his toddler daughter, Shaivi, and very supportive wife, Nupur.
In 1994 you could find Rick Morelan braving the frigid waters of the Bering Sea as an Alaska commercial fisherman. His computer skills were non-existent at the time, so you might not imagine such beginnings would lead him to SQL Server expertise at Microsoft. However, every computer expert in the world today woke up at some point in his or her life knowing nothing about computers. They say luck is what happens when preparation meets opportunity. In the case of Rick Morelan, people were a big part of his good luck.
Making the change from the fishing industry seemed scary and took daily schooling at Catapult Software Training Institute. Rick got his lucky break in August 1995, working his first database job at Microsoft. Since that time, Rick has worked more than 10 years at Microsoft and has attained over 30 Microsoft technical certifications in applications, networking, databases, and .NET Development.
Table of Contents
About the Authors
Pinal Dave
Rick Morelan
Acknowledgements
Introduction
Skills Needed for this Book
About this Book
Chapter 1. Introduction to Wait Stats
Your Natural Skills
What are Wait Stats
Query Execution Life Cycle
Kept Wait Stats
Summary
Introduction to Wait Stats - Points to Ponder
Acknowledgement
Chapter One - Review Quiz
Answer Key
Chapter 2. Querying Wait Stats
Wait Stats DMVs
Wait Stats DMFs
Persisting Wait Stat Data
Querying Wait Stats - Points to Ponder
Chapter Two - Review Quiz
Answer Key
Chapter 3. Parallel Execution
CXPACKET
Database Considerations for CXPACKET
CXPACKET Potential Problems
Reducing CXPACKET
Best Practices for CXPACKET
Bad Practices for CXPACKET
Parallel Execution - Points to Ponder
Chapter Three - Review Quiz
Answer Key
Chapter 4. Multi-Tasking Waits
SOS_SCHEDULER_YIELD
SOS_SCHEDULER_YIELD Potential Problems
Reducing SOS_SCHEDULER_YIELD Wait
Multi-Tasking Types
PREEMPTIVE Multi-Tasking
Cooperative (Non-PREEMPTIVE) Multi-Tasking
Both Types of Multi-Tasking in SQL
Multi-Tasking Potential Problems
Reducing Multi-Tasking Waits
Multi-Tasking - Points to Ponder
Chapter Four - Review Quiz
Answer Key
Chapter 5. IO Waits
PAGEIOLATCH_XX
PAGEIOLATCH_EX Potential Problems
Reducing PAGEIOLATCH_EX
IO_COMPLETION
Reducing IO_COMPLETION
ASYNC_IO_COMPLETION
ASYNC_IO_COMPLETION Potential Problems
Reducing ASYNC_ IO_COMPLETION
IO Waits - Points to Ponder
Chapter Five - Review Quiz
Answer Key
Chapter 6. Backup and Restore Waits
BACKUPIO and BACKUPBUFFER
Reducing BACKUPIO and BACKUPBUFFER
Backup and Restore Waits - Points to Ponder
Chapter Six - Review Quiz
Answer Key
Chapter 7.Locking Waits
LCK_M_XXX
LCK_M_XXX Potential Problems
Reducing LCK_M_XXX
Locking Waits - Points to Ponder
Chapter Seven - Review Quiz
Answer Key
Chapter 8. Database Log Waits
Data Files and Log Files
WRITELOG Waits
Reducing WRITELOG Waits
Logbuffer Waits
Potential Logbuffer Problems
Reducing Logbuffer
Database Log Waits - Points to Ponder
Chapter Eight - Review Quiz
Answer Key
Chapter 9. Waits on External Resources
MSSQL_XP Waits
Reducing MSSQL_XP Waits
OLEDB Linked Server Waits
Reducing OLEDB Waits
Waits on External Resources - Points to Ponder
Chapter Nine - Review Quiz
Answer Key
Chapter 10. DBCC SQLPERF
DBCC SQLPERF(wait stats)
SQL Server Denali Wait Types
Denali Deprecated Wait Types
New Denali Wait Types
Chapter 11. Best References
Index
Click here to Buy
