SQL performance tuning is probably one of those things you can do to really make a HUGE difference in performance. Let’s put this in perspective: take a typical application, if you can improve the performance by 100% then you really made a huge improvement. You can improve a SQL query by 1000% with 2 lines of code (sometimes all you have to do is take away a % sign). If you can make a query sargable so that the optimizer can do an index seek instead of an index scan your query might go from 12 seconds to 200 milliseconds. Now try doing that in an application, even if you change all the string concatenation to use a stringbuilder instead of creating new strings all the time you will not get such a drastic performance improvement. I am sure you get the point by now, let’s talk about the book.
Inside Microsoft SQL Server 2005: Query Tuning and Optimization is part 4 of the Inside Microsoft SQL Server 2005 series, it is written by Kalen Delaney and five other authors. There are 6 chapters in this book
1 A Performance Troubleshooting Methodology
This chapter explains some typical things that affect performance and also gives a troubleshooting overview
2 Tracing and Profiling
This chapter explains how to use the profiler and how to analyze traces. SQL Server’s built-in traces are also covered
3 Query Execution
This chapter gives a query processing and execution overview. It explains how to read plans and goes into a lot of detail about analyzing plans
4 Troubleshooting Query Performance
This chapter explains how to detect problems in plans, how to improve queries and some best practices
5 Plan Caching and Recompilation
This chapter goes into detail about plan caching and recompilation and how to troubleshoot plan cache issues
6 Concurrency Problems
The final chapter deals with concurrency (locking, blocking and deadlocking)
This is an excellent book for an intermediate/advanced developer. There is so much new stuff in SQL Server 2005 compared to 2000 to help you with tuning queries that you probably want to read each chapter several times. The Dynamic Management Views are a big help and this book shows you how to use them. Some other cool stuff in this book is the discussion of internal tables, undocumented DBCC commands and undocumented trace flags to discover information which could help you determine much faster what the cause of a performance problem might be.
Some pages are packed with so much information that you need to pause for a second and process all that info (I have read some pages two to three times in a row). You will also find out that there are more joins besides left, full and outer. Page 137 for example has a nice table with the three Physical Join Operators: Nested Loop Join, Hash Join and Merge Join. This table lists the characteristics for each of these joins.
If you are an intermediate to advanced developer then I highly recommend this book. I have interviewed Kalen a while back about this book and you can find that interview here
http://sqlservercode.blogspot.com/2007/10/interview-with-kalen-delaney-about.html
Showing posts with label Review. Show all posts
Showing posts with label Review. Show all posts
Thursday, February 07, 2008
Thursday, June 14, 2007
Book Review: Expert SQL Server 2005 Development By Adam Machanic
If you are an advanced or intermediate SQL Server developer then this is the book for you. Adam understands real world scenarios and understands that databases are part of a bigger group in the business world. The database is usually the most important asset in an organization. All your data is in the database, you need to secure it, this is where encryption, privilege and authorization comes in. The ratio of web servers to database servers is usually many to one, it is easy to scale out with web servers however with database servers this is not so easy. This is a reason why your code needs to be optimized and designed for application concurrency. I recommend this book to any intermediate or advanced SQL Server developer. This book is not a book that is like the other book you have but 2 chapters are different. NO, this book contains a lot of good info which is not available in other books. I learned a lot from reading this book and you will too. Here is the breakdown of what is covered in the chapters.
Chapter 1 Software Development Methodologies for the Database World
Adam explains what Cohesion, Coupling and Encapsulation is, where the business logic should live and the balance between maintainability, performance, security and more.
Chapter 2 Testing Database Routines
This chapter is worth the price of the book by itself. You will learn how to unit test your procedures, evaluate performance counters and this chapter introduces the SQLQueryStress Performance Tool (see picture below) which will be used in other chapters. This is a very useful tool if you have to tune a query. How many times do you set statistics time and statistics IO on and off to see the reads and CPU time? This tool does it all for you, paste in your query or proc call, specify how many times you want to run it that is it. This tool will save you many stressful (pun intended) hours
Chapter 3 Errors and Exceptions
This chapter explains the different type of exceptions and how to do error handling. You will also find out what a ‘doomed transaction’ is, this is the one where you get this user friendly message: “The current transaction cannot be commited and cannot support operations that write to the log file. Roll back the transaction.”
Chapter 4 Privilege and Authorization
This chapter explains what impersonation and ownership chaining is. Also covered is how to use EXECUTE AS and how to sign procedures.
Chapter 5 Encryption
This chapter will explain encryption to you in a clear and concise matter. You will learn how to improve performance by using Message Authentication Code. The difference between symmetric and asymmetric key encryption is covered as well as all the terminology that is needed to really understand encryption.
Chapter 6 SQLCLR: Architecture and Design Considerations
What this chapter covers is SQLCLR security, why to use SQLCLR and how to enhance Service Broker Scale-Out with SQLCLR
Chapter 7 Dynamic T-SQL
You want to protect your data? Then this is something you have to read. You will learn how to deal with sql injection, why sp_executesql is much better than exec and the performance implications of parameterization and caching.
Chapter 8 Designing Systems for Application Concurrency
If you are running an OLTP system and you are suffering from blocking/locking then this is the chapter for you. Isolation levels and how they affect concurrency is explained. This chapter uses the SQLQueryStress Performance Tool to show you the difference it makes in performance when you slightly change your proc.
Chapter 9 Working with Spatial Data
Spatial data, this is what a lot of people are storing these dates, unfortunately calculating the distance between 2 points is not as easy as it seems (the earth is not flat you know ;-( ) This chapter covers a couple of ways to represent Geospatial Data.
Chapter 10 Working with Temporal Data
Dates are everywhere in the database but unfortunately a lot of people do not know how dates are stored internally and how to write efficient queries which will cause an index seek instead of a scan. Calendar tables, time zones and intervals are all covered in this chapter
Chapter 11 Trees, Hierarchies, and Graphs
The difference between Nested Set Model, Persisting Materialized Paths and Adjacency list Hierarchies are explained. There is code included that shows you how to traverse up or down the hierarchy, insert new nodes and much more.
Amazon Link: Expert SQL Server 2005 Development
I have also interviewed Adam Machanic a while back, you can find that here: Interview with Adam Machanic Author Of Expert SQL Server 2005 Development
Wednesday, May 23, 2007
Book Review: Pro SQL Server 2005 Database Design and Optimization
Pro SQL Server 2005 Database Design and Optimization
By Louis Davidson, Kevin Kline, Kurt Windisch
ISBN: 1590595297
672 pp.
Published: May 2006
Before I start with the book review here is a little story that took place in 1995. A former colleague wanted to open a bar in the East Village (New York City). He looked and looked and found this place somewhere on 9th street. He looked around the place and liked what he saw. Then he went to the back and saw about seven wooden beams against the wall. When he asked about them the owner said that is to keep the wall from collapsing. Anyway my friend settled on another place and opened his bar in a former parking garage, the name of the place is Liquids and it is located on east 10 Street between First Avenue and Avenue A.
Now why am I telling you this story? This story happens in the database world all the time, think of the data model as the foundation of your house if your foundation is weak you need kludges (wooden beams) to prevent your structure from collapsing. How many times do you see on newsgroups questions relating to split string because someone stored a comma delimited string in a column instead of having a lookup table? You have to start with the proper design, once you have that everything else becomes so much easier. It is easy to change a proc or some code somewhere. Once you have to redo your design then you have to also go through all the code and make changes and then also pray that you didn’t break anything. So this is what this book is all about; to teach you how to properly design a data model for speed, concurrency, security, interoperability, integrity and much more. There is no need for triggers when you can use check constraints which in turn will perform much better.
I got my copy of the book the day after our twins were born, that is a little more than 10 months ago. I have read the book many times but I did not read it in the order that it is written in. However I do recommend reading at least the first 5 chapters in the correct order, this is especially true if you are new to databases.
So what do I think of the book? I think that this book is excellent, it is written in a way that is not dry, and you will actually laugh a couple of times when reading this book. The book is written in such a way that it goes into as much detail as necessary to explain the concept and also provides links where needed for material that is not in the scope of the book. Most chapters end with a best practices and a summary section which is very useful if you want to find out what the chapter is about without reading the whole chapter first.
The book has 11 Chapter and 2 appendixes which are listed below
Chapter 1 Introduction to Database Concepts
Chapter 2 Data Modeling
Chapter 3 Conceptual Data Modeling
Chapter 4 The Normalization Process
Chapter 5 Implementing the Base Table Structures
Chapter 6 Protecting the Integrity of Your Data
Chapter 7 Securing Access to the Data
Chapter 8 Table Structures and Indexing
Chapter 9 Coding for Concurrency
Chapter 10 Code-Level Architectural Decisions
Chapter 11 Database Interoperability
APPENDIX A Codd’s 12 Rules for an RDBMS
APPENDIX B Datatype Reference
INDEX
The name of the chapter indicates what the chapter is about, I won’t go into detail by reviewing every chapter.
If you are completely new to SQL then this is the book for you because a good design is the foundation of everything. Now there are two ways to find this out the first way is the easy way. You purchase the book, read it and use the code. The second way is more painful. You have a weak database foundation, performance is bad, customers complain, your boss is breathing down your neck and the only shows you watch on TV are the late shows because you have to update statistics and reindex your database between 7PM and 10PM every single day to make it perform somewhat responsive. If you are an advanced/intermediate developer don’t think that this book is not for you. It is! I have learned a bunch of new tricks/tips which I would not have if I did not read the book. Obviously I am not the only one who praises the book; just take a look at the reviews on Amazon
If you want check out the book first here is the link to the sample chapter: http://www.apress.com/book/supplementDownload.html?bID=10005&sID=3490
Here is the link to Louis’ blog where he posts updates about the book http://drsql.spaces.live.com
I did an interview with Louis a while back, you can find that here
The nest 2 reviews will be Itzik’s and then Adam’s book. I think that Itzik’s will be next week and Adam’s the week after that.
By Louis Davidson, Kevin Kline, Kurt Windisch
ISBN: 1590595297
672 pp.
Published: May 2006
Before I start with the book review here is a little story that took place in 1995. A former colleague wanted to open a bar in the East Village (New York City). He looked and looked and found this place somewhere on 9th street. He looked around the place and liked what he saw. Then he went to the back and saw about seven wooden beams against the wall. When he asked about them the owner said that is to keep the wall from collapsing. Anyway my friend settled on another place and opened his bar in a former parking garage, the name of the place is Liquids and it is located on east 10 Street between First Avenue and Avenue A.
Now why am I telling you this story? This story happens in the database world all the time, think of the data model as the foundation of your house if your foundation is weak you need kludges (wooden beams) to prevent your structure from collapsing. How many times do you see on newsgroups questions relating to split string because someone stored a comma delimited string in a column instead of having a lookup table? You have to start with the proper design, once you have that everything else becomes so much easier. It is easy to change a proc or some code somewhere. Once you have to redo your design then you have to also go through all the code and make changes and then also pray that you didn’t break anything. So this is what this book is all about; to teach you how to properly design a data model for speed, concurrency, security, interoperability, integrity and much more. There is no need for triggers when you can use check constraints which in turn will perform much better.
I got my copy of the book the day after our twins were born, that is a little more than 10 months ago. I have read the book many times but I did not read it in the order that it is written in. However I do recommend reading at least the first 5 chapters in the correct order, this is especially true if you are new to databases.
So what do I think of the book? I think that this book is excellent, it is written in a way that is not dry, and you will actually laugh a couple of times when reading this book. The book is written in such a way that it goes into as much detail as necessary to explain the concept and also provides links where needed for material that is not in the scope of the book. Most chapters end with a best practices and a summary section which is very useful if you want to find out what the chapter is about without reading the whole chapter first.
The book has 11 Chapter and 2 appendixes which are listed below
Chapter 1 Introduction to Database Concepts
Chapter 2 Data Modeling
Chapter 3 Conceptual Data Modeling
Chapter 4 The Normalization Process
Chapter 5 Implementing the Base Table Structures
Chapter 6 Protecting the Integrity of Your Data
Chapter 7 Securing Access to the Data
Chapter 8 Table Structures and Indexing
Chapter 9 Coding for Concurrency
Chapter 10 Code-Level Architectural Decisions
Chapter 11 Database Interoperability
APPENDIX A Codd’s 12 Rules for an RDBMS
APPENDIX B Datatype Reference
INDEX
The name of the chapter indicates what the chapter is about, I won’t go into detail by reviewing every chapter.
If you are completely new to SQL then this is the book for you because a good design is the foundation of everything. Now there are two ways to find this out the first way is the easy way. You purchase the book, read it and use the code. The second way is more painful. You have a weak database foundation, performance is bad, customers complain, your boss is breathing down your neck and the only shows you watch on TV are the late shows because you have to update statistics and reindex your database between 7PM and 10PM every single day to make it perform somewhat responsive. If you are an advanced/intermediate developer don’t think that this book is not for you. It is! I have learned a bunch of new tricks/tips which I would not have if I did not read the book. Obviously I am not the only one who praises the book; just take a look at the reviews on Amazon
If you want check out the book first here is the link to the sample chapter: http://www.apress.com/book/supplementDownload.html?bID=10005&sID=3490
Here is the link to Louis’ blog where he posts updates about the book http://drsql.spaces.live.com
I did an interview with Louis a while back, you can find that here
The nest 2 reviews will be Itzik’s and then Adam’s book. I think that Itzik’s will be next week and Adam’s the week after that.
Subscribe to:
Posts (Atom)