Latest Blog Posts - Ravi's SQL - SQLServerCentral The largest free SQL Server community. en-US Sat, 17 Mar 2012 18:00:09 UT /blogs/ravis-sql/2012/03/17// 0 <h2>Rules of Thumb in Physical Database Design</h2> <p>I am starting my first post in my blog of tuning the physical database design the basic and is very important in production systems and heavily depends on the specific workload and data distributions. The primary goal of the physical database design is <em>data processing efficiency</em>. Over time, a number of <em>rules of thumb </em>emerged providing some form of general guidance and best practices to follow during effective physical design sessions. Virtually every course in database systems or book that specializes in query performance discusses such rules. Below are points, we describe a few of these rules of thumb:</p> <ul> <li><em>Choosing data types for attributes</em>. Choosing the proper storage format of each field (attribute). The DBMS provides some set of data types that should be used for physical storage of fields in the database. Data types (data format) are chosen to reduce storage space and maximise data integrity.</li> <li><em>Index primary keys and most foreign keys</em><em>. </em>Since most joins are between columns in primary and foreign keys, including such columns in indexes can improve performance of complex queries.</li> <li><em>Columns often referenced in WHERE clauses are good candidates for indexes</em>. Columns that are referenced in equality or inequality predicates in WHERE clauses (especially those that are often referenced) will be efficiently fetched by proper indexes.</li> <li><em>Avoid redundant indexes.</em> Indexes defined over the same (or almost the same) columns are rarely beneficial, as they do not provide additional utility over a single index but at the same time use more space and need to be updated.</li> <li><em>Use index columns with caution when they are often updated</em>. When database columns are updated, all indexes defined over such columns need to be maintained. A column that is often updated might transform an otherwise useful index into a performance bottleneck.</li> <li><em>Consider covering indexes for critical queries</em>. Covering index (i.e., those indexes that contain multiple columns required in a query) is a non-clustered index is very good at improving the performance, but at the same time they can be large and useful only for a few queries. Queries that are critical or very frequent, however, might benefit from covering indexes.</li> <li><em>Avoid indexes on small tables</em>. Indexes on tables that fit in one or two pages often do not provide more performance benefits but increase the number of structures to maintain and administer.</li> <li><em>Defining blob data types</em>. If a table has multiple <strong>varchar, nvarchar, varbinary, sql_variant</strong> or CLR user-defined type columns, consider the percentage of the rows that are likely to <em>flow over</em> and the frequency with which this overflow data is likely to be queried. If they are likely to be frequent queries on many rows of row-overflow data, consider normalizing the table so that some columns are moved to another table. This can then be queried in an asynchronous JOIN operation.</li> </ul> <p>There can be many other rules while doing physical database design, in general these best practices make sense and can be justified while taken in isolation. At the same time, they are mostly qualitative assertions and principles that do not easily lead to detailed and well-defined rules. It is difficult to know exactly when and how to combine these rules, especially in the presence of multiple complex queries or storage constraints. It becomes challenging to understand how indexes interact to answer a given query. In general, some form of quantitative and systematic analysis becomes crucial for an effective physical design. In my future posts, I will further come up with advanced design and tuning concepts.</p> <br />Filed under: <a href=''>Administration</a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <a rel="nofollow" href=""><img alt="" border="0" src="" /></a> <img alt="" border="0" src=";blog=32823968&amp;post=148&amp;subd=ravisql&amp;ref=&amp;feed=1" width="1" height="1" />