As a SQL Server enthusiast, I have been diving deep into the world of SQL Server 2005 and wanted to share some interesting concepts and ideas that I have come across during my learning journey. While book reviews may not be the most exciting read, I hope you find these insights valuable.
Inside Microsoft SQL Server 2005: The Storage Engine
Kalen Delaney’s book, “Inside Microsoft SQL Server 2005: The Storage Engine,” is a comprehensive guide that covers various aspects of SQL Server 2005. Although the book is split into four parts, focusing on the storage engine, query optimization, TSQL programming, and TSQL querying, it provides first-class information for both advanced DBAs and those looking to advance their skills.
One interesting discovery I made while reading this book is that in SQL Server 2005, the ability to pin a table in memory using the DBCC Pintable command is no longer supported. While this feature may not have been widely used, it’s intriguing to know that it has been deprecated.
Another fascinating topic covered in the book is page splitting. In certain scenarios, SQL Server may need to split a page more than once when inserting a large row. Understanding these low-level details can help optimize database performance.
Additionally, the book delves into the new isolation options introduced in SQL Server 2005. While I grasp the concepts, I realized the importance of spending more time mastering the details to leverage these options effectively.
Professional SQL Server 2005 Integration Services
Brian Knight’s book, “Professional SQL Server 2005 Integration Services,” provides a comprehensive guide to SQL Server Integration Services (SSIS). This book covers a wide range of subjects, from converting DTS packages to SSIS to building custom tasks for SSIS.
One valuable lesson I learned from this book is the importance of setting the “retain same connection” property of the connection manager to true when working with temporary tables in SSIS. By doing so, you can ensure that the temporary table remains accessible across different steps in the package.
Another frustration I encountered while working with SSIS is the need to escape paths and use double equals for equality comparisons. Additionally, addressing variables by ordinal position in certain scenarios can be counterintuitive.
The book also highlights an interesting point about the move away from staging data in SSIS. With the ability to keep most of the required data in memory, SSIS offers improved performance and reduced management overhead. However, there are still cases where staging data can be beneficial, such as when dealing with slow or remote data retrieval or when better restartability is required.
SQL Server Query Performance Tuning Distilled
Sajal Dam’s book, “SQL Server Query Performance Tuning Distilled,” although focused on SQL Server 2000, offers valuable insights into query tuning and index design. While the book may not extensively cover data partitioning strategies, it provides a solid foundation for optimizing query performance.
One interesting lesson I learned from this book is the impact of using the two-part syntax (owner/schema qualification) when referencing objects. Not using the two-part syntax can result in separate execution plans being created, potentially affecting performance. This is something worth considering, especially when using NT authentication.
The book also highlights the performance benefits of using explicit transactions. In a specific example, performing 256,000 inserts in a loop with an explicit transaction was almost 10 times faster and significantly reduced the log flush rate. While transactions should be used judiciously to avoid concurrency issues, understanding their impact on performance is crucial.
I hope you find these insights as intriguing as I did. Exploring SQL Server concepts and ideas can be a rewarding journey, and these books provide valuable knowledge for both beginners and intermediate users. Stay tuned for more articles on SQL Server and feel free to share your own insights and experiences!