Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
Any database design can include only the Normal forms. But while a DBA performs a product deployment, it is critical to take care of certain factors for Index creation. With this noted, explain FILLFACTOR and PATFACTOR inside SQL Server. What are their uses and when would you use each of them?
Notes of Vinod Kumar
Tough FILLFACTOR is used from the side of performance tuning techniques. It also has an impact on storage needs – the IO increases for Reads if done incorrectly. This Hint has the information related to these notes. Is there a skill that you use regarding this matter? Feel free to share.
Notes of Pinal Dave
Fillfactor is an interesting concept. When I tell several DBA and developers that fillfactor 0 is the same as 100, I have seen them not believing what I said. An appropriate fillfactor can have a heavy impact on performance. The fillfactor can be adjusted at server level as well as at index level. A similar concept is PATFACTOR. I have seen it being abused, leading to a terrible performance on server. Aside from fillfactor, there are many additional performance tuning concepts one needs to master if performance is his primary concern for database.
SQL Server Interview Questions and Answers ISBN: 1466405643 Page#151-153
SQL SERVER – What is Fill Factor and What is the Best Value for Fill Factor
Fillfactor, Index and In-depth Look at Effect on Performance
Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
Change Default Fill Factor For Index
Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005
Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Here is a quick video about how to change fill factor:
Reference: Pinal Dave (http://blog.sqlauthority.com)